目录
1、死锁日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
200526 17:49:17
*** (1) TRANSACTION:
TRANSACTION 7892ECEC4, ACTIVE 50 sec inserting ## 事务ID=7892ECEC4,活跃了50s
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 ## 4个锁,2个行锁,1个undo log
MySQL thread id 41920347, OS thread handle 0x7f8fe5598700, query id 26349859046 *.*.*.* databaseA_rw update
## 该事务的线程ID=41920347
insert into
tableA
(
id, template_id, template_type, modify_time, create_time
)
values(null, '8eaf3990b3d4493198c0c13150965741', 2, now(), now()) ## 这是当前事务执行的SQL
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: ## 上面SQL等待的锁信息
RECORD LOCKS space id 1326 page no 4 n bits 256 index `UNIQ_IDX_TEMPLATE` of table `databaseA`.`tableA` trx id 7892ECEC4 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
## 申请的插入意向锁等待在唯一索引的page num=4 256 bits持有的X GAP锁(lock_mode X locks gap before rec),需等待T2中的GAP锁释放。
0: len 30; hex 393064383166343162336330343833343930353432383962313863663932; asc 90d81f41b3c048349054289b18cf92; (total 32 bytes);
1: len 4; hex 000000f5; asc ;;
*** (2) TRANSACTION:
TRANSACTION 7892F00C6, ACTIVE 35 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1 ## 事务ID=7892F00C6,活跃了35s
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 ## 4个锁,2个行锁,1个undo log
MySQL thread id 41920345, OS thread handle 0x7f8ff7041700, query id 26349859047 *.*.*.* database_rw update
insert into
tableA
(
id, template_id, template_type, modify_time, create_time
)
values(null, '8eaf3990b3d4493198c0c13150965741', 2, now(), now())
*** (2) HOLDS THE LOCK(S): ## 该事务持有的X GAP锁
RECORD LOCKS space id 1326 page no 4 n bits 256 index `UNIQ_IDX_TEMPLATE` of table `databaseA`.`tableA` trx id 7892F00C6 lock_mode X locks gap before rec
Record lock, heap no 168 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 393064383166343162336330343833343930353432383962313863663932; asc 90d81f41b3c048349054289b18cf92; (total 32 bytes);
1: len 4; hex 000000f5; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ## 申请的插入意向锁等待在唯一索引的page num=4 256 bits持有的X GAP锁(lock_mode X locks gap before rec),需等待T1中的GAP锁释放。
RECORD LOCKS space id 1326 page no 4 n bits 256 index `UNIQ_IDX_TEMPLATE` of table `databaseA`.`tableA` trx id 7892F00C6 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 393064383166343162336330343833343930353432383962313863663932; asc 90d81f41b3c048349054289b18cf92; (total 32 bytes);
1: len 4; hex 000000f5; asc ;;
*** WE ROLL BACK TRANSACTION (2) ## 这里选择回滚事务2
4 lock struct(s) 表示trx->trx_locks锁链表的长度为4,每个链表节点代表该事务持有的一个锁结构,包括表锁,记录锁以及auto_inc锁等。 1 row lock(s)表示当前事务持有的行记录锁/gap 锁的个数。
简要的死锁分析参考死锁日志中##。虽然死锁后,有一个事务会回滚,但会导致业务的等待。这段死锁对应的代码如下:
2、重现与分析
重现(存储引擎:innodb, 隔离级别:RR)
T1 | T2 |
---|---|
select * from tableA where templateId='111' for update(gap锁) | |
select * from tableA where templateId='111' for update (gap锁) | |
insert into tableA ( id, template_id, template_type, modify_time, create_time) values(null, '111', 2, now(), now()) | |
insert into tableA ( id, template_id, template_type, modify_time, create_time) values(null, '111', 2, now(), now()) |
分析
当两个事务同时通过select * from update,并且未命中任何记录的情况下,得到了相同的gap锁。此时再进入并发插入,当T1申请获取插入意向锁(insert intention lock),由于T2存在gap锁会阻塞插入意向锁,故T1会进入等待状态。当T2执行insert时,同样的T2获取插入意向锁由于T1存在的gap锁进入等待状态(insert intention lock相互不会阻塞),从而导致死锁。
这里再强调一点,gap锁与gap锁是兼容的,插入意向锁相互不会阻塞,插入意向锁也不会阻塞gap lock,但gap lock会阻塞插入意向锁,感觉gap lock存在的价值就是为了阻塞插入意向锁。
最后,也是最重要的,写这篇文章的目的,是分享一个链接: https://github.com/aneasystone/mysql-deadlocks。