Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.19
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?