Avoiding MySQL deadlock when upgrading shared to exclusive lock

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?

Solution

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