一致性非锁定读和锁定读

一致性非锁定读：

含义：如果正在读取的行执行了update或delete操作，那么读取的操作不会等到行上的锁被释放，但是会读该记录生成一个快照，则此时INNOdb引擎会去读取这个行的快照

不同的事务隔离级别的一致性非锁定读

1.read committed 级别
在这种隔离级别下，对于快照数据会总是去读取新的一份快照数据（即commit之后的数据）
seesionA;

/*隔离级别  read committed */
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update r_lock set a=2 where a=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql>

SessionB:

/*隔离级别  read committed */
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from r_lock ;
+------+
| a    |
+------+
|   11 |
|   22 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

mysql> select * from r_lock ;/*当sessionAcommit后读取的就是新的快照*/
+------+
| a    |
+------+
|   11 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

2.repeatable-read：总是 读取事务开始时的行数据，直到这个读取的事务commit之后
session A

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from r_lock limit 3;
+------+
| a    |
+------+
|   11 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from r_lock limit 3;
+------+
| a    |
+------+
|   11 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
mysql> commit;/*只有提交事务才能读取到session中修改的记录*/
Query OK, 0 rows affected (0.00 sec)

mysql> select * from r_lock limit 3;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

Session B

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> use learn1;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update r_lock set a=1 where a=11;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql>

一致性锁定读：

通过对数据读取操作显示的加锁来保证数据逻辑的一致性。Innodb对读取操作支持两种加锁：

由于锁之间有兼容性，所以就导致了在读取的过程中的数据时被加锁的直到读取的事务commit释放锁，修改数据的事务才可以进行（任何的修改操作都是排他锁）。
session A读取事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from r_lock limit 3 for update;/*设置排他锁*/
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> commit;

session B修改数据事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update r_lock set a=11 where a=1;
Query OK, 1 row affected (9.60 sec)/*直到读取操作commit之后才会进行，否则一直被挂起*/
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)