mysql中删除同一行会经常出现死锁？太可怕了

之前有一个同事问到我，为什么多个线程同时去做删除同一行数据的操作，老是报死锁，在线上已经出现好多次了，我问了他几个问题：

 

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';

*** (1) 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 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

ating

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

`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)

===================================================

这是在三个线程的情况下是可以重现死锁的，但是为了更容易调试，试了一下在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个条件：

1. 首先这个锁是自己事务的
2. 这个锁不是处于等待状态
3. 当前锁的类型与precise_mode是兼容的，precise_mode值是X锁，因为这里是要做删除
4. 当前锁不是NOT GAP类型，或者要加的锁类型是NOTGAP类型的，或者heapno为1
5. 当前锁不是GAP类型，或者要加的锁类型是GAP类型的，或者heapno为1
6. 当前锁不是意向插入锁

但此时发现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 检查死锁 没事
对二级索引记录加删除标志（这个是最关键的） 因为这个事件必须要与事务B有交叉点 这个交叉点就是：在锁@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中是根本不可想象的，根本不会出现的，所以才有一开始觉得不可能的感觉，最后才发现，原来是真的。

 

在这里感谢一下同事们的帮助与讨论，感谢勇哥，杰哥