InnoDB隔离级别与锁机制

 

参考：

《MySQL技术内幕InnoDB搜索引擎》

测试原因：

理解“一致性的非锁定读”操作及相关锁机制。

1. 在Read committed事务隔离级别下，对于快照数据，“一致性的非锁定读”总是读取被锁定行的最新一份快照数据。

2. 在Repeatable read事务隔离级别下，对于快照数据，“一致性的非锁定读”总是读取事务开始时的行数据版本。

测试条件：

在一行进行delete、update操作时，读操作不会等待该行锁的释放，而会读取一个快照版本。

测试涉及的知识：

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交度（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

锁类型

共享锁（S Lock），允许事务读一行数据

排他锁（X Lock），允许事务删除或者更新一行数据

意向锁（表级别）

意向共享锁（IS Lock），事务想要获得一个表中某几行的共享锁

意向排他锁（IX Lock），事务想要获得一个表中某几行的排他锁

对读操作进行加锁

select ... for update

加x锁，可以防止丢失更新的问题

select ... lock in share mode（在Serializable事务级别下，每条select语句，自动加上lock in share mode）

加s锁

锁算法

record lock 单个行记录上的锁

gap lock 间隙锁，锁定一个范围，但不锁定记录本身

next-key lock 锁定一个范围，并且锁定记录本身（在Repeatable read事务级别下，使用该算法，可避免幻读的产生。）

多版本并发控制，MVCC

注意：在MySQL5.5中，我尝试动态更改tx_isolation，结果报错，该参数不能更改。修改my.cnf，重启服务。

测试环境：

MySQL 5.5

创建一个测试表

create table t (a int not null primary key , b varchar(100));

insert into t select 1,'abc';

测试过程

1. READ-COMMITTED隔离级别

mysql> show global variables like 'tx_isolation';

+---------------+----------------+

| Variable_name | Value          |

+---------------+----------------+

| tx_isolation  | READ-COMMITTED |

+---------------+----------------+

1 row in set (0.00 sec)

 

session 1	session 2
mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t where a=1; +---+------+ | a | b    | +---+------+ | 1 | abc  | +---+------+ 1 row in set (0.00 sec) 开始一个事务，并读取a=1这行的记录。
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t set a=2 where a=1;   Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 同样开始一个事务，并更改a=1这样的记录，先不提交。 a=1这一行就获得了一个X锁。
mysql> select * from t where a=1; +---+------+ | a | b    | +---+------+ | 1 | abc  | +---+------+ 1 row in set (0.00 sec) 再次查询，可以查询，但是记录是未改变的。 分析： “一致性的非锁定读”总是读取被锁定行的最新一份快照数据。
	mysql> commit; Query OK, 0 rows affected (0.01 sec)
mysql> select * from t where a=1; Empty set (0.00 sec)

测试数据还原

mysql> update t set a=1 where a=2;  

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

2. Repeatable read

mysql> show global variables like 'tx_isolation';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation  | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

s

session 1	session 2
mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> select * from t where a=1; +---+------+ | a | b    | +---+------+ | 1 | abc  | +---+------+ 1 row in set (0.00 sec) 开始一个事务，并读取a=1这行的记录。
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t set a=2 where a=1;   Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 同样开始一个事务，并更改a=1这样的记录，先不提交。 a=1这一行就获得了一个X锁。
mysql> select * from t where a=1; +---+------+ | a | b    | +---+------+ | 1 | abc  | +---+------+ 1 row in set (0.00 sec) 再次查询，可以查询，但是记录是未改变的。
	mysql> commit; Query OK, 0 rows affected (0.01 sec)
mysql> select * from t where a=1; +---+------+ | a | b    | +---+------+ | 1 | abc  | +---+------+ 1 row in set (0.00 sec) session 2提交了事务，但是这里读取的记录仍然未改变 分析： “一致性的非锁定读”总是读取本次事务开始时的行数据版本。	mysql> select * from t where a=1; Empty set (0.00 sec) 读取不到数据是理所当然的。