MySQL – Stored Procedure Triggered on Insert Causes Deadlock

deadlockMySQLmysql-5.7stored-procedures

I'm trying to debug a deadlock that happens when a stored procedure is triggered inside a transaction. Table and trigger definitions are as follows:

CREATE TABLE a (
  id int(11) NOT NULL AUTO INCREMENT,
  b varchar(10) NOT NULL,
  d date NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; //

CREATE PROCEDURE CheckNoDuplicates (new_id int(11), new_b varchar(10), new_d date)
BEGIN
  DECLARE existingId INT(11);

  SELECT id INTO existingId
  FROM a
  WHERE new_b = b
      AND d IS NOT NULL AND new_d IS NOT NULL AND YEAR(d) = YEAR(new_d)
        OR d IS NULL AND new_d IS NULL
       AND id <> new_id;

  IF existingId IS NOT NULL THEN
    SIGNAL SQLSTATE '23000';
  END IF;
END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE INSERT ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE UPDATE ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

When two inserts happen at the same time inside a transaction, sometimes they succeed, sometimes they give me back a deadlock (Deadlock found when trying to get lock; try restarting transaction).

If I get rid of the stored procedure, I get no deadlocks anymore.

How are stored procedure executed inside a transaction?
Is there a better way to keep the constraint I'm trying to get (uniqueness on b and the YEAR of date d)?

Best Answer

Is there a better way to keep the constraint I'm trying to get (uniqueness on b and the YEAR of date d)?

Create virtual generated column which calculates value You'd like to be unique, and create unique secondary index over it.

For more information read MySQL 5.7 Reference Manual / Secondary Indexes and Generated Columns

Related Question