java - Nested update with select deadlock -

background

i using code seems deadlock itself. in java, produces deadlockloserdataaccessexception periodically, , offending statement causing deadlock itself. (this being run in transaction innodb)

update set     a_field = (select sum(b_field) b b.a_id = a.id)      = ? 

after doing reading, came across for update clause performing locking read. modified code below

update set     a_field = (select sum(b_field) b b.a_id = a.id update)      = ? 

question

is proper add for update lock inside nested update/select? none of examples on locking reads documentation use for update in way.

structure of tables

below simplified version fields applicable 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 indexes exist single column indexes on both primary keys, , foreign key table a.

a plain answer question is:

yes, mysql supports for update clause in subqueries

hovewer sure not solution problem.
update in subquery doesn't prevent deadlocks in case

since didn't show whole transaction, snippet, guess there must other command in transaction places lock on record referenced foreign key.

better understandeing of how locking in mysql works, take @ 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 a( a_field ) values ( 10 ), ( 20 ); insert b( a_id, b_field ) values ( 1, 20 ), ( 2, 30 );  delimiter $$ create procedure test( p_a_id int, p_count int ) begin    declare int;    set = 0;    repeat       start transaction;       insert c( a_id, c_field ) values ( p_a_id, round(rand() * 100) );       update          set  a_field = (                    select sum(b_field)                     b b.a_id = a.id                     update)                           id = p_a_id;        commit;         set = + 1;    until > p_count     end repeat; end $$ delimiter ; 

notice for update used in subquery.
if execute procedure in 2 session @ same time:

call test( 2, 400 ); 

we get, @ once, 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          set  a_field = (                    select sum(b_field)                     b b.a_id = a.id                     update)                           id = p_a_id *** (1) waiting lock 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 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          set  a_field = (                    select sum(b_field)                     b b.a_id = a.id                     update)                           id = p_a_id *** (2) holds 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 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 lock 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 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     ;;  *** roll transaction (2) ------------ 

as see, mysql reports deadlock error caused same 2 updates.

however, half of truth.

true reason of deadlock error insert c statement, placed shared lock on referenced record in table (because of foreign key constraint in c table).

, - surprisingly - prevent deadlock, there must placed lock on row in a table @ beginning of transaction:

  declare dummy int;   ......    start transaction;       select id dummy        id = p_a_id update;       insert c( a_id, c_field ) values ( p_a_id, round(rand() * 100) );       update          set  a_field = (                    select sum(b_field)                     b b.a_id = a.id                )                           id = p_a_id;        commit;  

after change procedure runs without deadlocks.

thus, can try add select ... ... update @ beggining of transaction.

but if won't work, further solve problem, please:

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