Bug #71060 INSERT-SELECT statement takes shared locks in trigger and causes deadlock
Submitted: 3 Dec 2013 14:08 Modified: 3 Dec 2013 15:36
Reporter: Sadao Hiratsuka Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.6.15, 5.6.16, 5.5.35, 5.1.74 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, trigger

[3 Dec 2013 14:08] Sadao Hiratsuka
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.
[3 Dec 2013 15:36] Sveta Smirnova
Thank you for the report.

Verified as described: if replace trigger call with INSERT INTO t1_hist (t1_id, updated_at) SELECT id, updated_at FROM t1 WHERE id = 1 in second transaction, query will fail with "Lock wait timeout exceeded; try restarting transaction", deadlock won't happen