Description:
Environment
-----------
MySQL version: 5.0.45
Operating system: Mac OS 10.5.3
Hardware: Intel Xeon
Database set up
---------------
CREATE TABLE PERSON
(
ID BIGINT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE PERSON_AUDIT
(
ID BIGINT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
AUDIT_TIME DATETIME NOT NULL,
PERSON_ID BIGINT NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (PERSON_ID) REFERENCES PERSON(ID)
);
MySQL configuration
-------------------
Default Engine: InnoDB
Transaction Isolation Level: READ-COMMITTED
Problem description
-------------------
Whenever any change is made to the PERSON table (INSERT, UPDATE or DELETE), a new record if simultaneously added to the PERSON_AUDIT table as well. The audit entry is written in the same database transaction as the actual change to the PERSON table.
Now, when more than user is accessing the system, we start getting database deadlocks. The MySQL 'SHOW INNODB STATUS\G;' command shows:
==================================================================================
------------------------
LATEST DETECTED DEADLOCK
------------------------
081111 11:29:25
*** (1) TRANSACTION:
TRANSACTION 0 6035023, ACTIVE 0 sec, OS thread id 2957864960 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 9586, query id 950435 localhost 127.0.0.1 mylogin update
insert into PERSON_AUDIT (FIRST_NAME, LAST_NAME, AUDIT_TIME, PERSON_ID) values (?, ?, ?, ?)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30068 page no 10 n bits 168 index `PRIMARY` of table `mylogin/person` trx id 0 6035023 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 0 6035022, ACTIVE 0 sec, OS thread id 2957250560 setting auto-inc lock
mysql tables in use 1, locked 1
23 lock struct(s), heap size 2496, undo log entries 15
MySQL thread id 9585, query id 950535 localhost 127.0.0.1 mylogin update
insert into PERSON_AUDIT (FIRST_NAME, LAST_NAME, AUDIT_TIME, PERSON_ID) values (?, ?, ?, ?)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 30068 page no 10 n bits 168 index `PRIMARY` of table `mylogin/person` trx id 0 6035022 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mylogin/person_audit` trx id 0 6035022 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)
==================================================================================
I was initially perplexed by this deadlock since I thought that InnoDB being a row-level locking engine should not have caused a deadlock with simultaneous inserts. However, after reading through the MySQL 5.0 manual I understood that since auto-increment identifiers were in use on both tables, the inserts were causing table-level locks.
I then upgraded by environment to 5.1.29 and set the innodb_autoinc_lock_mode parameter to 2. Even after the upgrade and the new setting, the deadlocks have continued to occur. I have tried all possible values for the innodb_autoinc_lock_mode parameter and am getting deadlocks every time.
I would like to understand what could be causing the deadlocks and how I can resolve them. Without a solution to this problem I guess my application cannot service simultaneous requests.
How to repeat:
Database set up
---------------
CREATE TABLE PERSON
(
ID BIGINT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE PERSON_AUDIT
(
ID BIGINT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
AUDIT_TIME DATETIME NOT NULL,
PERSON_ID BIGINT NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (PERSON_ID) REFERENCES PERSON(ID)
);
Subject system to some load.