Bug #40670 Getting database deadlocks on simultaneous inserts
Submitted: 12 Nov 2008 15:11 Modified: 14 Dec 2008 19:16
Reporter: Agrieved User Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.29 OS:MacOS (10.5.3)
Assigned to: CPU Architecture:Any

[12 Nov 2008 15:11] Agrieved User
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.
[14 Nov 2008 19:16] Valeriy Kravchuk
Thank you for a problem report. Please, provide a repeatable test case. At least, send exact statements that forms a transaction. I assume you first insert into PERSON and then into PERSON_AUDIT table, all in one transaction. But I want ot be sure.
[15 Dec 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".