Avoiding MySQL deadlock when upgrading shared to exclusive lock

Question

I'm using MySQL 5.5. I've noticed a peculiar deadlock occurring in a concurrent scenario, and I don't think this deadlock should occur.

Reproduce like this, using two mysql client sessions running simultaneously:

mysql session 1:

create table parent (id int(11) primary key); insert into parent values (1); create table child (id int(11) primary key, parent_id int(11), foreign key (parent_id) references parent(id));  begin; insert into child (id, parent_id) values (10, 1); -- this will create shared lock on parent(1) 

mysql session 2:

begin; -- try and get exclusive lock on parent row select id from parent where id = 1 for update; -- this will block because of shared lock in session 1 

mysql session 1:

-- try and get exclusive lock on parent row select id from parent where id = 1 for update; -- observe that mysql session 2 transaction has been rolled back 

mysql session 2:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 

The information reported from show engine innodb status is this:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 161207 10:48:56 *** (1) TRANSACTION: TRANSACTION 107E67, ACTIVE 43 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 13074, OS thread handle 0x7f68eccfe700, query id 5530424 localhost root statistics select id from parent where id = 1 for update *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E67 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  0: len 4; hex 80000001; asc     ;;  1: len 6; hex 000000107e65; asc     ~e;;  2: len 7; hex 86000001320110; asc     2  ;;  *** (2) TRANSACTION: TRANSACTION 107E66, ACTIVE 52 sec starting index read mysql tables in use 1, locked 1 5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 12411, OS thread handle 0x7f68ecfac700, query id 5530425 localhost root statistics select id from parent where id = 1 for update *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  0: len 4; hex 80000001; asc     ;;  1: len 6; hex 000000107e65; asc     ~e;;  2: len 7; hex 86000001320110; asc     2  ;;  *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  0: len 4; hex 80000001; asc     ;;  1: len 6; hex 000000107e65; asc     ~e;;  2: len 7; hex 86000001320110; asc     2  ;;  *** WE ROLL BACK TRANSACTION (1) 

You can see that transaction (1) doesn't show any S or X locks already acquired; it's just blocked trying to acquire an exclusive lock. Since there's no cycle, there shouldn't be a deadlock in this situation, as I understand it.

Is this a known MySQL bug? Have other people encountered it? What workarounds were used?

These are the possible steps forward we could take:

• Reduce our usage of foreign keys (in our production scenario, we only soft delete rows in the referenced table, but is icky)
• Acquire exclusive locks up front rather than implicit shared locks (will reduce our concurrent throughput)
• Change our logic so we no longer need an exclusive lock on parent in same transaction that adds child row (risky and hard)
• Change our version of MySQL to one that doesn't exhibit this behaviour

Are there other options we're not considering?

Answer 1

This is a long standing bug which you can read more from: This bug report

This is a problem in MySQL-level table locking.

Internally inside InnoDB, a FOREIGN KEY constraint check may read (or, with ON UPDATE or ON DELETE clause, write) parent or child tables.

Normally, table access is governed by the following locks: 1. MySQL meta-data lock 2. InnoDB table lock 3. InnoDB record locks

All these locks are held until the end of the transaction.

The InnoDB table and record locks are skipped in certain modes, but not during foreign key checks. The deadlock is caused because MySQL acquires the meta-data lock only for the table(s) that are explicitly mentioned in the SQL statements.

I guess that a workaround could be to access the child (or parent) tables at the start of the transaction, before the problematic FOREIGN KEY operation.

Read the discussion and it's reply's

Answer 2

The reason for updating the parent row was not given, but I would assume this has to do with some de normalization, based on this sequence from the question:

-- session 1 begin; insert into child (id, parent_id) values (10, 1); ... select id from parent where id = 1 for update; 

For example, an order (parent table) has a column amount, which is maintained as the sum of the amounts of all order lines (child table).

It seems the logic to maintain the parent data is coded in the application itself (with explicit update statements), which has the following consequences:

• If insert into child is done in many different places, then the application logic in the client must be updated in all these places to maintain integrity. This is code duplication.

• Even if this is done in only one place, the fact that the parent table needs to be updated when adding a child is impossible for the server to find out.

Instead, please consider the following option:

Define triggers on the child table, that update the parent table as needed.

It has the following implications:

• First, the logic to maintain the parent table is no longer (possibly) duplicated, as it is in the trigger itself.

• Second, and this is the important part here, the MySQL server now knows that the table parent is updated whenever a child record is inserted, and because of this, the proper lock (exclusive instead of shared) is taken.

Tested with 8.0, see below.

Regarding the concern about concurrency throughput,

• different transactions operating on different parent rows will execute in parallel, as exclusive locks are taken on the parent (different) rows, not the parent table.

• transactions operating concurrently on the same parent row will indeed be serialized ... which is actually the expected result, as they complete on the same record anyway.

Serializing transactions that are guaranteed to succeed should provide better throughput (as far as the application workload is concerned) that having some transaction fail, only to retry them.

Obviously, update and delete triggers should be needed also, to update the parent as well, depending on the application logic.

Setup

create table parent (   id int(11) primary key,   number_of_children int(11));  create table child (   id int(11) primary key,   parent_id int(11),   foreign key (parent_id) references parent(id));  delimiter $$; create trigger bi_child before insert on child for each row begin   update parent     set number_of_children = number_of_children + 1     where id = NEW.parent_id; end $$ delimiter ;$$  begin; insert into parent values (1, 0); insert into parent values (2, 0); commit; 

Session 1

begin; insert into child values (10, 1); 

Session 2

begin; insert into child values (20, 2); 

Not blocked, as a different parent is used.

Session 3

begin; -- this now blocks, waiting for an X lock on parent row 1. insert into child values (11, 1); 

Session 1

-- unlocks session 3 commit; 

Session 3

commit;

Session 2

commit;

Results

select * from parent; id      number_of_children 1       2 2       1