带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究（4）https://developer.aliyun.com/article/1340030?groupCode=taobaotech
问题分析
复现
表中已有数据：
----+---------------------+---------------------+----------+-------------+-------------+
| id | gmt_create | gmt_modified | lock_key | lock_biz | lock_context|
+----+---------------------+---------------------+----------+-------------+-------------+
| 12 | 2022-02-15 19:54:42 | 2022-02-15 19:54:42 | 123 | accountUser | 0 |
| 50 | 2022-02-15 19:55:05 | 2022-02-15 19:55:05 | 150 | accountUser | 0 |
| 75 | 2022-02-15 19:55:19 | 2022-02-15 19:55:19 | 200 | accountUser | 0 |
从死锁日志可以看出，是由于insert操作引发死锁，故重点研究与讲解。
步骤 | T1 | T2 | T3 | T4 |
1 | begin; | | begin; | |
2 | insert into test_lock( `gmt_create` ,`gmt_- modified` ,`lock_key` , `lock_biz` ) VALUE (now(),now(), '140', 'AccountUser'); | | insert into test_lock( `gmt_create` ,`gmt_- modified` ,`lock_key` , `lock_biz` ) VALUE (now(),now(), '144', 'AccountUser'); | |
3 | | begin; | | begin; |
4 | | insert into test_lock`( `gmt_create` ,`gmt_- modified` ,`lock_key` , `lock_biz` ) VALUE (now(),now(), '140', 'AccountUser'); | | insert into `test_lock`( `gmt_create` ,`gmt_- modified` ,`lock_key` , `lock_biz` ) VALUE (now(),now(), '144', 'AccountUser'); |
5 | 存 在 lock_key=140, lock_biz='AccoutUser'的X record行锁 | 因为出现唯一性冲突，故加S Next-key Lock,锁住(123-140],(140,150]之间 的空间， | 存 在 lock_key=144, lock_biz='AccoutUser'的X record行锁 | 因为出现唯一性冲突，故加S Next-key Lock,锁住(123-144],(144,150]之间 的空间 |
6 | rollback; | 申请插入意向锁，阻塞 | | |
7 | | | rollback; | 申请插入意向锁，阻塞 |
8 | | 成功获取锁，并插入 | | deadLock |
T1: insetrt后，存在lock_key=140, lock_biz='AccoutUser'的X记录锁
T2: 与T1发生唯一键冲突，故加上S Next-key Lock（也就是lock mode S waiting），锁住(123-140],(140,150]之间的空间。
T3: insert后，存在lock_key=144, lock_biz='AccoutUser'的X记录锁
T4: 与T3发生唯一键冲突，故加上S Next-key Lock（也就是lock mode S waiting），锁住(123-144],(144,150] 之间的空间。
T2：T1 回滚后，T2与T4锁冲突，等待T4 S-Next-key Lock锁释放，然后申请意向锁，在日志中显示lock_- mode X locks gap before rec insert intention waiting.
T4：T3回滚后，T2和T4同时申请意向锁，死锁出现。
通过show engine innodb status;命令查看死锁日志，可以看到与线上表现一致。
------------------------
LATEST DETECTED DEADLOCK 3 ------------------------
2022 02-15 20:34:19 0x70000ec62000
*** (1) TRANSACTION:
TRANSACTION 8501, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 123145550733312, query id 93 localhost root update
insert into `test_lock`( `gmt_create` ,`gmt_modified` ,`lock_key` , `lock_biz` ) VALUE (now(),now(), '144', 'AccountUser')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index idx_uk_lock_name of table `dian- jing_test`.`test_lock` trx id 8501 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 14 0: len 3; hex 313530; asc 150;;
1: len 11; hex 6163636f756e7455736572; asc accountUser;;
2: len 8; hex 8000000000000032; asc 2;;
*** (2) TRANSACTION:
TRANSACTION 8495, ACTIVE 31 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145550176256, query id 89 localhost root update
insert into `test_lock`( `gmt_create` ,`gmt_modified` ,`lock_key` , `lock_biz` ) VALUE (now(),now(), '140', 'AccountUser')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 4 n bits 80 index idx_uk_lock_name of table `dian- jing_test`.`test_lock` trx id 8495 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 3; hex 313530; asc 150;;
1: len 11; hex 6163636f756e7455736572; asc accountUser;;
2: len 8; hex 8000000000000032; asc 2;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index idx_uk_lock_name of table `dian- jing_test`.`test_lock` trx id 8495 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 3; hex 313530; asc 150;;
1: len 11; hex 6163636f756e7455736572; asc accountUser;;
2: len 8; hex 8000000000000032; asc 2;;
小注解：mysql的锁，放在系统库information_schema的INNODB_LOCKS和INNODB_LOCK_WAITS两个表中，可直接select查看。
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究（6）https://developer.aliyun.com/article/1340028?groupCode=taobaotech
