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