Categories
Development History MySQL PHP SQL

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *