Mysql deadlock caused by INSERT… ON DUPLICATE KEY UPDATE

2019.08.02

What is a deadlock and what conditions cause deadlock?

A deadlock is a state in which each member of a group is waiting for another member.
An example of deadlock is the dining philosophers problem. Each philosopher will pick up the left fork and wait for the right fork to become available, but it never does.

This week, I encountered mysql deadlock when running multiple “INSERT… ON DUPLICATE KEY UPDATE” queries on the database. With a single connection, this insertion works perfectly. However, with multiple connections, it creates a deadlock issue. Below is a summary of what happened.

DB Schema

CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(10) unsigned DEFAULT NULL,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `num_index` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=utf8;

Existing data

+----+------+-------+
| id | num  | name  |
+----+------+-------+
|  1 |   28 | Alice |
|  2 |   35 | Bob   |
|  3 |   40 | Jerry |
|  4 |   20 | Tom   |
|  5 |   51 | Mary  |
+----+------+-------+

Mysql version and isolation level

Mysql version is 5.7.25, and isolation level is repeatable-read.

The db queries that cause deadlock

First connection: 
INSERT INTO test_table (id, num, name) VALUES (6, 36, "John") ON DUPLICATE KEY UPDATE name="John";

Second connection:
INSERT INTO test_table (id, num, name) VALUES (7, 37, "Mia") ON DUPLICATE KEY UPDATE name="Mia";

First connection:
INSERT INTO test_table (id, num, name) VALUES (8, 39, "Emilie") ON DUPLICATE KEY UPDATE name="Emilie";

What happened?

INSERT INTO test_table (id, num, name) VALUES (6, 36, "John")
ON DUPLICATE KEY UPDATE name="John";

When we run the first query, mysql will acquire a gap lock between 35 and 36 since we have unique key num.

INSERT INTO test_table (id, num, name) VALUES (7, 37, "Mia")
ON DUPLICATE KEY UPDATE name="Mia";

Now a second connection runs the above query. It acquires a gap lock between 36 and 37, and an insert intention lock, waiting for the first connection to be unlocked.

INSERT INTO test_table (id, num, name) VALUES (8, 39, "Emilie")
ON DUPLICATE KEY UPDATE name="Emilie";

Now the first connection runs another query. It will try to acquire an insert intention lock, however this conflicts with the existing gap lock from the second connection (which is waiting). It causes deadlock and error occurs.

Solution

The solution is to use INSERT instead of INSERT ON DUPLICATE KEY UPDATE. INSERT would add record x locks in unique key and primary key, instead of acquiring a gap lock, so it wouldn't create a deadlock.

The lesson to learn is that if we want to use INSERT… ON DUPLICATE KEY UPDATE with multiple connections, we should pay close attention to the unique key in db schema.

Mysql logs

Here are the detailed mysql logs for more information.

mysql> set GLOBAL innodb_status_output=ON;
mysql> show engine innodb status;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-08-02 13:58:03 0x7f89cc1be700
*** (1) TRANSACTION:
TRANSACTION 479001, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140229811361536, query id 40 localhost root update
INSERT INTO test_table (id, num, name) VALUES (7, 37, "Mia") ON DUPLICATE KEY UPDATE name="Mia"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22410 page no 4 n bits 80 index num_index of table `test`.`test_table` trx id 479001 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000028; asc    (;;
 1: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 478996, ACTIVE 133 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 140229811627776, query id 41 localhost root update
INSERT INTO test_table (id, num, name) VALUES (8, 39, "Emilie") ON DUPLICATE KEY UPDATE name="Emilie"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 22410 page no 4 n bits 72 index num_index of table `test`.`test_table` trx id 478996 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000028; asc    (;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000024; asc    $;;
 1: len 4; hex 80000006; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22410 page no 4 n bits 80 index num_index of table `test`.`test_table` trx id 478996 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000028; asc    (;;
 1: len 4; hex 80000003; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

Credits

To write this blog, I refer to a few blogs to understand the issues.

Much thanks to these writers 👏