Bug #79390 SELECT + INSERT inside a trigger result in InnoDB gap lock
Submitted: 23 Nov 2015 23:47 Modified: 23 Jan 2019 13:12
Reporter: Geoff Montee Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.27 OS:Any
Assigned to: CPU Architecture:Any

[23 Nov 2015 23:47] Geoff Montee
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.
[22 Jan 2019 13:26] MySQL Verification Team
Hi,

I think that this is expected behaviour.

Your trigger is BEFORE UPDATE. Hence, there is a gap lock from SELECT, since it is locking a row that is about to be updated. This is a very large difference from SELECT that is used irrelevant of the UPDATE.

If this is not properly documented, let us know and we shall correct it.
[22 Jan 2019 22:06] Geoff Montee
Hi Sinisa,

Thanks for the feedback. This does seem to be undocumented. InnoDB locking behavior for statements inside triggers is not described on any of the following documentation pages:

https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

https://dev.mysql.com/doc/refman/5.6/en/triggers.html

https://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

Thanks!
[23 Jan 2019 13:12] MySQL Verification Team
Hi Geoff,

I agree with you entirely.

Verified as a documentation request.