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