mysql中删除同一行会经常出现死锁？太可怕了
浏览数：35 /
时间：2015年06月12日
之前有一个同事问到我，为什么多个线程同时去做删除同一行数据的操作，老是报死锁，在线上已经出现好多次了，我问了他几个问题：
CREATE TABLE `abcdefg` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`abc` varchar(30),
`def` varchar(30) ,
`ghi` date,
`jkl` date,
`mnp` tinyint(4),
PRIMARY KEY (`id`),
UNIQUE KEY `uniqdefghijkl` (`def`,`ghi`,`jkl`)
);
1. 是不是在一个事务中做了好几件事情?
答：不是，只做一个删除操作，自动提交
2. 有多少个线程在做删除？
答：差不多10个
3. 是什么隔离级别？
答：可重复读
当时觉得不可思议，按说自动提交的话行锁，如果已经有事务加锁了，则会等待，等提交之后再去做，发现已经删除了，就会返回，删除0条，为什么会死锁？
但事情已经出了，必须研究一下，不然终究是心头之苦啊。
然后想到既然线上有这么简单的操作就可以死锁，那么应该写个简单的程序就可以重现，然后同事李润杰兄弟咔嚓咔嚓没多时就给我了，在这里谢谢他。
首先环境是这样的：
CREATE TABLE `abcdefg` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`abc` varchar(30),
`def` varchar(30) ,
`ghi` date,
`jkl` date,
`mnp` tinyint(4),
PRIMARY KEY (`id`),
UNIQUE KEY `uniqdefghijkl` (`def`,`ghi`,`jkl`)
);
这个表包括2个索引，一个是聚簇索引，另一个是uniqdefghijkl的二级唯一索引。
事先插入很多数据，然后3个线程同时做对同一条记录的删除，这里只做删除操作，并且都是自动提交，为了得到一批要删除的数据，事先查询很多条出来备用。
删除语句是这样的：
delete from abcdefg WHERE abc= ‘我是变量‘ and def=
‘我是变量‘ and ghi= ‘2013-12-19 00:00:00‘ and jkl= ‘2013-12-20
00:00:00‘;
那么现在就开始重现。。。
果然很快，死锁真的出现了，下面是执行show engine innodb status的结果：
===================================================
LATEST DETECTED DEADLOCK
------------------------
140123 12:20:50
*** (1) TRANSACTION:
TRANSACTION 2E10, ACTIVE 4917 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x1008, query id 43 192.168.xx.x username upd
ating
delete from abcdefg WHERE abc= ‘我是变量‘ and def= ‘我是变量‘ and ghi= ‘2013-12-19 00:00:00‘ and jkl= ‘2013-12-20 00:00:00‘;
------------------------
140123 12:20:50
*** (1) TRANSACTION:
TRANSACTION 2E10, ACTIVE 4917 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x1008, query id 43 192.168.xx.x username upd
ating
delete from abcdefg WHERE abc= ‘我是变量‘ and def= ‘我是变量‘ and ghi= ‘2013-12-19 00:00:00‘ and jkl= ‘2013-12-20 00:00:00‘;
*** (1) WAITING FOR THIS LOCK TO BE
GRANTED:
RECORD LOCKS space id 0 page no 12295 n bits 528 index `uniqdefghijkl` of table
RECORD LOCKS space id 0 page no 12295 n bits 528 index `uniqdefghijkl` of table
`deadlock`.`abcdefg` trx id 2E10
lock_mode X locks rec but not gap waiti
ng
Record lock, heap no 167 PHYSICAL RECORD: n_fields 4; compact format;
*** (2) TRANSACTION:
TRANSACTION 2E0E, ACTIVE 4917 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1, OS thread handle 0x1190, query id 41 192.168.xx.xx username upd
Record lock, heap no 167 PHYSICAL RECORD: n_fields 4; compact format;
*** (2) TRANSACTION:
TRANSACTION 2E0E, ACTIVE 4917 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1, OS thread handle 0x1190, query id 41 192.168.xx.xx username upd
ating
RECORD LOCKS space id 0 page no 12295 n bits 528 index `uniqdefghijkl` of table
delete from abcdefg WHERE
abc= ‘我是变量‘ and def= ‘我是变量‘ and ghi= ‘2013-12-19 00:00:00‘ and
jkl= ‘2013-12-20 00:00:00‘;
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 0 page no 12295 n bits 528 index `uniqdefghijkl` of table
`deadlock`.`abcdefg` trx id 2E0E
lock_mode X locks rec but not gap
Record lock, heap no 167 PHYSICAL
RECORD: n_fields 4; compact format;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12295 n bits 528 index `uniqdefghijkl` of table
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12295 n bits 528 index `uniqdefghijkl` of table
`deadlock`.`abcdefg` trx id 2E0E
lock_mode X waiting
Record lock, heap no 167 PHYSICAL
RECORD: n_fields 4; compact format;
*** WE ROLL BACK TRANSACTION (1)
*** WE ROLL BACK TRANSACTION (1)
===================================================
这是在三个线程的情况下是可以重现死锁的，但是为了更容易调试，试了一下在2个线程的情况下如何，最终发现重现不了。
这下坏了，多线程调试很麻烦，有时候这个走那个不走的，如果冻结某个线程，有可能导致线程之间死锁，或者自然执行，那又不能出现死锁的情况，因为这个死锁也是偶然性的，所以最终只有一种方法，那就是在mysql代码中打印log信息，将锁、记录与事务这块的函数中具有分歧点的地方都加了注释，并且将有用的信息打印出来，最终分析log文件，才发现了真正死锁的猫腻。
现在将三个导致死锁的事务的时序图画出来：
| 事务A | 事务B | 事务C |
| 开始 | ||
| 表的IX锁 17 @1 | ||
| 二级索引行锁X REC NOTGAP 1059 @2 检查死锁 没事 |
||
| 表IX锁 17 @3 | ||
| 二级索引记录行锁 REC NOTGAP X WAIT 1315 @4 检查死锁，没事 |
||
| 表IX锁 17 @5 | ||
|
二级索引记录行锁 REC NOTGAP X WAIT 1315 @6
检查死锁 没事 | ||
| 聚簇索引行锁X REC NOTGAP 1059 @7 | ||
| wait.... suspend.... | wait.... suspend.... | |
| commit | ||
| wakeup this trx 将@4的WAIT去掉，成为1059 |
||
|
二级索引记录行锁 REC X WAIT 291 @8
检查死锁 发现死锁 |
图1
说明：
上面的数字都是源代码中的关于各种锁的位图值：
LOCK_TABLE：16
LOCK_IX：1
LOCK_REC_NOT_GAP：1024
LOCK_WAIT：256
LOCK_REC：32
LOCK_X：3
所以锁@6表示的是LOCK_REC
| LOCK_REC_NOT_GAP | LOCK_X
| LOCK_WAIT = 1315
依次类推
这里检查死锁的算法大体上说一下，无非是检查有没有形成等待环
事务B的锁@8等待事务C的锁@6，事务C的锁@6在等待事务B的锁@3，此时发现又绕回来了，那么产生死锁。
到这里，死锁的现象如何产生已经解释清楚，但是，这是为什么呢？
这里的疑问是：
在事务A提交之后，将事务B唤醒了，此时事务B的锁@4为REC NOTGAP
X（1059），那么此时这个事务又去检查锁的情况，看看自己事务的锁有没有GRANT成功的，如果有则直接使用并且继续执行，如果没有则再加锁，做这个检查的函数为lock_rec_has_expl，它做的事情是下面的检查：
===========================================================
lock =
lock_rec_get_first(block, heap_no);
while (lock)
{
if (lock->trx
== trx
&& !lock_is_wait_not_by_other(lock->type_mode)
&& lock_mode_stronger_or_eq(lock_get_mode(lock),
precise_mode
& LOCK_MODE_MASK)
&& (!lock_rec_get_rec_not_gap(lock)
||
(precise_mode & LOCK_REC_NOT_GAP)
||
heap_no == PAGE_HEAP_NO_SUPREMUM)
&& (!lock_rec_get_gap(lock)
||
(precise_mode & LOCK_GAP)
||
heap_no == PAGE_HEAP_NO_SUPREMUM)
&& (!lock_rec_get_insert_intention(lock))) {
return(lock);
}
lock
= lock_rec_get_next(heap_no, lock);
}
=============================================================
这里需要满足6个条件：
- 首先这个锁是自己事务的
- 这个锁不是处于等待状态
- 当前锁的类型与precise_mode是兼容的，precise_mode值是X锁，因为这里是要做删除
- 当前锁不是NOT GAP类型，或者要加的锁类型是NOTGAP类型的，或者heapno为1
- 当前锁不是GAP类型，或者要加的锁类型是GAP类型的，或者heapno为1
- 当前锁不是意向插入锁
但此时发现1059（锁@4）根本不满足第4点啊，因为它首先是NOTGAP锁，同时heapno不是1，所以没有找到，所以在外面又重新创建一个锁，因为此时这行已经有锁了，那么它会创建一个REC
WAIT X锁（291），也就是锁@8。
所以即使锁@4不是处于等待状态了，此时也不能直接执行呢，而是重新创建了一个锁。此时导致了死锁。
那么现在问题又来了，从上图可以看到，这个时间序列没有什么特别的，或者特殊的一个交叉过程，从而是不是我们可以很容易的重现呢？仅仅通过开启三个会话，都设置为not
autocommit的，因为需要将第一个事务A的提交放在事务B C的后面。
那么开始了，创建相同的表，删除同一行记录。
| 事务A | 事务B | 事务C |
| begin | ||
| delete 删除行数返回为1 |
||
| begin | ||
| delete 阻塞 | ||
| begin | ||
| 阻塞 | ||
| commit | ||
| 观察有没有死锁 其实并没有死锁 删除行数返回为0 |
||
| 删除行数返回为0 |
图2
按说，上面这个图与图1没有什么区别，但没有死锁？为什么？
其实没有死锁是正常的，如果这样就死锁了，那mysql简直不能用了！！！
看来还是有区别的
正常模式下再做一次log分析，从log中看出了大问题......
再将上面详细的加锁图在无死锁模式下的情况贴出来：
| 事务A | 事务B | 事务C |
| 开始 | ||
| 表的IX锁 17 @1 | ||
| 二级索引行锁X REC NOTGAP 1059 @2 检查死锁 没事 |
||
|
聚簇索引行锁X REC NOTGAP 1059 @7
检查死锁 没事 |
||
| 表IX锁 17 @3 | ||
| 二级索引记录行锁 REC X WAIT 291
@4 检查死锁，没事 |
||
| 表IX锁 17 @5 | ||
|
二级索引记录行锁 REC X WAIT 291 @6
检查死锁 没事 | ||
| wait.... suspend.... | wait.... suspend.... | |
| commit | ||
| wakeup this trx 将@4的WAIT去掉，成为35 |
||
| 执行完成，提交 | ||
| 执行完成 |
图3
此时发现，图3其实与图1是一样的，那为什么图3可以正常执行完成，而图1死锁了呢？
但认真仔细看了之后，发现有很小的地方是不同的，图3中的锁@4加上的锁是291（REC & X &
WAIT），而图1中加的锁比它多了一个NOTGAP的锁，锁@6也是一样的，图3的事务A在提交并且唤醒了锁@4之后，它的锁类型为REC+X（35），而图1中的值也是比它多了一个NOTGAP锁。
现在已经基本定位了问题所在，应该是NOTGAP搞的鬼。但是为什么会有差别呢？
此时还需要回到代码中查看，通过日志分析，发现2个在执行下面代码时走了不同的路：
=======================================
if (prebuilt->select_lock_type
!= LOCK_NONE) {
ulint lock_type;
if (!set_also_gap_locks
|| srv_locks_unsafe_for_binlog
|| trx->isolation_level <= TRX_ISO_READ_COMMITTED
|| (unique_search
&&
!UNIV_UNLIKELY(rec_get_deleted_flag(rec, comp)))) {
goto no_gap_lock;//直接路到下面 lock_type
= LOCK_REC_NOT_GAP;处
} else {
lock_type =
LOCK_ORDINARY;
}
、
if (index ==
clust_index
&& mode == PAGE_CUR_GE
&& direction == 0
&& dtuple_get_n_fields_cmp(search_tuple)
== dict_index_get_n_unique(index)
&& 0 == cmp_dtuple_rec(search_tuple, rec, offsets))
{
no_gap_lock://标记
lock_type
= LOCK_REC_NOT_GAP;
}
=======================================
这里关键的分叉口就是在上面红色字体部分，死锁的时候走了goto no_gap_lock，而没有出现死锁的时候走的是lock_type
=
LOCK_ORDINARY;，而 LOCK_ORDINARY表示的是0，什么都没有，所以这2条路的不同就是差1024（NOTGAP锁）。
那么从日志中发现，走了第一条路是因为条件(unique_search &&
!UNIV_UNLIKELY(rec_get_deleted_flag(rec,
comp))是符合的。rec_get_deleted_flag函数的作用是判断这条记录是不是已经打了删除标志。
现在豁然明白了，如果当前这条要加锁的记录还没有打删除标志，则加的锁是NOTGAP类型的锁，否则就不设置类型，那说明上面的图1中事务A还是有一个细节没有画出来，正因为这个细节与事务B发生了交叉，导致了事务B在做的时候还没有打了删除标记，所以就加了NOTGAP锁，所以导致后面的死锁。
而正常情况下，也就是图2的测试，因为事务A已经完成了所有的操作，只等待提交，此时肯定已经打了删除标志，则在加锁时不会加NOTGAP锁，所以就不会出现死锁。
哎，用一句同事常说的话：我这下真的了然了，原来问题这么复杂，mysql中的猫腻太多了。
那现在分析一下原因吧：
现在已经确定问题就是出现在上面代码的判断中，在上面代码的上面还有一段注释：
/*
Try to place a lock on the index record; note that delete
marked
records are a special case in a unique search. If there
is
a non-delete marked record, then it is enough to lock its
existence
with LOCK_REC_NOT_GAP. */
这说明了加NOTGAP锁的意图，说明上面代码的判断是专门做的，具体原因就无从查起了，但是注释中说这是一种特殊情况，为什么呢？解决方式是把那2行直接去掉就可以了（测试过不会出现死锁了），但这个会不会是解决问题的根本原因，还要等待官方人员的处理。
所以到这里，把完整的死锁图贴上来：
| 事务A | 事务B | 事务C |
| 开始 | ||
| 表的IX锁 17 @1 | ||
| 二级索引行锁X REC NOTGAP 1059 @2 检查死锁 没事 |
||
| 表IX锁 17 @3 | ||
| 二级索引记录行锁 REC NOTGAP X WAIT 1315 @4 检查死锁，没事 |
||
| 表IX锁 17 @5 | ||
|
二级索引记录行锁 REC NOTGAP X WAIT 1315 @6
检查死锁 没事 | ||
|
对二级索引记录加删除标志（这个是最关键的） 这个交叉点就是：在锁@2与@7之间，有事务B加了锁@4，事务加了锁@6 |
||
| 聚簇索引行锁X REC NOTGAP 1059 @7 | ||
| wait.... suspend.... | wait.... suspend.... | |
| commit | ||
| wakeup this trx 将@4的WAIT去掉，成为1059 |
||
|
二级索引记录行锁 REC X WAIT 291 @8
检查死锁 发现死锁 |
思维发散：
1.
对于已经删除的记录（已经提交，但还没有purge），如果再去做删除操作，则此时还会加锁么？加什么锁？（这个问题，由于时间太紧，后面再给出验证说明，如果有兴趣，自己也可以做一下的）
2. 这个问题是在隔离级别是可重复读的情况下存在的，但如果是其它情况下会出现么？
3. 如果是根据主键删除，这个问题还会出现么？
总结：在mysql中，其实很多东西都不能按照常理来想的，这个问题本来在达梦与oracle中是根本不可想象的，根本不会出现的，所以才有一开始觉得不可能的感觉，最后才发现，原来是真的。
在这里感谢一下同事们的帮助与讨论，感谢勇哥，杰哥
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
