java – 使用select deadlock的嵌套更新

背景

我正在使用一些似乎经常与自身发生死锁的代码.在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 = ?

题

在嵌套的UPDATE / SELECT中添加FOR UPDATE锁是否合适？ Locking Reads Documentation上的所有示例都没有以这种方式使用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的外键.

最佳答案

您问题的简单答案是：

Yes,MysqL supports the FOR UPDATE clause in subqueries

Hovewer肯定不能解决您的问题.
子查询中的FOR UPDATE在这种情况下不会阻止死锁
由于您没有向我们展示整个事务,而只是一个片段,我的猜测是在事务中必须有一些其他命令对外键引用的记录设置锁定.

为了更好地了解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,`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,locked 2
5 lock struct(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报告死锁错误是由相同的两个UPDAT引起的.
然而,这只是事实的一半.
死锁错误的真正原因是INSERT INTO c语句,该语句在A表中的引用记录上放置了共享锁(因为C表中的FOREIGN KEY约束).
并且 – 令人惊讶的是 – 为了防止死锁,必须在事务开始时在A表中的一行上放置一个锁：

  declare dummy int;
  ...... 
  START TRANSACTION;
      SELECT id INTO dummy FROM A 
      WHERE id = p_a_id FOR UPDATE;
      INSERT INTO c( 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.但如果这不起作用,为了进一步帮助解决这个问题,请：

>显示整个事务(事务中涉及的所有命令)
>显示事务使用的所有表的结构
>显示在插入/更新/删除时触发的触发器,用于修改事务触及的表

java – 使用select deadlock的嵌套更新

相关文章