Description:
Executing a SELECT and an INSERT inside a trigger seems to require a gap lock, while this gap lock is not needed if the two statements are executed in a transaction outside of the trigger. There might be a good reason for this, but it seems to be undocumented.
For example, "trigger" doesn't appear at all on these pages:
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html
How to repeat:
First, let's create some tables:
CREATE TABLE test (
id int(3) auto_increment primary key,
name varchar(10)
) ENGINE=InnoDB;
CREATE TABLE test_log (
id int(3) auto_increment primary key,
name varchar(10)
) ENGINE=InnoDB;
INSERT INTO test (id, name) VALUES (1, 'test1-1');
INSERT INTO test (id, name) VALUES (2, 'test2-1');
Then let's say that we have the following trigger on test:
DROP TRIGGER IF EXISTS test_trg;
DELIMITER //
CREATE TRIGGER test_trg
BEFORE UPDATE
ON test
FOR EACH ROW
BEGIN
IF EXISTS(SELECT 1 FROM test_log WHERE id = OLD.id) THEN
signal SQLSTATE VALUE '99999';
END IF;
INSERT INTO test_log (id, name) VALUES (OLD.id, OLD.name);
END;
//
DELIMITER ;
And then we execute this:
BEGIN;
UPDATE test SET name = 'test1-2' WHERE id = 1;
SHOW ENGINE INNODB STATUS shows that this transaction has a gap lock on the test_log table:
---TRANSACTION 20258, ACTIVE 27 sec
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 0x7f901b6eb700, query id 64 localhost root cleaning up
TABLE LOCK table `db1`.`test` trx id 20258 lock mode IX
RECORD LOCKS space id 17 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test` trx id 20258 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 000000004f22; asc O";;
2: len 7; hex 1b00000155031e; asc U ;;
3: len 7; hex 74657374312d32; asc test1-2;;
TABLE LOCK table `db1`.`test_log` trx id 20258 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx id 20258 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 18 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx id 20258 lock_mode X locks gap before rec
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 000000004f22; asc O";;
2: len 7; hex 9b0000016d0110; asc m ;;
3: len 7; hex 74657374312d31; asc test1-1;;
This locking behavior does not appear to be equivalent to the locking behavior of the statements that the transactions run.
For example, if we ran the following:
BEGIN;
SELECT 1 FROM test_log WHERE id = 1;
INSERT INTO test_log (id, name) VALUES (1, 'test1-1');
Then SHOW ENGINE INNODB STATUS shows the following:
---TRANSACTION 20264, ACTIVE 6 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x7f901b6eb700, query id 71 localhost root cleaning up
Trx read view will not see trx with id >= 20265, sees < 20265
TABLE LOCK table `db1`.`test_log` trx id 20264 lock mode IX
To get the same locking behavior as the trigger, you have to use a SELECT FOR UPDATE:
BEGIN;
SELECT 1 FROM test_log WHERE id = 1 FOR UPDATE;
INSERT INTO test_log (id, name) VALUES (1, 'test1-1');
Then SHOW ENGINE INNODB STATUS shows the following:
---TRANSACTION 20265, ACTIVE 6 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x7f901b6eb700, query id 76 localhost root cleaning up
TABLE LOCK table `db1`.`test_log` trx id 20265 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx id 20265 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 18 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx id 20265 lock_mode X locks gap before rec
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 000000004f29; asc O);;
2: len 7; hex 9f0000016e0110; asc n ;;
3: len 7; hex 74657374312d31; asc test1-1;;
Suggested fix:
Document this behavior.
