亲宝软件园·资讯

展开

关于 InnoDB 锁的超全总结

以终为始 人气:0
几个月之前,开始深入学习 MySQL 。说起数据库,并发控制是其中很重要的一部分。于是,就这样开起了 MySQL 锁的学习,随着学习的深入,发现想要更好的理解锁,需要了解 MySQL 事务,数据底层的存储方式,MySQL 的执行流程,特别是索引的选择等。 在学习期间,查找了不少资料,现根据个人的理解总结下来,方便日后复习。 ## InnoDB 锁一览 先从 [MySQL 官网](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks)的锁介绍开始,来逐一认识下这些让我们夜不能寐的小王八蛋: ![](https://img2020.cnblogs.com/blog/1861307/202003/1861307-20200308182446703-619213870.png) ### Shared and Exclusive Locks 这二位正式称呼呢,就是共享锁和排他锁,其实就是我们常说的读锁和写锁。它们之间的互斥规则,想必都清楚,就不赘述了。但有一点需要注意,**共享锁和排他锁是标准的实现行级别的锁。**举例来说,当给 select 语句应用 `lock in share mode ` 或者 `for update`,或者更新某条记录时,加的都是行级别的锁。 与行级别的共享锁和排他锁类似的,还有表级别的共享锁和排他锁。如 `LOCK TABLES ... WRITE/READ` 等命令,实现的就是表级锁。 ### Intention Locks 在 InnoDB 中是支持多粒度的锁共存的,比如表锁和行锁。而 Intention Locks - 意向锁,就是表级锁。和行级锁一样,意向锁分为 [intention shared lock](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/glossary.html#glos_intention_shared_lock) (`IS`) 和 [intention exclusive lock](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/glossary.html#glos_intention_exclusive_lock) (`IX`) . 但有趣的是,IS 和 IX 之间并不互斥,也就是说可以同时给不同的事务加上 IS 和 IX. 兼容性如下: ![](https://img2020.cnblogs.com/blog/1861307/202003/1861307-20200308182522643-1705968057.png) 这时就产生疑问了,那它俩存在的意义是什么?作用就是,和共享锁和排他锁互斥。注意下,**这里指的是表级别的共享锁和排他锁,和行级别没有关系!** 官网中给了这样一段解释: ``` The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table. ``` 意向锁的目的就是表明有事务正在或者将要锁住某个表中的行。想象这样一个场景,我们使用 `select * from t where id=0 for update;` 将 id=0 这行加上了写锁。假设同时,一个新的事务想要发起 `LOCK TABLES ... WRITE` 锁表的操作,这时如果没有意向锁的话,就需要去一行行检测是否所在表中的某行是否存在写锁,从而引发冲突,效率太低。相反有意向锁的话,在发起 `lock in share mode ` 或者 `for update` 前就会自动加上意向锁,这样检测起来就方便多了。 在实际中,手动锁表的情况并不常见,所以意向锁并不常用。特别是之后 MySQL 引入了 [MDL 锁](https://www.cnblogs.com/michael9/p/12167434.html#_label1_2),解决了 DML 和 DDL 冲突的问题,意向锁就更不被提起来了。 ### Record Locks record lock ,就是常说的行锁。InnoDB 中,[表都以索引的形式存在](https://www.cnblogs.com/michael9/p/12144435.html#_label0_2),每一个索引对应一颗 B+ 树,这里的行锁锁的就是 B+ 中的索引记录。之前提到的共享锁和排他锁,就是将锁加在这里。 ### Gap Locks Gap Locks, 间隙锁锁住的是索引记录间的空隙,是为了解决[幻读问题](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/innodb-locking.html)被引入的。有一点需要注意,间隙锁和间隙锁本身之间并不冲突,仅仅和插入这个操作发生冲突。 ### Next-Key lock next-key lock [是行锁(Record)和间隙锁的并集](https://www.cnblogs.com/michael9/p/12358631.html#_label2)。**在 RR 级别下,InnoDB 使用 next-key 锁进行树搜索和索引扫描。**记住这句话,加锁的基本单位是 next-key lock. ## 加锁规则 该加锁原则由林晓斌老师刷代码后总结,符合的版本如下: 1. MySQL 版本:5.x - 5.7.24, 8.0 - 8.0.13. 我是 5.7.27 也未发现问题。 规则包括:两个“原则”、两个“优化”和一个“bug”。 1. 原则1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。 2. 原则2:查找过程中访问到的对象才会加锁。 3. 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。 4. 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。 5. 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。 解释下容易理解错误的地方: 1. 对优化 2 的说明: 从等值查询的值开始,向右遍历到第一个不满足等值条件记录结束,然后将不满足条件记录的 next-key 退化为间隙锁。 2. 等值查询和遍历有什么关系? 在分析加锁行为时,一定要从索引的数据结构开始。通过树搜索的方式定位索引记录时,用的是"等值查询",而遍历对应的是在记录上向前或向后扫描的过程。 ## 应用场景 表结构如下: ``` CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25); ``` ### 场景1:主键索引等值间歇锁 | Session A | Session B | Session C | | ------------------------------ | ---------------------------- | ------------------------------- | | begin; | | | | update t set d=d+1 where id=7; | | | | | insert into t values(8,8,8); | | | | 被阻塞 | update t set d=d+1 where id=10; | | | | 查询正常 | 其中 id 列为主键索引,并且 id=7 的行并不存在。 对于 Session A 来说: 1. 根据原则1,加锁的单位是 next-key, 因为 id=7 在 5 - 10 间,next-key 默认是左开右闭。所以范围是 (5,10]. 2. 根据优化2,但因为 id=7 是等值查询,到 id=10 结束。next-key 退化成间隙锁 (5,10). 对于 Session B 来说: * 插入操作与间隙锁冲突,所以失败。 对于 Session C 来说: 1. 根据原则1,next-key 加锁 (5,10]. 2. 根据优化1:给唯一索引加锁时,退化成行锁。范围变为:id=10 的行锁 3. Session C 和 Session A (5,10) 并不起冲突,所以成功。 这里可以看出,**行锁和间隙锁都是有 next-key 锁满足一定后条件后转换的,加锁的默认单位是 next-key.** ### 场景2:非唯一索引等值锁 | Session A | Session B | Session C | | ---------------------------------------------- | ------------------------------ | ---------------------------- | | begin; | | | | select id from t where c=5 lock in share mode; | | | | | update t set d=d+1 where id=5; | | | | 查询正常 | insert into t values(7,7,7); | | | | 被阻塞 | 关注几点:c为非唯一索引,查询的字段仅有 id,`lock in share mode` 给满足条件的行加上读锁。 Session A: 1. c=5,等值查询且值存在。先加 next-key 锁,范围为 (0,5]. 2. 由于 c 是普通索引,因此仅访问 c=5 这一条记录不会停止,会继续向右遍历,到 10 结束。根据原则2,这时会给 id=10 加 next-key (5,10]. 3. 但 id=10 同时满足优化2,退化成间隙锁 (5,10). 4. 根据原则2,该查询使用覆盖索引,可以直接得到 id 的值,主键索引未被访问到,不加锁。 Session B: 1. 根据原则1 和优化1,给 id=10 的主键索引加行锁,并不冲突,修改成功。 Session C: 1. 由于 Session A 已经对索引 c 中 (5,10) 的间隙加锁,与插入 c=7 冲突, 所以被阻塞。 可以看出,**加锁其实是在索引上,并且只加在访问到的记录上,如果想要在 lock in share mode 下避免数据被更新,需要引入覆盖索引不能包含的字段。** 假设将 Session A 的语句改成 `select id from t where c=5 for update;`, for update 表示可能当前事务要更新数据,所以也会给满足的条件的主键索引加锁。这时 Session B 就会被阻塞了。 ### 场景3:非唯一索引等值锁-锁主键 | Session A | Session B | | ---------------------------------------------- | ----------------------------- | | begin; | | | select id from t where c=5 lock in share mode; | | | | insert into t values(9,10,7); | | | 被阻塞 | | | | 和场景 2 很相似,该例主要是为了更好的说明间隙的概念。 Session A 的加锁范围不变,给索引 C 加了 (0,5] 和 (5,10) 的行锁。需要知道的是,非唯一索引形成的 key,需要包含主键 id,用于保证唯一性,画个图如下。 由图中可知,由于非唯一索引存在主键id,并且按照 B+ 树的排序规则,不光 c 的值在加锁范围内不能被更改和插入,对应 id 的范围也不能被更改。怎么理解这句话呢,上个例子不是说,主键索引不会被加锁,怎么这里的主键 id 又被锁了呢? 首先主键索引是另外一颗B+树确实没有被锁,但这里由于 C 是非唯一索引,形成的B+树需要将主键索引的 Id 包含进来,并在按照先 c 字段,后 id 字段进行排序。这样,在给 c 字段加行锁时,对应的 id 也同时加了行锁。 上面例子中,Session2 更新成功是因为修改的是 d 地段,并没有更新 id 的值,所以成功了。而这里想要插入的 (id=6, c=10), 虽然 c=10 没有锁,但 id=6 却在锁的范围内,所以这里就被阻塞了。同样插入 (id=100,c=6) 也会被阻塞。 **也就说,对于非唯一索引,考虑加锁范围时要考虑到主键 Id 的情况。** ### 场景4:主键索引范围锁 | Session A | Session B | Session C | | --------------------------------------------------- | ------------------------------- | ------------------------------- | | begin; | | | | select * from t where id>=10 and id <11 for update; | | | | | insert into t values(8,8,8); | | | | 正常 | | | | insert into t values(13,13,13); | | | | 被阻塞 | | | | | update t set d=d+1 where id=15; | | | | 被阻塞 | Session A: 1. 先找到 id=10 行,属于等值查询。根据原则1,优化1,范围是 id=10 这行。 2. 向后遍历,属于范围查询,找到 id=15 这行,根据原则2,范围是 (10,15] Session B: 1. 插入 (8,8,8) 可以,(13,13,13) 就不行了。 Session C: 1. id=15 同样在锁的范围内,所以也被阻塞。 ### 场景5:非唯一索引范围锁 | Session A | Session B | Session C | | ------------------------------------------------- | ------------------------------- | ------------------------------ | | begin; | | | | select * from t where c>=10 and c <11 for update; | | | | | insert into t values(8,8,8); | | | | 被阻塞 | | | | insert into t values(13,13,13); | | | | 被阻塞 | | | | | update t set d=d+1 where c=15; | | | | 被阻塞 | Session A: 1. 由于 c 是非唯一索引,索引对于 c=10 等值查询来说,根据原则1,加锁范围为 (5,10]. 2. 向右遍历,范围查询,加锁范围为 (10,15]. Session B: 1. (8,8,8) 和 (13,13,13) 都冲突,所以被阻塞。 Session C: 1. c=5 也被锁住,也会被阻塞。 ### 场景6:唯一索引范围锁 bug | Session A | Session B | Session C | | --------------------------------------------------- | ------------------------------- | ------------------------------- | | begin; | | | | select * from t where id>10 and id <=15 for update; | | | | | update t set d=d+1 where id=20; | | | | 被阻塞 | | | | | insert into t values(16,16,16); | | | | 被阻塞 | Session A: 1. 由于开始找大于 10 的过程中是第一次是范围查询,所以没有优化原则。加 (10,15]. 2. 有一个特殊的地方,理论上说由于 id 是唯一主键,找到 id=15 就应该停下来了,但实际没有。根据 bug 原则,会继续扫描第一个不满足的值为止,接着找到 id=20,因为是范围查找,没有优化原则,继续加锁 (15,20]. > 这个 bug 在 8.0.18 后已经修复了 对于 Session B 和 Session C 均和加锁的范围冲突。 ### 场景7:非唯一索引 delete | Session A | Session B | Session C | | ------------------------- | ------------------------------ | ------------------------------ | | begin; | | | | delete from t where c=10; | | | | | insert into t values(12,12,12) | | | | 被阻塞 | | | | | update t set d=d+1 where c=15; | | | | 成功 | delete 后加 where 语句和 `select * for update` 语句的加锁逻辑类似。 Session A: 1. 根据原则1,加 (5,10] 的 next-key. 2. 向右遍历,根据优化2,加 (10,15) 的间歇锁。 ### 场景8:非唯一索引 limit | Session A | Session B | | --------------------------------- | ------------------------------ | | begin; | | | delete from t where c=10 limit 1; | | | | insert into t values(12,12,12) | | | 正常 | 虽然 c=10 只有一条记录,但和场景7 的加锁范围不同。 Session A: 1. 根据原则1,加 (5,10] 的 next-key. 2. 因为加了 limit 1,所以找到一条就可以了,不需要继续遍历,也就是说不在加锁。 所以对于 session B 来说,就不在阻塞。 ### 场景9:next-key 引发死锁 | Session A | Session B | | ----------------------------------------------- | ------------------------------------------------------------ | | begin; | | | select id from t where c=10 lock in share mode; | | | | update t set d=d+1 where c=10; | | | 被阻塞 | | insert into t values(8,8,8) | | | | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | 1. Session A 第一句 加 (5,10] next-key. 和 (10,15) 的 间隙锁。 2. Session B,和 Session A 想要的加锁范围相同,先加 (5,10] next-key 发现被阻塞,后面 (10,15) 没有被加上,暂时等待。 3. Session A,加入 (8,8,8) 和 Session B 的加锁范围 (5,10] 冲突,被阻塞。形成 A,B 相互等待的情况。引发死锁检测,释放 Session B. > 假如把 insert into t values(8,8,8) 改成 insert into t values(11,11,11) 是可以的,因为 Session B 的间歇锁 (10,15) 没有被加上。 分析下死锁: ``` mysql> show engine innodb status\G; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-03-08 17:04:10 0x7f9be0057700 *** (1) TRANSACTION: TRANSACTION 836108, ACTIVE 16 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1653, OS thread handle 140307320846080, query id 1564409 localhost cisco updating update t set d=d+1 where c=10 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 251 page no 4 n bits 80 index c of table `my_test`.`t` trx id 836108 lock_mode X waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; *** (2) TRANSACTION: TRANSACTION 836109, ACTIVE 22 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 1655, OS thread handle 140307455112960, query id 1564410 localhost cisco update insert into t values(8,8,8) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 251 page no 4 n bits 80 index c of table `my_test`.`t` trx id 836109 lock mode S Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 251 page no 4 n bits 80 index c of table `my_test`.`t` trx id 836109 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; *** WE ROLL BACK TRANSACTION (1) ``` 1. `(1) TRANSACTION` 表明发生死锁的第一个事务信息。 2. `(2) TRANSACTION` 表明发生死锁的第二个事务信息。 3. `WE ROLL BACK TRANSACTION (1)` 表明死锁的处理方案。 针对 `(1) TRANSACTION`: 1. `(1) WAITING FOR THIS LOCK TO BE GRANTED` 表示 `update t set d=d+1 where c=10` 要申请写锁,并处于锁等待的情况。 2. 申请的对象是 `n_fields 2`,`hex 8000000a;` 和 `hex 8000000a;`, 也就是 id=10 和 c=10 的记录。 针对 `(1) TRANSACTION`: 1. `HOLDS THE LOCK(S):` 表示当前事务2持有的锁是 :`hex 8000000a;` 和 `hex 8000000a;`. 2. `WAITING FOR THIS LOCK TO BE GRANTED:` 表示对于 `insert into t values(8,8,8)` 进行所等待。 3. `lock_mode X locks gap before rec insert intention waiting`: 表明在插入意向锁时,等待一个间隙锁( `gap before rec`)。 所以最后选择,回滚事务 (1)。 ### 场景10:非唯一索引 order by | Session A | Session B | | ------------------------------------------------------------ | ---------------------------- | | begin; | | | select * from t where c>=15 and c<=20 order by c desc lock in share mode; | | | | insert into t values(6,6,6); | | | 被阻塞 | 在分析具体的加锁过程时,先要分析语句的执行顺序。如 Session A 中使用了 `ordery by c desc` 按照降序排列的语句,这就意味着需要在索引树 C 上,找到第一个 20 的值,然后向左遍历。并且由于 C 是非唯一索引 20 的值应该是记录中最右边的值。 Session A 的加锁过程: 1. 在找到第一个 c=20 的值后,加 next-key (15,20]. 2. 但不会停下,因为无法确定当前 c=20 是最右面的值,继续遍历到 c=25,发现不满足,根据优化2,加 (20,25) 的间隙锁。 3. 然后从最左面的 c=20 向左遍历,找到 c=15,加锁 next-key (10,15]. 4. 和之前是一样,无法确定 c=15 是最左面的值,继续遍历到 c=10,根据优化2,加(5,10)的间隙锁 。 5. 最后由于是 `select * ` 对应主键索引 id=10,15,20 加行锁。 ### 场景 11:INSERT INTO .... SELECT ... | Session A | Session B | | ---------------------------- | ------------------------------------------------------ | | begin; | begin; | | insert into t values(1,1,1); | | | | insert into t (id,c,d) select 1,1,1 from t where id=1; | | | 被阻塞 | 为了保证数据的一致性,对于 `INSERT INTO .... SELECT ...` 中 select 部分会加 next-key 的读锁。 对于 Session A,在插入数据后,有了 id=1 的行锁。而 Session B 中的 select 虽然是一致性读,但会加上 id=1 的读锁。与 Session A 冲突,所以被阻塞。 ### 场景12:不等号条件里的等值查询 ``` begin; select * from t where id>9 and id<12 order by id desc for update; ``` 1. 这里由于是 `order by` 语句,优化器会先找到第一个比 12 小的值。在索引树搜索过程后,其实要找到 id=12 的值,但没有找到,向右遍历找到 id=15,所以加锁 (10,15]. 2. 但由于第一次查找是等值查找(在索引树上搜索),根据优化2,变为间隙锁 (10,15). 3. 然后向左遍历,变为范围查询,找到 id=5 这行,加 (0,5] 的 next-key. ### 场景 13:等值查询 in ``` begin; select id from t where c in(5,20,10) lock in share mode; ``` ``` mysql> explain select id from t where c in (5,20,10) lock in share mode\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: range possible_keys: c key: c key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) ERROR: No query specified ``` rows=3 并且使用索引 c,说明三个值都是通过 B+ 树搜索定位的。 1. 先查找 c=5,锁住 (0,5]. 由于 c 不是唯一索引,向右遍历到 c=10,开始是等值查找,加 (5,10). 2. 查找 c=15,锁住 (10,15], 再加 (15,20). 3. 最后查找 c=20,锁住 (15,20]. 再加 (20,25). 可见,**在 MySQL 中,锁是一个个逐步加的。** 假设还有一个这样的语句: ``` select id from t where c in(5,20,10) order by c desc for update; ``` 由于是 order by c desc,虽然这里的加锁范围没有变,但是加锁的顺序发生了改变,会按照 c=20,c=10,c=5. 的顺序加锁。虽然说间隙锁本身并不冲突,但记录锁却会。这样如果是两个语句并发的情况,就可能发生死锁,第一个语句拥有了 c5 的行锁,请求c=10 的行锁。当第二个语句,拥有了 c=10 的行锁,请求 c=5 的行锁。 ### 场景14:GAP 动态锁 | Session A | Session B | | -------------------------------------------------- | ------------------------------- | | begin; | | | select * from t where id>10 and id<=15 for update; | | | | delete from t where id=10; | | | 成功 | | | insert into t values(10,10,10); | | | 被阻塞。 | 这里 insert 被阻塞,就是因为间隙锁是个动态的概念,Session B 在删除 id=10 的记录后,Session A 持有的间隙变大。 对于 Session A 原来持有,(10,15] 和 (15,20] 的 next-key 锁。 Session B 删除 id=10 的记录后,(10,15] 变成了 (5,15] 的间隙。所以之后就插入不回去了。 ### 场景15:update Gap 动态锁 | Session A | Session B | | ----------------------------------------------- | ----------------------------- | | begin; | | | select * from t where id> 5 lock in share mode; | | | | update t set c=1 where c = 5; | | | 成功 | | | update t set c=5 where c = 1; | | | 被阻塞。 | Session A 加锁:(5,10], (10,15], (15,20], (20,25], (25,supermum]. > c>5 第一个找到的是 c=10,并且是范围查找,没有优化原则。 Session B 的 update 可以拆成两步: 1. 插入 (c=1,id=5). 2. 删除 (c=5,id=5). > 或者理解成,加(0.5] next-key 和 (5,10) 的间隙锁,但间隙锁不冲突。 修改后 Session A 的锁变为; (c=1, 10], (10,15], (15,20], (20,25], (25,supermum]. 接下来:update t set c=1 where c = 1 1. 插入 (c=5,id=5). 2. 删除 (c=1,id=5). 第一步插入意向锁和间隙锁冲突。 ## 参考 [InnoDB-locking](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/innodb-locking.html) [加锁过程](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/innodb-locks-set.html) [explain rows](https:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/5.7/en/explain-output.html#explain_rows)

加载全部内容

相关教程
猜你喜欢
用户评论