带有选择死锁的嵌套更新 [英] Nested update with select deadlock
问题描述
我正在使用一些似乎经常会自身死锁的代码.在Java中，它会定期生成DeadLockLoserDataAccessException，而导致死锁的令人反感的语句通常是本身 . (这正在与InnoDB的事务中运行)
I am using some code that seems to deadlock often with itself. In Java, it produces a DeadLockLoserDataAccessException periodically, and the offending statement causing the deadlock is often itself. (This is being run in a transaction with InnoDB)
UPDATE a
SET
a_field = (SELECT sum(b_field) FROM b WHERE b.a_id = a.id)
WHERE
a = ?
做完一些读取后，我遇到了FOR UPDATE子句，它执行了锁定读取.所以我修改了下面的代码
After doing some reading, I came across the FOR UPDATE clause performing a locking read. So I modified the code below
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锁是否合适? 锁定阅读文档上的示例均不存在.以这种方式使用FOR UPDATE.
Question
Is it proper to add the FOR UPDATE lock inside a nested UPDATE/SELECT? None of the examples on the Locking Reads Documentation use FOR UPDATE in this way.
下面是简化版，其中的字段仅适用于查询
Below is a simplified version with fields only applicable to the query
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的外键.
Indexes
The only indexes that exist are single column indexes on both primary keys, and the foreign key to table a.
推荐答案
您的问题的简单答案是:
A plain answer to your question is:
是的，MySql在子查询中支持
FOR UPDATE子句
请确保一定不能解决您的问题.
在这种情况下，子查询中的FOR UPDATE不能防止死锁
由于您没有向我们展示整个事务，而只是一个摘要，因此我猜测事务中还必须存在其他一些命令，该命令将锁定由外键引用的记录.
为了更好地了解MySql中的锁定如何工作，请看以下简单示例:
Hovewer this for sure is not solution to your problem.
The FOR UPDATE in the subquery doesn't prevent deadlocks in this case
Since you didn't show us the whole transaction, but only a snippet, my guess is that there must be some other command in the transaction that places a lock on a record referenced by the foreign key.
To get better understandeing of how locking in MySql works, take a look at this simple example:
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.
如果我们在两个会话中同时执行该过程，则:
Notice that FOR UPDATE is used in the subquery.
If we execute the procedure in two session at the same time:
call test( 2, 400 );
我们几乎立即收到一个死锁错误:
we get, almost at once, a deadlock error:
------------------------
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表中的FOREIGN KEY约束).
而且-令人惊讶的是-为防止死锁，必须在事务开始时在A表中的行上放置锁:
As you see, MySql reports that the deadlock error is caused by the same two UPDATes.
However, this is only a half of the truth.
The true reason of the deadlock error is the INSERT INTO c statement, that placed a shared lock on a referenced record in the A table (because of the FOREIGN KEY constraint in the C table).
And - surprisingly - to prevent the deadlock, there must be placed a lock on a row in A table at the beginning of the transaction:
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.
但是，如果这行不通，请寻求进一步的帮助来解决此问题，
After this change the procedure runs without deadlocks.
Thus, you can try to add SELECT ... FROM A ... FOR UPDATE at the beggining of your transaction.
But if this won't work, to get further help to solve this problem, please:
- 显示整个交易(交易中包含所有命令)
- 显示交易使用的所有表的结构
- 显示在插入/更新/删除时触发的触发器，用于修改事务处理的表
这篇关于带有选择死锁的嵌套更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
