Description:
When updating a table that has triggers that insert rows into another table, operations that would not wait on locks without the triggers in place cause lock waits.
How to repeat:
create table a ( bone INT PRIMARY KEY, btwo INT ) ENGINE=InnoDB;
create table b ( bone INT AUTO_INCREMENT PRIMARY KEY, btwo INT ) ENGINE=InnoDB;
DELIMITER |
CREATE TRIGGER a AFTER INSERT ON a
FOR EACH ROW BEGIN
INSERT INTO b (btwo) VALUES(1);
END;
|
CREATE TRIGGER a2 AFTER UPDATE ON a
FOR EACH ROW BEGIN
INSERT INTO b (btwo) VALUES(1);
END;
|
DELIMITER ;
insert into a VALUES(1, 5);
------------------------------
>> Connection One
begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set btwo=4 where bone=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
>> Connection Two
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where bone=1 LOCK IN SHARE MODE;
(This pauses, waiting for the lock)
>> Connection One
mysql> insert into a values(3, 7);
This pauses, waiting on a lock. (Without the triggers this would return immediately). and eventually outputs.
Query OK, 1 row affected (45.50 sec)
and at the same time Connection Two outputs
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
