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 in table
| 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”;
So 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 35 and 37, as well as 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 locks with unique key and primary key, instead of gap locks, 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.
Credits
To write this blog, I refer to a few blogs to understand the issues. Much thanks to these writers 👏.
https://zhuanlan.zhihu.com/p/29349080
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-intention-locks