0

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)?

  • missing an open paren?? – Rick James Jun 5 '18 at 17:00
  • The missin parenthesis was just a typo - I've edited my question to remove the extra paren. The point was about the deadlock though. – Pamela Gotti Jun 20 '18 at 17:31
0

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

0
INDEX(b)

should help a lot. This might be even better:

INDEX(b, d, id)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.