All geek questions in one place

Nested update with deadlock selection

Background

I use some code that often looks dead end with myself. In Java, it periodically DeadLockLoserDataAccessException , and the statement causing the deadlock is often itself . (This is done 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 some reading, I came across a FOR UPDATE clause that reads with a lock. So I changed 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 correct to add a FOR UPDATE lock inside a nested UPDATE/SELECT ? None of the examples in Locking Reads Documentation use FOR UPDATE in this way.

Table structure

The following is a simplified version with fields applicable only to the request

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)

Indices

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

+4
source share
2 answers

A simple answer to your question:

Yes, MySql supports the FOR UPDATE clause in subqueries

Hovewer is definitely not the solution to your problem.
FOR UPDATE in the subquery does not prevent deadlocks in this case

Since you did not show us the whole transaction, but only a fragment, I assume that there must be some other command in the transaction that puts the lock on the record referenced by the foreign key.

To better understand how locking works in MySql, 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 ;

Note that FOR UPDATE used in the subquery. If we perform the procedure for two sessions simultaneously:

 call test( 2, 400 );

we get a deadlock almost immediately:

 ------------------------ 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 can see, MySql reports that the deadlock error was caused by the same two UPDATs. However, this is only half the truth. The true cause of the deadlock error is the INSERT INTO c statement that it placed a shared lock referenced by an entry in the table (due to a foreign key constraint in the C table).

And - surprisingly - to prevent a deadlock, a row in table A should be placed 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 is executed without deadlocks. So you can try adding SELECT ... FROM A ... FOR UPDATE at the beginning of your transaction.

But if this does not work, to get additional help to solve this problem, please:

  • show the whole transaction (all commands nested in the transaction)
  • show structures of all tables used by the transaction
  • show triggers that start on insert / update / delete that change the tables affected by the transaction.
+2
source

If one request falls into deadlock, it must be a MySQL error. One transaction should never end in deadlock. Check with unit test and into MySQL MySQL database .

When updating a row, some RDBMSs block the row to prevent a complex / bad merge algorithm. Maybe your code runs on many transactions, and both of them have deadlocks?

If the error 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 that a = ? means a.id = ? ?

+1
source

Source: https://habr.com/ru/post/1497840/

More articles:


All Articles