最近开发的时候遇到一个问题, 就是两个进程分别执行多表联合删除的时候, 会出现死锁的现象. 研究了很久终于明白了是什么原因导致的, 于是总结记录一下.

两个删除的 SQL 分别如下: 

-- TRANSACTION(1)：
DELETE P FROM
    EXEC_TBL AS E
INNER JOIN
    PROG_TBL AS P
ON
    E.USER_ID = P.USER_ID
WHERE
    E.EXE_TIME <= NOW();

-- TRANSACTION(2)：
DELETE E, P FROM
    EXEC_TBL AS E
LEFT JOIN
    PROG_TBL AS P
ON
    E.USER_ID = P.USER_ID
WHERE
    E.USER_ID = 'user@domain.com'

死锁后, 通过如下命令查看详细信息: 

-- 查看死锁详细信息
SHOW ENGINE INNODB STATUS

得到如下报错: 

------------------------
LATEST DETECTED DEADLOCK
------------------------
220915  3:55:53
*** (1) TRANSACTION:
TRANSACTION A859F2, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1248, 6 row lock(s)
MySQL thread id 1954, OS thread handle 0x7f82fe0c9700, query id 315491 localhost 127.0.0.1 root Sending data
DELETE P FROM
            EXEC_TBL AS E
        INNER JOIN
            PROG_TBL AS P
        ON
            E.USER_ID = P.USER_ID
        WHERE
            E.EXE_TIME <= NOW()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 860 n bits 160 index `PRIMARY` of table `lane`.`EXEC_TBL` trx id A859F2 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION A859F1, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 2326, OS thread handle 0x7f82c7159700, query id 315490 localhost 127.0.0.1 root statistics
DELETE E, P FROM
            EXEC_TBL AS E
        LEFT JOIN
            PROG_TBL AS P
        ON
            E.USER_ID = P.USER_ID
        WHERE
            E.USER_ID = 'user@domain.com'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 860 n bits 160 index `PRIMARY` of table `lane`.`EXEC_TBL` trx id A859F1 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1199 n bits 160 index `PRIMARY` of table `lane`.`PROG_TBL` trx id A859F1 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

仔细阅读死锁信息可知:

  • TRANSACTION(1) 拿到了 PROG_TBL 的 S lock, 尝试去拿 EXEC_TBL 的 S lock, 但是失败了.
  • TRANSACTION(2) 拿到了 EXEC_TBL 的 X lock, 尝试去拿 PROG_TBL 的 X lock, 同样失败了.

确实是两者同时锁住了对面想锁住的信息, 因此导致了死锁.

但是有一点很奇怪, 两个删除操作的书写顺序都是 EXEC_TBL 在前 PROG_TBL 在后, 那么他们的锁表顺序其实都应该是先 E 表, 然后 P 表.

那么为什么 TRANSACTION(1) 会先去锁住 PROG_TBL 表呢?

原因就在于 TRANSACTION(1) 使用的 INNER JOIN 并未强制要求必须先读取左边的表( LEFT JOIN 是会保证先读取左表的). INNER JOIN在执行的时候, MySQL 的优化器有可能会介入, 在 INNER JOIN 左右两张表数据量相差较大的时候, INNER JOIN并不会保证按照从左到右的顺序读取执行, 而是会选择一个数据量较小的表作为驱动表来先读取.

本次我遇到的情况中就是, 虽然 E 表书写在前, 但是实际环境中 E 表的数据量是远远大于 P 表的, 所以优化器执行的时候选择了先读取 P 表(右表)作为驱动表, 导致两者锁表顺序不一致从而发生了死锁.

如果要避免这种情况, 就需要改造 SQL 的写法. 如果依然要使用 INNER JOIN 但是又要保证读取和锁表顺序是从左往右的话, 那么可以将 INNER JOIN 改为 STRAIGHT_JOIN(注意下划线): 

-- TRANSACTION(1)：
DELETE P FROM
    EXEC_TBL AS E
STRAIGHT_JOIN
    PROG_TBL AS P
ON
    E.USER_ID = P.USER_ID
WHERE
    E.EXE_TIME <= NOW();

如此, JOIN 的结果与 INNER JOIN 一致, 但是可以强制要求 MySQL 按从左往右的顺序执行, 永远使用左表作为驱动表.

作为验证的话, 可以使用 EXPLAIN 命令查看.

首先在数据库的 E 表中创建大量数据(保证 E 表数据量远大于 P 表), 然后将 DELETE 命令临时改造成相应的 SELECT 便于使用 EXPLAIN, 然后分别执行下面两个 SQL: 

-- 命令1, 结果是先读取P表然后E表
EXPLAIN SELECT * FROM
    EXEC_TBL AS E
INNER JOIN
    PROG_TBL AS P
ON
    E.USER_ID = P.USER_ID
WHERE
    E.EXE_TIME <= NOW();
    
-- 命令2, 结果是先读取E表然后P表
EXPLAIN SELECT * FROM
    EXEC_TBL AS E
STRAIGHT_JOIN
    PROG_TBL AS P
ON
    E.USER_ID = P.USER_ID
WHERE
    E.EXE_TIME <= NOW();

如果在开发中遇到类似的问题导致的死锁问题, 可以考虑按照以上的方向排查和修改.

Comments
Write a Comment