在MYSQL中通过唯一性索引删除同一条纪录出现死锁的分析与总结
曾经有没有遇到过，在mysql数据库中，在自动提交的模式下，单纯一个delete语句并发执行也出现死锁，当时是不是觉得很怪？而且很想知道原因。本文告诉您答案。...
文章原创，未经允许，请务转载！ 如在手机上影响阅读，请查看个人技术博客网站http://www.xuchunyang.com
我们先来看一个图片:
我们可以看到：三个会话都是执行一个完全相同的删除语句，但有一个事务（事务A）执行成功，并删除1条记录。事务B执行成功，但删除0条记录，事务C报deadlock后回滚。（特别说明一下：这个是调式结果，如果手工一个一个事务执行，是不会有死锁出现的，只有高并发的时候才会出现。）。
有没有觉得奇怪？ 我们来看一下死锁信息的输出（mysql的死锁信息打出来不全面，但输出了冲突的锁，也就够了）
执行命令 show engine innodb status G ,查看最近一次的死锁信息：
mysql> show engine innodb
status G
*************************** 1. row***************************
Type: InnoDB
Name:
Status:
=====================================
2016-01-13 10:03:20 0x7f8c3687e700 INNODB MONITOROUTPUT
=====================================
Per second averages calculated from the last 13seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 16 srv_active, 0srv_shutdown, 1470 srv_idle
srv_master_thread log flush and writes: 1486
----------
SEMAPHORES
----------
-------------
RW-LATCH INFO
-------------
Total number of rw-locks 131165
OS WAIT ARRAY INFO: reservation count 1133
OS WAIT ARRAY INFO: signal count 834
RW-shared spins 0, rounds 884, OS waits 478
RW-excl spins 0, rounds 11186, OS waits 410
RW-sx spins 1, rounds 30, OS waits 1
Spin rounds per wait: 884.00 RW-shared, 11186.00RW-excl, 30.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-01-13 09:46:39 0x7f8c367bb700
*** (1) TRANSACTION:
TRANSACTION 16434, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 rowlock(s)
MySQL thread id 11, OS thread handle 140240187025152,query id 76 localhost root updating
delete from unlockt where b=8
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60page no 4 n bits 80 index b of table"xcytest"."unlockt" trx id 16434 lock_mode X locks rec butnot gap waiting
Record lock, heap no 6 PHYSICALRECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000008; asc
;;
1: len 4; hex 80000008; asc
;;
*** (2) TRANSACTION:
TRANSACTION 16433, ACTIVE 15 sec starting index read,thread declared inside InnoDB 1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 12, OS thread handle 140240186226432,query id 75 localhost root updating
delete from unlockt where b=8
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 60page no 4 n bits 80 index b of table"xcytest"."unlockt" trx id 16433 lock_mode X locks rec butnot gap
Record lock, heap no 6PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex80000008; asc
;;
1: len 4; hex80000008; asc
;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60page no 4 n bits 80 index b of table"xcytest"."unlockt" trx id 16433 lock_mode X waiting
Record lock, heap no 6PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex80000008; asc
;;
1: len 4; hex80000008; asc
;;
*** WE ROLL BACK TRANSACTION (1)
在没有了解mysql的源码之前，上面的死锁信息真心看不懂; 感觉特别怪，尤其对于事务2.
事务2（也就是删除0条数据的事务B）： 持有了 space id 60page no 4
heap no 6
lock_mode X locks rec but not gap ， （翻译一下就是空间id为60,页号为4,行号为6 的行锁， 类型为x （排它锁），种类为 not gap 非间隙锁，因为mysql的默认级别为repeatable-read,所以有间隙锁跟非间隙锁之分，因为为唯一性索引，因此种类为not-gap） .再来看等待的锁的信息。
事务2：等待space id 60 page no 4 heap no 6 lock_mode X ，等待的锁，跟持有的锁的行号完全一样，且lock_mode 也同为X， 确实奇怪。
再来看事务1（也就是被回滚的事务c），因为事务1也在等待space id 60 page no 4
heap no6 的排它锁，该锁被事务2持有， 但事务2等待的锁，晚于事务1申请，且两个申请的锁之间冲突，因此出现死锁，事务2把事务1干掉，然后获得资源，执行后续的操作。 事务1 回滚。
为毛事务2持有了该行上的锁，还再去申请该行的锁？已经获得的锁不是白获得了不？不急，且看下面的分析。为了把问题说的足够清晰，过程会比较详细，请耐心。
当事务A（最先执行的事务）delete from unlockt where b=8;（死锁图片上是b=5，但过程是一样的，请忽略这个差异，没有再截图了，下面都是按照b=8进行死锁分析）提交之后，（如果对该sql的完整执行过程感兴趣，请打断，将做补充）释放自己所占有的锁，同时将锁授权给其他等待该锁的线程。通过函数lock_rec_dequeue_from_page来实现，规则如下：
for (lock= lock_rec_get_first_on_page_addr(lock_hash, space, page_no);
lock != NULL;
lock = lock_rec_get_next_on_page(lock)) {
if(lock_get_wait(lock)
&&!lock_rec_has_to_wait_in_queue(lock)) {
/* Grant the lock */
ut_ad(lock->trx != in_lock->trx);
lock_grant(lock);
}
}
如果该锁不是必须等待，则将锁授权。是否必须等待的条件如下：
for (lock =lock_rec_get_first_on_page_addr(hash, space, page_no);
lock != wait_lock; （wait_lock 是传入参数）
lock =lock_rec_get_next_on_page_const(lock)) {
constbyte*p = (constbyte*) &lock[1];
if (heap_no rec_hash, block, heap_no);
lock != NULL;
lock = lock_rec_get_next(heap_no, lock)) {
if (lock->trx == trx
&&!lock_rec_get_insert_intention(lock)
&& lock_mode_stronger_or_eq(
lock_get_mode(lock),
static_cast(
precise_mode & LOCK_MODE_MASK))
&& !lock_get_wait(lock)
&& (!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)) {
return(lock);
}
}
return(NULL);
}
在该示列中， 在b列索引上，请求的锁的模型Mode的位图值为1059。
根据位图表
#define LOCK_TABLE
16
2的4次方，也就是第五位为1。留下的1-4bit位表示锁的类型，是共享还是排它，是意向共享锁还是意向排它锁。
#define LOCK_REC
32
6
#define LOCK_TYPE_MASK
0xF0UL
锁类型的掩码也就使用第5到8位表示锁的类型。
#define LOCK_WAIT
256
9
#define LOCK_ORDINARY
0
#define LOCK_GAP
512
10
#define LOCK_REC_NOT_GAP1024
11
#defineLOCK_INSERT_INTENTION 2048
12
/* Basic lock modes */
enum lock_mode {
LOCK_IS = 0,
/*intention shared */
LOCK_IX,
/*intention exclusive */
LOCK_S,
/*shared */
LOCK_X,
/*exclusive */
LOCK_AUTO_INC,
/* locks theauto-inc counter of a table
in anexclusive mode */
LOCK_NONE,
/* this is usedelsewhere to note consistent read */
LOCK_NUM =LOCK_NONE, /* number of lock modes */
LOCK_NONE_UNSET= 255
};
根据上面的lock_mode,可以知道LOCK_X=3
将模型1059换算成锁的类型是：1059=1024+32+3=LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X
我们来看第五个必须满足的条件，条件如下，之间是取或的关系，只要一个满足，则第五个条件就为真。
&& (!lock_rec_get_rec_not_gap(lock)
因为lock类型为LOCK_REC_NOT_GAP，取反后不满足。
|| (precise_mode & LOCK_REC_NOT_GAP) 因为precise_mode 为3，不是一个not_gap 锁，所以也不满足。
|| heap_no == PAGE_HEAP_NO_SUPREMUM)
因为该行是个普通的行，不是极大行，因此也不满足。
所以，不满足lock_rec_has_expl 函数中，判断是否已经获得锁的必须满足的第五个条件，所以不认为该事务已经获得了满足需要的锁了，必须重新加锁。这也就是为什么在死锁输出信息中，已经持了该行上的锁，还得重新加锁的原因。
Why ??? 之前申请的锁，被赋权了，还得重新申请，之前的锁不是白申请了吗？为什么要再重新申请，这是严重的bug吗？
是为了避免所获得行锁在请求后所对应的行发生修改了吗？ 确实是的，因为在repeatable-read的事务隔离级别下，一个update语句或者delete语句，在事务内是可以连续重复执行的，对最终结果不产生影响。这一类型的事务，在请求加锁出现等待时，即使被授权后，也需要重新进行加锁。这就是repeatable事务隔离级别的限制，很多人认为这个是mysql的bug,其实不是，是repeatable-read的事务隔离级别要求导致，因为在请求锁等待到锁最终获得的这段时间，请求访问的数据可能发生了变化，在repeatable-read 的隔离级别下，必须重新加锁。
如果是read_committed的级别，则在索引查找上加的是LOCK_ORDINARY类型的锁，请求的锁类型应该是mode值35，即32+0+3=LOCK_REC|LOCK_ORDINARY|LOCK_X ，则就满足上面的条件，因此不需要再重新申请锁。
因为需要重新申请锁，则需要判断申请的锁，是否跟现有的请求的锁冲突。于是就需要执行下面的步骤
lock_rec_other_has_conflicting， 用于检查申请锁跟其他的锁之间的冲突（即申请者跟占有者之间的冲突，申请者跟申请者之间的冲突）。 如果没有冲突，则直接获得锁，如果有冲突，则需要打waiting标记，加入到请求队列中，等待持有锁的事务释放锁。
因为另外一个事务C同样也在做deletefrom unlockt where b=8 的操作，因此其也在等待b列索引上b=8的行，所以有锁冲突，不能直接加锁，因此（然后）要放入到请求队列中。
如是就有了：
err =rec_lock.add_to_waitq(wait_for);
RecLock::add_to_waitq函数会做死锁检查（申请者跟申请者之间是否形成 环 ），因为事务C同样也在等待该行的锁，所以必冲突。
进入死锁检查之后，发现冲突，然后会选择一个权限低的踢掉，本例是把对方踢掉，即把事务C踢掉。
mysql> delete from unlockt where b=8;
ERROR 1213 (40001): Deadlock found when trying toget lock; try restarting transaction
事务c所占用的锁将释放。 因此事务B将直接获得锁，不再返回DB_WAIT_LOCK值，而是直接返回DB_SUCCESS,加锁成功，无需进入等待队列。然后返回上层函数。
返回row_search_mvcc之后，则需要检查数据是否被删除，如是，需要有下面的判断。
if(rec_get_deleted_flag(rec, comp)) {
if ((srv_locks_unsafe_for_binlog
|| trx->isolation_levelselect_lock_type != LOCK_NONE
&&!did_semi_consistent_read) {
row_unlock_for_mysql(prebuilt, TRUE);
}
goto next_rec;
}
因为返回的行已经被事务A删除，所以是一个删除标记的行，所以需要进行判断，如果同时满足如下条件：
1. srv_locks_unsafe_for_binlog 为1 或者trx->isolation_levelselect_lock_type != LOCK_NONE(满足）
3. !did_semi_consistent_read
（满足）
因此，如果当隔离级别为read_commit的话，则将刚才加锁的标记为delete的行的锁释放掉。然后进行一下行查找,会加给行加上LOCK_GAP标签。找到下一行之后，进行对比，发现不等于8，比8大，则以DATA_NO_FIND返回给
线程，最终事务B以0条数据修改返回。事务B的线程把事务A rollback掉，自己最终已0条记录修改返回。
mysql> delete fromunlockt where b=8;
Query OK, 0 rowsaffected (2 min 37.13 sec)
对于删除同一行记录出现死锁的情况，归根为事务隔离级别所导致，当锁请求获得授权之后，在repeatable的隔离级别下，对于在唯一性索引上的加锁，需要再次进行加锁，在并发删除同一条记录的情况下，可能出现锁冲突。
说到这里，大家绝对不禁有疑问，为啥在并行删除的时候，很多数情况下，是没有冲突的呢？这是为什么？ 原因在于出现冲突跟不冲突，加锁的类型有稍微的差异，在row_search_mvcc(一致性查找函数）中，有下面这一段代码：
if (prebuilt->select_lock_type!= LOCK_NONE) {
/* Try to place a lock on the index record; note thatdelete
markedrecords are a special case in a unique search. If there
is anon-delete marked record, then it is enough to lock its
existencewith LOCK_REC_NOT_GAP. */
/* If innodb_locks_unsafe_for_binlog option is used
or thissession is using a READ COMMITED isolation
level welock only the record, i.e., next-key locking is
not used. */
ulint
lock_type;
if(!set_also_gap_locks
|| srv_locks_unsafe_for_binlog
|| trx->isolation_level
关注 数据库随笔
微信扫一扫关注公众号
