MySQL Forums
Forum List  »  InnoDB

Simple deadlock question with InnoDB AUTO INCREMENT
Posted by: Jeremy Roy
Date: June 16, 2005 05:30PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple deadlock question with InnoDB AUTO INCREMENT
11119
June 16, 2005 05:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.