带你读《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
