Nested update with select deadlock

Question

Background

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 = ?

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 = ?

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.

Structure Of Tables

Below is a simplified version with fields only applicable to the query

Table A

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

Table B

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

Indexes

The only indexes that exist are single column indexes on both primary keys, and the foreign key to table a.

Answer 1

A plain answer to your question is:

Yes, MySql supports the FOR UPDATE clause in subqueries

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 ;

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)
------------

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; 

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:

• show the whole transaction (all commands invlolved in the transaction)
• show structures of all tables used by the transaction
• show triggers that are fired on insert/update/delete that modify tables touched by the transaction

Answer 2

If a single query fall into a dead lock, it must be a MySQL bug. An alone transaction must never end into a dead lock. Check with a unit test and into MySQL bug database.

When updating a row, some RDBMS lock the row in order to prevent complex/bad merging algorithm. May be your code is running over many transactions and both of them have dead locks ?

If the bug is proven, you can split your query (which seems very simple) :

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 : I suppose a = ? means a.id = ? ?