Ahh deadlocks. Few words have the same impact as this in the software world. Doesn’t matter if you’re a DBA or developer you will get impatient. Some deadlocks take days for fixing, they are hard to reproduce, some of them reproduce only on prod machines. It’s not unusual to do blind fixes, you just don’t know what’s going on and you can only assume. You add more logs, then create a patch and throw it into production with the hope you will get more information. This happened to me lately. In this post I will try to share the my findings about how to deal with database deadlocks in mysql.
Pull a mysql docker image and start it
docker pull mysql
docker run --name local-mysql -e MYSQL_ROOT_PASSWORD=pass -d mysql:latest
Good. Let’s create a structure and cause a deadlock. First we need to connect to our mysql instance. Get the container id
docker ps -a
Connect to it
docker exec -ti 94300e36a1d0 /bin/bash
Next connect to the mysql instance.
mysql -p
Enter password: pass
We create a schema:
CREATE SCHEMA test;
USE test;
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`child_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
`parent_name` varchar(45) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_child_fk_idx` (`child_id`),
CONSTRAINT `parent_child_fk` FOREIGN KEY (`child_id`) REFERENCES `child` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`child` (`id`, `child_name`) VALUES ('1', 'child1');
INSERT INTO `test`.`child` (`id`, `child_name`) VALUES ('2', 'child2');
INSERT INTO `test`.`child` (`id`, `child_name`) VALUES ('3', 'child3');
INSERT INTO `test`.`parent` (`id`, `parent_name`, `child_id`) VALUES ('1', 'parent1', '1');
INSERT INTO `test`.`parent` (`id`, `parent_name`, `child_id`) VALUES ('2', 'parent1', '2');
INSERT INTO `test`.`parent` (`id`, `parent_name`, `child_id`) VALUES ('3', 'parent2', '3');
Now that we have the structure and some data inserted, we can begin. We need to start two database connections so that we can produce a deadlock.
In 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, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
We start session two and write
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into parent values(4,'parent2',1);
We go back to session one and write
mysql> delete from parent;
Query OK, 3 rows affected (0.00 sec)
We immediately see that there is a deadlock in session two
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Ok. Let’s take it step by step and investigate. Firstly we get the status of the innodb engine.
SHOW ENGINE INNODB STATUS;
Obviously we are interested in the deadlock part of the output.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-23 12:16:31 0x7fddcc0e7700
*** (1) TRANSACTION:
TRANSACTION 2523, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 109, OS thread handle 140590591276800, query id 374587 localhost root update
insert into parent values(4,'parent2',1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2523 lock mode S locks rec but not gap waiting
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 0000000009d5; asc ;;
2: len 7; hex 010000012101ca; asc ! ;;
3: len 1; hex 61; asc a;;
*** (2) TRANSACTION:
TRANSACTION 2522, ACTIVE 9 sec fetching rows
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3
MySQL thread id 108, OS thread handle 140590588000000, query id 374588 localhost root updating
delete from parent
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2522 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 0000000009d5; asc ;;
2: len 7; hex 010000012101ca; asc ! ;;
3: len 1; hex 61; asc a;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13 page no 4 n bits 72 index PRIMARY of table `test`.`parent` trx id 2522 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000009db; asc ;;
2: len 7; hex 81000000940110; asc ;;
3: len 1; hex 32; asc 2;;
4: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (1)
We see that transaction one is waiting for a lock to be granted. The lock in question is on primary key of the child table and it’s a shared(S) lock.
insert into parent values(4,'parent2',1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2523 lock mode S locks rec but not gap waiting
Onto the next piece of information.
delete from parent
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2522 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13 page no 4 n bits 72 index PRIMARY of table `test`.`parent` trx id 2522 lock_mode X waiting
Session two has two locks. It has a exclusive(X) lock on the primary key of the child table and it waits for a exclusive(X) lock on the primary key of parent table. So there you go. Session one waits on session two which waits on session one. Deadlock.
Cool. Looking back at our queries this makes perfect sense. Firstly we do
update child set child_name='child10' where id=1;
What this means that it will create an exclusive(X) lock on the child table record. We can verify by running
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 |
+-------------+-----------+---------------+-----------+-------------+
2 rows in set (0.00 sec)
Now when we try to insert a new record with child 1
insert into parent values(4,'parent2',1);
we are blocked by the previous exclusive(X) lock . Insert needs a shared(S) lock on the child table record due to the foreign key constraint.
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 |
| child | TABLE | IS | NULL | GRANTED |
| child | RECORD | S,REC_NOT_GAP | 1 | WAITING |
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
5 rows in set (0.00 sec)
It’s normal that when we do a
mysql> delete from parent;
to wait for an exclusive(X) lock for the parent table. But the insert it’s still waiting for a shared(S) lock. At this point session two is selected as the victim(will be rolled back), thus session one wins.
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 | supremum pseudo-record | GRANTED |
| parent | RECORD | X | 1 | GRANTED |
| parent | RECORD | X | 2 | GRANTED |
| parent | RECORD | X | 3 | GRANTED |
+-------------+-----------+---------------+------------------------+-------------+
7 rows in set (0.00 sec)
So, what we have learned from all this. Foreign keys and deadlocks do not always understand each other. An insert/update will lock the child entity through its foreign key with a shared lock(S). This means only reads are acceptable. A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. There are also some nice features starting with MySQL 8.0.1 which I will go through below.
SKIP LOCKED
mysql> insert into parent select 4,'parent2',id from child where id=1 for update skip locked;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
modifier which can be used to non-deterministically read rows from a table while skipping over the rows which are locked. We see that we have no waiting locks and the parent does not lock with an exclusive(X) lock. This is session two.
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,GAP | 2 | GRANTED |
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
4 rows in set (0.00 sec)
After we do the delete in session one we get
mysql> delete from parent; Query OK, 3 rows affected (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,GAP | 2 | GRANTED |
| child | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X | supremum pseudo-record | GRANTED |
| parent | RECORD | X | 1 | GRANTED |
| parent | RECORD | X | 2 | GRANTED |
| parent | RECORD | X | 3 | GRANTED |
+-------------+-----------+---------------+------------------------+-------------+
9 rows in set (0.00 sec)
Pretty neat.
Also if we don’t want to wait the whole 50 seconds (innodb_lock_wait_timeout) for a lock to be released we can now use NOWAIT modifier which will throw an error immediately. This is in session two.
mysql> insert into parent select 4,'parent2',id from child where id=1 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
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 | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
+-------------+-----------+---------------+-----------+-------------+
3 rows in set (0.00 sec)
After the delete in session one we get
mysql> delete from parent; Query OK, 3 rows affected (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 | TABLE | IX | NULL | GRANTED |
| child | RECORD | X,REC_NOT_GAP | 1 | GRANTED |
| parent | TABLE | IX | NULL | GRANTED |
| parent | RECORD | X | supremum pseudo-record | GRANTED |
| parent | RECORD | X | 1 | GRANTED |
| parent | RECORD | X | 2 | GRANTED |
| parent | RECORD | X | 3 | GRANTED |
+-------------+-----------+---------------+------------------------+-------------+
8 rows in set (0.00 sec)
No more deadlocks!!!
For more tips&tricks on how to handle and minimise deadlock go here.
Learn. Write. Repeat. 
1 thought on “Deadlocks 101 – MySQL”