Description:
In trigger, INSERT INTO T SELECT ... FROM S statement takes shared locks on S
even if the transaction isolation level is READ COMMITTED.
How to repeat:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t1_hist;
DROP TABLE IF EXISTS t1_trig;
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, updated_at DATETIME);
CREATE TABLE t1_hist (id INT PRIMARY KEY AUTO_INCREMENT, t1_id INT, updated_at DATETIME, KEY (t1_id));
CREATE TABLE t1_trig (id INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 (updated_at) VALUES (NOW());
INSERT INTO t1_hist (t1_id, updated_at) SELECT id, updated_at FROM t1 WHERE id = 1;
DROP TRIGGER IF EXISTS t1_trig_ins;
DELIMITER //
CREATE TRIGGER t1_trig_ins AFTER INSERT ON t1_trig FOR EACH ROW
BEGIN
INSERT INTO t1_hist (t1_id, updated_at) SELECT id, updated_at FROM t1 WHERE id = 1;
END
//
DELIMITER ;
-- reproduce -----------------------------------------------
-- session A
SET SESSION tx_isolation = 'READ-COMMITTED';
BEGIN;
UPDATE t1 SET updated_at = NOW() WHERE id = 1;
-- session B
SET SESSION tx_isolation = 'READ-COMMITTED';
BEGIN;
UPDATE t1_hist SET updated_at = NOW() WHERE t1_id = 1;
INSERT INTO t1_trig VALUES (DEFAULT); -- blocked
-- session A
UPDATE t1_hist SET updated_at = NOW() WHERE t1_id = 1; -- deadlock
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-- not reproduce if trigger is not used --------------------
-- session A
SET SESSION tx_isolation = 'READ-COMMITTED';
BEGIN;
UPDATE t1 SET updated_at = NOW() WHERE id = 1;
-- session B
SET SESSION tx_isolation = 'READ-COMMITTED';
BEGIN;
UPDATE t1_hist SET updated_at = NOW() WHERE t1_id = 1;
INSERT INTO t1_hist (t1_id, updated_at) SELECT id, updated_at FROM t1 WHERE id = 1; -- pass
-- session A
UPDATE t1_hist SET updated_at = NOW() WHERE t1_id = 1; -- blocked until B is committed
-- innodb status -------------------------------------------
------------------------
LATEST DETECTED DEADLOCK
------------------------
2013-12-03 22:43:00 7fe4045a7700
*** (1) TRANSACTION:
TRANSACTION 1337132, ACTIVE 4 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 5, OS thread handle 0x7fe48006a700, query id 84 localhost root statistics
INSERT INTO t1_hist (t1_id, updated_at) SELECT id, updated_at FROM t1 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 490 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 1337132 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000014672b; asc g+;;
2: len 7; hex 2b0000401d3087; asc + @ 0 ;;
3: len 5; hex 9991476ab4; asc Gj ;;
*** (2) TRANSACTION:
TRANSACTION 1337131, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 0x7fe4045a7700, query id 85 localhost root updating
UPDATE t1_hist SET updated_at = NOW() WHERE t1_id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 490 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 1337131 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000014672b; asc g+;;
2: len 7; hex 2b0000401d3087; asc + @ 0 ;;
3: len 5; hex 9991476ab4; asc Gj ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 491 page no 4 n bits 72 index `t1_id` of table `test`.`t1_hist` trx id 1337131 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (2)
Suggested fix:
If the transaction isolation level is READ COMMITTED,
this statement should not take shared locks and should do a consistent read.