Simple deadlock question with InnoDB AUTO INCREMENT

[Jeremy Roy]

I have been experiencing deadlocks in my application that I have boiled down to the following simple example:

-------------------------------------------------------
-- example table definition
create table test(
col_1 integer auto_increment,
col_2 char(10),
primary key (col_1)
) type = InnoDB;

-- on client 1:
begin;
insert into test (col_2) values ('test1');

-- on client 2:
begin;
insert into test (col_2) values ('test2'); -- this will hang while waiting for the PRIMARY index lock held by client 1

-- on client 1 (while 2 is waiting):
insert into test (col_2) values ('test3');
-------------------------------------------------------

The insert on client 2 will reliably fail with a detected deadlock at that point:

-------------------------------------------------------
------------------------
LATEST DETECTED DEADLOCK
------------------------
050616 19:19:20
*** (1) TRANSACTION:
TRANSACTION 0 43838, ACTIVE 5 sec, OS thread id 3488 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320
MySQL thread id 16, query id 138 localhost 127.0.0.1 root update
insert into test (col_2) values ('test2')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 714 n bits 72 index `PRIMARY` of table `test/test` trx id 0 43838 lock_mode X insert int
ention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

*** (2) TRANSACTION:
TRANSACTION 0 43837, ACTIVE 18 sec, OS thread id 5668 setting auto-inc lock, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 15, query id 139 localhost 127.0.0.1 root update
insert into test (col_2) values ('test3')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 714 n bits 72 index `PRIMARY` of table `test/test` trx id 0 43837 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/test` trx id 0 43837 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)
-------------------------------------------------------


The problem is well understood; client 1 holds a lock on a range of the PRIMARY KEY index, and needs the AUTO-INC lock. Client 2 has acquired the AUTO-INC lock at the beginning of the statement, but then needs to lock the same range in the PRIMARY KEY index.

I understand why it happens, but why doesn't InnoDB acquire the AUTO-INC lock after it gets the others, since it holds it for the shortest time?

Is there any practical workaround for this problem, other than simply re-trying the insert? I would rather the second client simply wait rather than having to catch and handle the deadlock; rolling back and retrying is undesirable because of the particulars of my application at that point. Normally I would simply re-order the operations to ensure that both threads get their locks in the same order; but I have no way to control when the AUTO-INC table lock is acquired by InnoDB. Is there a way to explicitly acquire the index lock in client 2 first, which would force it to wait for client 1 to release it?

Any help is greatly appreciated.

Thank you,

Jeremy Roy