[MDEV-9139] SELECT inside a trigger results in InnoDB gap lock Created: 2015-11-17  Updated: 2018-07-16  Resolved: 2018-07-16

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Triggers
Affects Version/s: 10.0.19
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: innodb


 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

https://mariadb.com/kb/en/mariadb/xtradbinnodb-lock-modes/

And I don't see this particular locking behavior documented here:

https://mariadb.com/kb/en/mariadb/triggers-and-implicit-locks/

First, let's create some tables:

CREATE OR REPLACE TABLE test (
 
    id int(3) auto_increment primary key,
 
    name varchar(10)
 
) ENGINE=InnoDB;
 
 
 
CREATE OR REPLACE 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 9090, ACTIVE 1 sec
 
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
 
MySQL thread id 8, OS thread handle 0x7f287b6c8700, query id 131 localhost root init
 
show engine innodb status
 
Trx #rec lock waits 0 #table lock waits 0
 
Trx total rec lock wait time 0 SEC
 
Trx total table lock wait time 0 SEC
 
TABLE LOCK table `db1`.`test` trx id 9090 lock mode IX lock hold time 1 wait time before grant 0
 
RECORD LOCKS space id 26 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test` trx table locks 2 total table locks 1  trx id 9090 lock_mode X locks rec but not gap lock hold time 1 wait time before grant 0
 
TABLE LOCK table `db1`.`test_log` trx id 9090 lock mode IX lock hold time 1 wait time before grant 0
 
RECORD LOCKS space id 27 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 2 total table locks 1  trx id 9090 lock_mode X lock hold time 1 wait time before grant 0
 
RECORD LOCKS space id 27 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 2 total table locks 1  trx id 9090 lock_mode X locks gap before rec lock hold time 1 wait time before grant 0

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 9092, ACTIVE 5 sec
 
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
 
MySQL thread id 8, OS thread handle 0x7f287b6c8700, query id 136 localhost root init
 
show engine innodb status
 
Trx read view will not see trx with id >= 9093, sees < 9093
 
Trx #rec lock waits 0 #table lock waits 0
 
Trx total rec lock wait time 0 SEC
 
Trx total table lock wait time 0 SEC
 
TABLE LOCK table `db1`.`test_log` trx id 9092 lock mode IX lock hold time 3 wait time before grant 0

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');
 

---TRANSACTION 9097, ACTIVE 4 sec
 
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
 
MySQL thread id 8, OS thread handle 0x7f287b6c8700, query id 141 localhost root init
 
show engine innodb status
 
Trx #rec lock waits 0 #table lock waits 0
 
Trx total rec lock wait time 0 SEC
 
Trx total table lock wait time 0 SEC
 
TABLE LOCK table `db1`.`test_log` trx id 9097 lock mode IX lock hold time 4 wait time before grant 0
 
RECORD LOCKS space id 27 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 1 total table locks 1  trx id 9097 lock_mode X lock hold time 4 wait time before grant 0
 
RECORD LOCKS space id 27 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 1 total table locks 1  trx id 9097 lock_mode X locks gap before rec lock hold time 3 wait time before grant 0

Do triggers force SELECT statements to lock like SELECT FOR UPDATE?

Is this locking behavior documented somewhere?

 Comments   
Comment by Geoff Montee (Inactive) [ 2015-11-25 ]

Does this trigger require an X gap lock on test_log because a field in test_log is being directly compared to the row in test being updated (i.e. WHERE id = OLD.id)?

For example, if the trigger looks like this:

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;
 
 
 
END;
 
//
 
 
 
DELIMITER ;

Then it still requires an X gap lock:

---TRANSACTION 17759, ACTIVE 3 sec
 
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
 
MySQL thread id 6, OS thread handle 0x7f3aa7eff700, query id 105 localhost root cleaning up
 
Trx #rec lock waits 0 #table lock waits 0
 
Trx total rec lock wait time 0 SEC
 
Trx total table lock wait time 0 SEC
 
TABLE LOCK table `db1`.`test` trx id 17759 lock mode IX lock hold time 3 wait time before grant 0
 
RECORD LOCKS space id 36 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test` trx table locks 2 total table locks 1  trx id 17759 lock_mode X locks rec but not gap lock hold time 3 wait time before grant 0
 
TABLE LOCK table `db1`.`test_log` trx id 17759 lock mode IX lock hold time 3 wait time before grant 0
 
RECORD LOCKS space id 37 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 2 total table locks 1  trx id 17759 lock_mode X lock hold time 3 wait time before grant 0
 
RECORD LOCKS space id 37 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 2 total table locks 1  trx id 17759 lock_mode X locks gap before rec lock hold time 3 wait time before grant 0

But if the trigger looks like this:

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 EXISTS(SELECT OLD.id)) THEN
 
signal SQLSTATE VALUE '99999';
 
END IF;
 
 
 
END;
 
//
 
 
 
DELIMITER ;

Then only an S lock is taken on test_log:

---TRANSACTION 17763, ACTIVE 4 sec
 
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
 
MySQL thread id 6, OS thread handle 0x7f3aa7eff700, query id 117 localhost root cleaning up
 
Trx #rec lock waits 0 #table lock waits 0
 
Trx total rec lock wait time 0 SEC
 
Trx total table lock wait time 0 SEC
 
TABLE LOCK table `db1`.`test` trx id 17763 lock mode IX lock hold time 4 wait time before grant 0
 
RECORD LOCKS space id 36 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test` trx table locks 2 total table locks 1  trx id 17763 lock_mode X locks rec but not gap lock hold time 4 wait time before grant 0
 
TABLE LOCK table `db1`.`test_log` trx id 17763 lock mode IS lock hold time 4 wait time before grant 0
 
RECORD LOCKS space id 37 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test_log` trx table locks 2 total table locks 1  trx id 17763 lock mode S lock hold time 4 wait time before grant 0

Comment by Jan Lindström (Inactive) [ 2015-11-26 ]

Remember that this trigger is BEFORE UPDATE, thus assumption is we make some changes. X lock or gap lock will make sure that result set of that select inside a trigger does not change, now why X-lock, I think this comes from the fact that this is UPDATE trigger. In last example select is based on constant (from InnoDB point of view).

Comment by Jan Lindström (Inactive) [ 2018-07-16 ]

greenman Can you check the documentation and add a note that UPDATE triggers could acquire gap-locks.

Generated at Thu Feb 08 07:32:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.