mysql - 如何使用事务中的 INSERT 和 UPDATE 语句来防止这种死锁

Question

我有两个表 table_a 和 table_b。在我的应用程序中，我有一个执行以下操作的业务方法:

// it is one transaction

INSERT INTO table_b (join_col) VALUES (some_value);

UPDATE table_a a 
JOIN table_b b ON a.join_col = b.join_col AND a.column_a = b.column_b
SET a.column_c = a.column_c + 1;

我正在使用 InnoDB 引擎，问题是，当我并行或几乎并行运行我的方法时，我经常收到此错误消息:

Deadlock found when trying to get lock; try restarting transaction

好像第一个事务开始更新table_a并锁定table_b，而第二个并行事务不能插入到table_b，因为它被锁定了。

如果我评论我的 UPDATE 语句，它就会开始工作。两个事务都毫无问题地插入到 table_b 中。那么，我该如何解决呢？如果重要的话，我使用的是 MySQL 5.7。

Answer 1

看起来您要先锁定表 b，然后再锁定表 a。可能 a 锁是索引锁。

试试这个，总是首先在表上获取适当的锁。

BEGIN TRANSACTION;
  SELECT COUNT(join_col) INTO @counter 
    FROM table_a a
   WHERE a.join_col = some_value
     FOR UPDATE;
  INSERT INTO table_b (join_col) VALUES (some_value);
  UPDATE table_a a 
    JOIN table_b b ON a.join_col = b.join_col 
                  AND a.column_a = b.column_b
     SET a.column_c = a.column_c + 1;
COMMIT;

INTO @counter 内容阻止第一个 SELECT 将结果集返回给您的程序。

如果这不起作用，请尝试使用 LOCK TABLES .这是一把大锤子，但很有效。

LOCK TABLES table_a, table_b WRITE;
 your queries
UNLOCK TABLES;