带有选择死锁的嵌套更新

Question

背景

我正在使用一些代码，这些代码似乎与死锁(经常与其本身一起)有关。在Java中，它周期性地生成一个DeadLockLoserDataAccessException，导致死锁的违规语句通常是本身的。(这是在使用InnoDB的事务中运行的)

UPDATE a
SET
    a_field = (SELECT sum(b_field) FROM b WHERE b.a_id = a.id)
WHERE 
    a = ?

在进行了一些阅读之后，我遇到了执行锁定读取的FOR UPDATE子句。所以我修改了下面的代码

UPDATE a
SET
    a_field = (SELECT sum(b_field) FROM b WHERE b.a_id = a.id FOR UPDATE)
WHERE 
    a = ?

问题

在嵌套的FOR UPDATE中添加UPDATE/SELECT锁合适吗？锁定读取文档上的任何一个示例都没有以这种方式使用FOR UPDATE。

表的结构

下面是一个简化的版本，其中的字段仅适用于查询

表A

id      int(11) PRIMARY KEY
a_field int(11)

表B

id      int(11) PRIMARY KEY
a_id    int(11) FOREIGN KEY REFERENCES (a.id)
b_field int(11)

索引

存在的唯一索引是主键上的单列索引和表a的外键。

Answer 1

对你的问题的一个简单的回答是：

是的，MySql支持子查询中的FOR UPDATE子句

这肯定不是解决你的问题的办法。

在这种情况下，子查询中的FOR更新不会阻止死锁

由于您没有向我们展示整个事务，而只显示了一个代码段，我猜事务中肯定还有其他命令，这些命令对外键引用的记录设置了一个锁。

要更好地理解MySql中的锁定是如何工作的，请看下面这个简单的示例：

CREATE TABLE `a` ( 
   `id` int(11) primary key AUTO_INCREMENT, 
   `a_field` int(11) 
);
CREATE TABLE `b` ( 
   `id` int(11) primary key AUTO_INCREMENT, 
   `a_id` int(11), 
   `b_field` int(11),
   CONSTRAINT `b_fk_aid` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
);
CREATE TABLE `c` ( 
   `id` int(11) primary key AUTO_INCREMENT, 
   `a_id` int(11), 
   `c_field` int(11),
   CONSTRAINT `c_fk_aid` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
);

insert into a( a_field ) values ( 10 ), ( 20 );
insert into b( a_id, b_field ) values ( 1, 20 ), ( 2, 30 );

delimiter $$
create procedure test( p_a_id int, p_count int )
begin
   declare i int;
   set i = 0;
   REPEAT
      START TRANSACTION;
      INSERT INTO c( a_id, c_field ) values ( p_a_id, round(rand() * 100) );
      UPDATE a
         SET  a_field = (
                   SELECT sum(b_field) 
                   FROM b WHERE b.a_id = a.id 
                   FOR UPDATE)
         WHERE 
                id = p_a_id;
       commit; 
       set i = i + 1;
   until i > p_count 
   end repeat;
end $$
DELIMITER ;

注意，在子查询中使用了FOR UPDATE。

如果我们同时在两个会话中执行该过程：

call test( 2, 400 );

我们几乎同时得到一个死锁错误：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2013-09-05 23:08:27 1b8c
*** (1) TRANSACTION:
TRANSACTION 1388056, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 0x1db0, query id 3107246 localhost 127.0.0.1 test updating
UPDATE a
         SET  a_field = (
                   SELECT sum(b_field) 
                   FROM b WHERE b.a_id = a.id 
                   FOR UPDATE)
         WHERE 
                id = p_a_id
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 1388056 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000152e16; asc     . ;;
 2: len 7; hex 2d0000013b285a; asc -   ;(Z;;
 3: len 4; hex 8000001e; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1388057, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x1b8c, query id 3107247 localhost 127.0.0.1 test updating
UPDATE a
         SET  a_field = (
                   SELECT sum(b_field) 
                   FROM b WHERE b.a_id = a.id 
                   FOR UPDATE)
         WHERE 
                id = p_a_id
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 222 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 1388057 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000152e16; asc     . ;;
 2: len 7; hex 2d0000013b285a; asc -   ;(Z;;
 3: len 4; hex 8000001e; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 1388057 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000152e16; asc     . ;;
 2: len 7; hex 2d0000013b285a; asc -   ;(Z;;
 3: len 4; hex 8000001e; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------

正如您所看到的，MySql报告说死锁错误是由同一两个UPDATes引起的。

然而，这只是事实的一半。

死锁错误的真正原因是INSERT INTO c语句，它在A表中的引用记录上放置了一个共享锁(因为C表中的外键约束)。

令人惊讶的是，为了防止死锁，必须在事务开始时对A表中的一行设置一个锁：

  declare dummy int;
  ...... 
  START TRANSACTION;
      SELECT id INTO dummy FROM A 
      WHERE id = p_a_id FOR UPDATE;
      INSERT INTO c( a_id, c_field ) values ( p_a_id, round(rand() * 100) );
      UPDATE a
         SET  a_field = (
                   SELECT sum(b_field) 
                   FROM b WHERE b.a_id = a.id 
              )
         WHERE 
                id = p_a_id;
       commit; 

在进行此更改后，过程将无死锁地运行。

因此，您可以尝试在处理事务时添加SELECT ... FROM A ... FOR UPDATE。

但是，如果这样做行不通，为了得到进一步的帮助来解决这个问题，请：

• 显示整个事务(事务中调用的所有命令)
• 显示事务所使用的所有表的结构。
• 显示在insert/update/delete上触发的触发器，这些触发器修改事务所触摸的表

Answer 2

如果单个查询落入死锁，则必须是MySQL错误。单独的事务绝不能以死锁结束。使用单元测试检查并进入MySQL错误数据库。

在更新一行时，一些RDBMS会锁定该行，以防止复杂/糟糕的合并算法。可能是您的代码正在运行在许多事务上，而且它们都有死锁？

如果该错误得到证实，您可以拆分查询(这似乎非常简单)：

SELECT id FROM a WHERE id=? FOR UPDATE;
SELECT SUM(b_field) FROM b WHERE b.a_id=?;
UPDATE a SET a_field=? WHERE id=?;
COMMIT

PS :我想a = ?的意思是a.id = ?？