We are in 2019. Who uses triggers you may wonder? That’s a valid question. But if you plan to do a database migration you may find them handy. Let’s say that part of the migration you create new separate schema which you plan to use it for the new version. Or you could break one table in multiple tables, in order to separate domain concepts. One case for this would be refactoring your monolith to microservices and part of that is this database migration. So, we’re not going with a bing bang approach but with incremental changes. We want to keep the monolith working but in the same time prepare the new services so that we can do a canary release at some point. Of course in order to do this we need to synchronise the data. And in the same time we don’t want to work on the monolith to add more code which would support this migration, so the obvious choice would be to use database triggers. So now that we found a valid case for using triggers, let’s see what we need to be careful when using them.
Please check this article in order to prepare your setup.
We’re gonna add a column to the parent table.
mysql > alter table parent add column child_name varchar(255);
The purpose of this column is to avoid a join between parent and child in situations where the join takes too much time to get the result. Cool. Now every time we update the name of a child we need also to update this column in the parent table. We create the following trigger on child table.
CREATE DEFINER=`root`@`%` TRIGGER `child_AFTER_UPDATE` AFTER UPDATE ON `child` FOR EACH ROW BEGIN
update parent
set child_name = new.child_name
where child_id = new.id;
END
This is our database content.
mysql> select * from parent;
+----+-------------+----------+------------+
| id | parent_name | child_id | child_name |
+----+-------------+----------+------------+
| 1 | parent1 | 1 | NULL |
| 2 | parent1 | 2 | NULL |
| 3 | parent2 | 3 | NULL |
+----+-------------+----------+------------+
3 rows in set (0.00 sec)
mysql> select * from child;
+----+------------+
| id | child_name |
+----+------------+
| 1 | child1 |
| 2 | child2 |
| 3 | child3 |
+----+------------+
3 rows in set (0.00 sec)
As usual let’s start two sessions.
Session one
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update child set child_name='child10' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+---------------+-----------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+-----------+-------------+
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | RECORD | X,GAP | 2, 2 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
6 rows in set (0.00 sec)
Session two
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update parent set parent_name='parent2' where id=1;
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+---------------+-----------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+-----------+-------------+
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | WAITING |
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | RECORD | X,GAP | 2, 2 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
8 rows in set (0.00 sec)
OK. What do we know after this statements. Session one locks two rows in the parent table, one of which is a gap lock. So session two obvious has to wait.
Going back to session one we write
mysql> select * from parent where id<>1 for share;
+----+-------------+----------+------------+
| id | parent_name | child_id | child_name |
+----+-------------+----------+------------+
| 2 | parent1 | 2 | NULL |
| 3 | parent2 | 3 | NULL |
+----+-------------+----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+---------------+------------------------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+------------------------+-------------+
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | RECORD | X,GAP | 2, 2 | GRANTED |
| parent | RECORD | S | supremum pseudo-record | GRANTED |
| parent | RECORD | S | 3 | GRANTED |
| parent | RECORD | S | 2 | GRANTED |
| parent | RECORD | S | 1 | GRANTED |
+-------------+-----------+---------------+------------------------+-------------+
10 rows in set (0.01 sec)
We want to read the records that are not touched by the second session. Well, surprise. Deadlock.
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
What happened? If you want to investigate step by step you can do that by checking this. The problem that you won’t find the culprit in the logs. We saw that this query
mysql> update child set child_name='child10' where id=1;
locks also some rows in the parent table.
+-------------+-----------+---------------+-----------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+-----------+-------------+
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | RECORD | X,GAP | 2, 2 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
This is due to the trigger. But why is it locking two rows. What does a gap lock mean? A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. When we run the same query twice, we get the same result, regardless other session modifications on that table. This makes reads consistent and therefore makes the replication between servers consistent. If we look at the trigger in the where section
update parent
set child_name = new.child_name
where child_id = new.id;
for our query (child_id=1) will return the row with parent name “parent1” in the parent table. Now there is another row with the parent name “parent1”. The second row is locked with a exclusive gap lock.
Now that we know this, it’s normal that when we do in session two a
mysql> select * from parent where id<>1 for share;
to deadlock in the first session. Why is the gap lock being acquired? The answer is the isolation level. By default mysql has a REPETABLE-READ isolation level. If we want to remove the unnecessary locks we need to decrease it. Let’s go to READ_COMMITED.
Session one
mysql> set @@SESSION.transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update child set child_name='child10' where id=1; Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+---------------+-----------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+-----------+-------------+
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
5 rows in set (0.00 sec)
We already notice that the gap lock is missing.
Session two
mysql> set @@SESSION.transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update parent set parent_name='parent2' where id=1;
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+---------------+-----------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+-----------+-------------+
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | WAITING |
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
7 rows in set (0.00 sec)
Session one
mysql> select * from parent where id<>1 for share;
+----+-------------+----------+------------+
| id | parent_name | child_id | child_name |
+----+-------------+----------+------------+
| 2 | parent1 | 2 | NULL |
| 3 | parent2 | 3 | NULL |
+----+-------------+----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+---------------+-----------+-------------+
| object_name | lock_type | lock_mode | lock_data | lock_status |
+-------------+-----------+---------------+-----------+-------------+
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | WAITING |
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1, 1 | GRANTED |
| parent | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | RECORD | S,REC_NOT_GAP | 3 | GRANTED |
| parent | RECORD | S,REC_NOT_GAP | 2 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
9 rows in set (0.00 sec)
Now we see that this statement does not lead to deadlock, but the second session will eventually time out because session one was not committed or rolled back.
mysql> update parent set parent_name='parent2' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
This would happen only in the case the first session will take more than 50 seconds. One solution would be to increase this timeout(not recommended) or to implement a retry mechanism. This is out of the scope of this article. Be careful when you implement triggers, they can bite you and as you saw the logs can be misleading. Remember that the trigger is part of the same transaction as the action that triggers it.
Learn. Write. Repeat. 