数据库死锁 对 gap lock 的理解

最近，在项目开发过程中，碰到了数据库死锁问题，在解决问题的过程中，笔者对MySQL InnoDB引擎锁机制的理解逐步加深。

案例如下：

        表: TSK_TASK

        字段: 
                MON_TIME：监测时间；
                STATUS_ID：任务状态；
        索引：
                 索引一 : 主键索引 PRIMARY
                 索引二 : KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

两条语句

        事务(TRANSACTION) 一 :   update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME

        事务(TRANSACTION) 二 :   update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)

    

在使用Show innodb status检查引擎状态时，发现了死锁问题：

*** (1) TRANSACTION:

        TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read

        mysql tables in use 1, locked 1

        LOCK WAIT 3 lock struct(s), heap size 320

        MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update

        update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

        RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting

        Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

        0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

---

*** (2) TRANSACTION :

        TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499

        mysql tables in use 1, locked 1

        3 lock struct(s), heap size 320, undo log entries 1

        MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating

        update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)

*** (2) HOLDS THE LOCK(S):

        RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap

        Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

        0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

        RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting

Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;

*** WE ROLL BACK TRANSACTION (1)

分析，涉及的两条语句应该不会涉及相同的TSK_TASK记录，那为什么会造成死锁呢？

   查询MySQL官网文档，发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁，我原来的理解是直接对记录进行锁定，实际上并不是这样的。

   要点如下:

    在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking ( 行锁 +  两头 间隙锁 )；如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000会锁定所有主键大于等于1000的所有记录，在该语句完成之前，你就不能对主键等于10000的记录进行操作；当非簇索引(non-cluster index)记录被锁定时，相关的簇索引(cluster index ,通常是主键索引)记录也需要被锁定才能完成相应的操作。

    再分析一下发生问题的两条SQL语句，就不难找到问题所在了：当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME. 假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)”几乎同时执行时，本语句首先锁定簇索引(主键)，由于需要更新STATUS_ID的值，所以还需要锁定KEY_TSKTASK_MONTIME2的某些索引记录。

   这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录，等待主键索引，而第二条语句则锁定了主键索引记录，而等待KEY_TSKTASK_MONTIME2的记录，在此情况下，死锁就产生了。

   笔者通过拆分第一条语句解决死锁问题：先查出符合条件的ID：select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute)；然后再更新状态：update TSK_TASK set STATUS_ID=1064 where ID in (….)

   至此，死锁问题彻底解决。