死锁的产生与解决

死锁的产生与解决

Hi，I’m zaki，今天，一名开发找到了我们DBA，说程序里面产生了大量的死锁，想让我们帮助分析一下死锁的原因，也正好借此机会，对mysql的锁进行了学习。

业务过程

和开发聊了聊，业务流程主要是用户每次登陆之后，都会通过一个update语句来进行更新用户的登陆次数，但是涉及到的表没有主键，使用索引之后，更新语句扫描到的行数大概在几百行左右。

查看死锁信息命令

show engine innodb status \G; 

查看关键信息

2017-03-07 15:23:14 7fe7b4ac0700
*** (1) TRANSACTION:
TRANSACTION 7688667739, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 233 lock struct(s), heap size 46632, 4 row lock(s)
MySQL thread id 337979114, OS thread handle 0x7fe702fe3700, query id 6088738244 10.0.4.19 uce_mem_xDT_rw updating
UPDATE mb_login_info SET `online`=18432  WHERE user_id=1957164 and `ymd`='2017-03-07'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1288 page no 261765 n bits 368 index `GEN_CLUST_INDEX` of table `zhubajie_member`.`mb_login_info` trx table locks 1 total table locks 2  trx id 7688667739 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 7688667740, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1783
mysql tables in use 3, locked 3
176 lock struct(s), heap size 30248, 3 row lock(s)
MySQL thread id 337979115, OS thread handle 0x7fe7b4ac0700, query id 6088738247 10.0.4.19 uce_mem_xDT_rw updating
UPDATE mb_login_info SET `online`=22699  WHERE user_id=16383730 and `ymd`='2017-03-07'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1288 page no 261765 n bits 368 index `GEN_CLUST_INDEX` of table `zhubajie_member`.`mb_login_info` trx table locks 1 total table locks 2  trx id 7688667740 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1288 page no 261495 n bits 1192 index `ymd` of table `zhubajie_member`.`mb_login_info` trx table locks 1 total table locks 2  trx id 7688667740 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (2)

信息解读

(1)代表第一个事务，执行一个update的语句，在扫描到某页的时候发现这一行已经被锁住，等待着讲行锁给到这个事务，(2)代表第二个事务，在执行update语句的时候，拿到了第一个事务等待解锁的行锁，然后在继续扫描的时候，在page no 261495的地方发现这个行锁已经被其他线程取走了，两个事务都被锁住，最终出现了死锁。

过程分析

在扫描表的过程中，扫描过的行都会被加上行锁，这个时候另一个用户进行登陆，也会对登陆次数进行更新，然后也会扫描表进行加锁。加上A事务扫描280行，B事务扫描360行，当A事务扫描到189行的时候发现操作的行已经被锁了，就需要等待锁给到A事务，原来是B事务已经在扫描的时候，锁住了A事务需要锁的行，B事务继续向前扫描，在扫描到289行的时候发现该行已经没有了锁，这时就需要等待其他事务释放锁然后给到B事务，而这行也正好是A事务扫描过并得到了该行的行锁，最后两个事务都在事务中等待互相的锁，最终造成了死锁。（文中的数字是为了方便进行分析）

解决方法

扫描行数到1行的时候（通过组合索引或者通过主键查询等），出现死锁的概率很小，但是也有几率出现死锁现象，在表上加上了一个组合索引，让查询到每一行，这样锁住的就是单独一行了。由于表示很早的表了，没有主键，所以后面建表必须有主键的SQL规范也是很有必要的，尤其是对于这种大表来说。

索引到单条记录死锁情况

上面分析的这个并发流程，完整展现了死锁日志中的死锁产生的原因。其实，根据事务1步骤6，与事务0步骤3/4之间的顺序不同，死锁日志中还有可能产生另外一种情况，那就是事务1等待的锁模式为记录上的X锁 + No Gap锁(lock_mode X locks rec but not gap waiting)。