InnoDB学习(六)之数据库锁

InnoDB学习(六)之数据库锁

InnoDB存储引擎的默认隔离级别事可重复读,MVCC多版本并发控制仅仅解决了快照读情况下的数据隔离,而对于当前读,InnoDB通过锁来进行并发控制。

InnoDB锁

本文主要参考了MySQL官方文档,并在上面添加了一些自己的理解,有兴趣看英文的也可以看MySQL官方文档。本文分为以下章节:

  1. 共享锁和独占锁;
  2. 意向锁;
  3. 行锁;
  4. 间隙锁;
  5. Next-Key锁
  6. 插入意向锁;
  7. 自增锁;

共享锁和排他锁

InnoDB锁的最小粒度是行锁,行锁可以分为两大类:共享锁(S)和独占锁(X)。

  • 共享锁:持有某行数据共享锁的事务,可以读取行锁对应行的数据;
  • 独占锁:持有某行数据独占锁的事务,可以修改行锁对应行的数据;

如果事务T1持有行R的共享锁,那么对于事务T2对行R的访问分为两种情况:

  • 如果事务T2请求行R的共享锁,则事务T2可以请求成功,请求完成后事务T1和事务T2同时持有行R的共享锁;
  • 如果事务T2请求行R的排他锁,事务T2会被阻塞,直到事务T1释放锁或者事务超时回滚;

如果事务T1持有行R的共享锁,那么不管事务T2请求R行的共享锁还是排他锁,都会被事务T1阻塞,直到事务T1释放锁或事务T2回滚。

意向锁

InnoDB支持支持多种粒度的锁,比如对于以下两个SQL语句,加锁的对象就完全不同:

  1. SELECT * FROM USER_INFO WHERE ID = 1 FOR UPDATE,其中ID是主键,ID=1的数据行存在,那么这句SQL会获取ID=1的数据行的独占锁;
  2. LOCK TABLES USER_INFO WRITE,其中USER_INFO表存在,那么这句SQL会获取USER_INFO表的独占锁;

表锁和行锁之间也存在互斥的情况,比如表上的独占锁和表中每一行数据的独占锁之间冲突(锁表了当然不允许修改表中的内容),这种互斥要怎么实现呢?InnoDB使用了意向锁实现表锁和行锁之间的互斥,意向锁是表级别的锁,对一行数据添加独占锁或排他锁时,会先向数据行所在的表添加意向锁,意向锁分为两种类型:

  1. 共享意向锁:事务会对表中的某一行数据添加共享锁;
  2. 排他意向锁:事务会对表中的某一行数据添加排他锁;

所以对表添加意向锁的情况也分两种:

  1. 如果事务需要获取某一行数据的共享锁,那么必然会首先获取数据所在表的共享意向锁,如SQL语句SELECT * FROM USER_INFO WHERE ID = 1 LOCK IN SHARE MODE会首先向表USER_INFO添加共享意向锁;
  2. 如果事务需要获取某一行数据的排他锁,那么必然会首先获取数据所在表的排他意向锁,如SQL语句SELECT * FROM USER_INFO WHERE ID = 1 FOR UPDATE会首先向表USER_INFO添加共享排他锁;

表锁和意向锁之间的冲突情况如下所示:

表排他锁 共享排他锁 表共享锁 共享意向锁
表排他锁 冲突 冲突 冲突 冲突
共享排他锁 冲突 不冲突 冲突 不冲突
表共享锁 冲突 冲突 不冲突 不冲突
共享意向锁 冲突 不冲突 不冲突 不冲突

如果事务请求的表锁和表现有的锁之间不冲突,那么事务可以请求锁成功;如果事务请求的锁和表现有的锁冲突,那么事务必须等待表锁被释放,或者当前事务需要回滚。

我们可以注意到,意向锁之间不会互斥,因为意向锁代表的是修改表中的某一行数据,两个意向锁表示修改表中的两行数据,所以两个意向锁不一定会冲突。意图锁只会和表锁之间冲突,如LOCK TABLES USER_INFO WRITE会向表添加表锁。

InnoDB中,我们可以通过SHOW ENGINE INNODB STATUS语句查看表锁状况,以下为锁状况示例:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

行锁

行锁是添加在索引上的锁,例如对于SELECT * FROM USER_INFO WHERE ID = 1 FOR UPDATE,在ID是唯一索引的情况下,该SQL语句会对ID对应的索引节点上添加排他锁,阻止其它事务修改该行数据。行锁添加的对象是索引节点,如果表没有定义索引,InnoDB会创建一个隐藏的聚集索引,并使用该索引来添加行锁。

InnoDB中,我们可以通过SHOW ENGINE INNODB STATUS语句查看行锁状况,以下为锁状况示例:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     "O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁

间隙锁加锁的对象是索引之间的间隙,例如如果对于SQL语句SELECT * FROM USER_INFO WHERE ID>10 and ID<20 FOR UPDATE,该事务会向数据库中ID索引树上10~20之间的所有节点间隙添加间隙锁。当另一个事务尝试向数据库中插入ID=15的记录时,会被间隙锁阻塞。

间隙锁的间隙中可以包含多个索引节点、单个索引阶段或者不包含任何节点。间隙锁主要用于解决可重复读隔离级别下的幻读问题。

对于唯一索引,如果使用等值查询,那么间隙锁会退化为行锁,如下SQL中,ID是唯一索引列,并且ID=100的数据存在,那么以下SQL只会添加行锁:

SELECT * FROM child WHERE id = 100;

如果ID不是唯一索引,那么上文中的SQL语句则会给ID索引树中的Id=100和前一个节点之间的间隙添加GAP锁,间隙锁之间不冲突,并且两个间隙锁之间的节点被删除之后,两个间隙锁还会合并为一个间隙锁。

InnoDB中的间隙锁只有一个目的,阻止向间隙内插入数据,间隙锁只和插入意向锁冲突,和其它任何锁都不冲突。可以通过将事务隔离级别更改为读已提交或启用innodb_locks_unsafe_for_binlog系统变量来禁用间隙锁。

在禁用间隙锁的情况下,InnoDB还会将释放不匹配行的记录锁(违反了加锁的2PL原则)。对于UPDATE语句,InnoDB执行"半一致"读取:读取最新提交的数据,MySQL使用最新提交的数据判断是否符合UPDATE语句中的WHERE条件。

Next-Key锁

Next-Key锁是行锁和间隙锁的组合,在InnoDB唯一索引加锁的过程中,InnoDB会从索引中查找符合条件的索引节点,并对这些符合条件的索引节点添加行锁。

如果对某行记录加Next-Key锁而不是行锁,那么而Next-Key锁不仅会对记录本身添加行锁,还会对行锁之前的间隙添加间隙锁,二者组合成了Next-Key。Next-Key不允许其它事务向加锁的间隙中插入数据。

假设ID索引包含值10、11、13和20,那么先索引的节点添加Next-Key锁可能会有以下几种情况,下文中圆括号表示排除间隙,方括号表示包含端点:

  1. 如果对索引10所在的节点加Next-Key,加锁范围为(负无穷, 10];
  2. 如果对索引11所在的节点加Next-Key,加锁范围为(10, 11];
  3. 如果对索引13所在的节点加Next-Key,加锁范围为(11, 13];
  4. 如果对索引20所在的节点加Next-Key,加锁范围为(13, 20];
  5. 如果20之后的间隙加Next-Key,加锁范围为(20, 正无穷);

对于最后一个间隙,可以理解为:InnoDB中有一个虚拟的最大节点,会在该节点上添加Next-Key.

InnoDB中,我们可以通过SHOW ENGINE INNODB STATUS语句查看Next-Key锁状况,以下为锁状况示例:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     "O;;
 2: len 7; hex b60000019d0110; asc        ;;

插入意向锁

插入意向锁是向数据库中插入一行新数据时,需要向插入间隙添加的一种间隙锁。插入意向锁之间不冲突,例如两个事务分别打算向(4,7]之间的间隙插入5和6,这两个事务都会向(4,7]中的间隙添加插入意向锁,但是二者互不阻塞。

假设有两个事务,事务A和事务B,数据库表中包含两条记录90102。事务A对ID大于100的索引记录添加Next-Key独占锁定:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

事务B尝试向数据库中插入一条101的记录:

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

通过SHOW ENGINE INNODB STATUS,我们可以看到此时数据库的锁等待情况:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

自增锁

自增锁是一种特殊的表级锁,当表中包含AUTO_INCREAMENT的表中的事务使用。在最简单的情况下,如果一个事务正在向表中插入数据行,该事务会占有自增所,其它任何事务在向表中插入数据时都会被该锁阻塞。我们可以通过innodb_autoinc_lock_mode变量控制自增锁的自增的算法,MySQL对自增锁有很多优化,本文不详细介绍。

我是御狐神,欢迎大家关注我的微信公众号:wzm2zsd

qrcode_for_gh_83670e17bbd7_344-2021-09-04-10-55-16

参考文档

MySQL官方文档

本文最先发布至微信公众号,版权所有,禁止转载!