在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
关注 数据库随笔
微信扫一扫关注公众号