SQLSTATE[40001]: Serialization failure: 1213 Deadlock issue caused by INSERT trigger on concurrent access

Question

I found a SQL deadlock issue which occurs when the function is executed concurrently by two users. I have a PHP function which executes several database insert queries which enclose in a transaction. And one of the inserts fires a trigger as well. See my table schema and code sample below.

main_table

CREATE TABLE `main_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

history_table

CREATE TABLE `history_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `audit_id` INT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

audit_table

CREATE TABLE `audit_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have a trigger on main_table, defined as follows. What it does is that selecting the max id from audit_table and insert a record to the history_table.

CREATE TRIGGER watch_insert_main_table
AFTER INSERT ON main_table
FOR EACH ROW BEGIN
    DECLARE max_id INT;
    SET max_id = (SELECT MAX(`id`) FROM `audit_table`) + 1;
    INSERT INTO `history_table` SET audit_id=max_id;
END;

Following is the function which is executed by two users concurrently. insertRecord funtion simply inserts a record to the given table.

try {
    $dbConnection->beginTransaction();
    $this->insertRecord('main_table');    
    $this->insertRecord('audit_table');
    $dbConnection->commit();    
} catch (Exception $e) {
    $dbConnection->rollback();    
}

I get the following dead lock error when the function is called for the second time (concurrently).

40001 - SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

If I do either of the following changes, this issue won't occur.

• Remove the enclosed transaction in the PHP code
• Remove $this->insertRecord('audit_table'); line from the PHP code
• Modify the trigger so that it doesn't have a select statement from the audit_table

I want to know the root cause of this issue. Is another transaction being starting from the MySQL trigger when it is fired? How are the transactions and locks working inside a trigger?

I found that the following two questions are also related to a similar issue.

• MySQL Deadlock with an an insert that raises a trigger
• Deadlock caused by INSERT trigger - thread looking for lock it already has?

Answer 1

See if this simplification helps:

CREATE TRIGGER watch_insert_main_table
AFTER INSERT ON main_table
FOR EACH ROW BEGIN
    INSERT INTO `history_table` (audit_id)
        SELECT MAX(`id`)+1 FROM `audit_table`;
END;

Notice how it combines your two statements into a single statement. This may avoid letting another connection get in there and grab the same id (or something to that effect).

The following may be related (provided by OP): This happens due to a known issue in MySQL. Setting a variable from a select acquires a lock when using read uncommitted isolation level. This thread has more information.

Are you using isolation mode Read Uncommitted? If so, why?