MySql Deadlock — INSERT… ON DUPLICATE KEY UPDATE

Both transactions are waiting for a resource to become available, neither ever release the locks it holds

Bruno

First, let me explain why deadlocks happen, and then I will give you some tips how to avoid them.


Locking is crucial to avoid two users or services modifying data at the same time, you may think that’s unlikely, but depending on the application, there is a significant risk of that happen.

Mysql perform a lock on every update or delete and generally locks the set of records scanned in the index while processing the SQL statement. InnoDB does not care about the where clause only the index range scanned.

Normally next-key lock is made, which is: an index record lock with a gap lock before the index record. So, if we have a table with following structure and data:

Client_Days table
+-----------+------------+---------|
| Client_Id | Day | Value_A |
+-----------+------------+---------+
| ABC10 | 2018-05-01 | 3 |
| 05BCA | 2018-07-23 | 4 |
+-----------+------------+---------+
UNIQUE KEY Client_Id_Day (Client_id, Day)

A first connection is made to process the following statement:

INSERT INTO 
Client_Days (Client_Id, Day, Value_A)
('ABC10', '2018-08-01', 5),
('ABC10', '2018-06-01', 7)
ON DUPLICATE KEY UPDATE
Value_A = VALUES(Value_A);

MySql will lock the gap between 2018–07–23 and 2018–08–01, and then lock the gap between 2018–05–01 and 2018–06–01.


If at the same time a second connection occurs to execute the statement:

INSERT INTO 
Client_Days (Client_Id, Day, Value_A)
('ABC10', '2018-07-11', 6)
('ABC10', '2018-07-25', 6)
ON DUPLICATE KEY UPDATE
Value_A = 6;

We will have a deadlock, the first connection is waiting for the unlock between 2018–05–01 and 2018–07–11 to insert the value in 2018–06–01.

The second connection is waiting for unlock to be made by the first connection in the gap between 2018–07–23 and 2018–08–01 to insert the value in 2018–07–25.

So, they are both waiting for each other.


Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

If the deadlocks are so frequent, here you have some tips:

  • If possible, use a message broker queue system fifo, behind the database. In the example below a queue per client id would solve the deadlocks, the transactions would be sequential executed.
  • Implement a retry mechanism, which should solve the issue.
  • Transactions should be simple and fast to avoid collisions.
  • Wisely create indexes in your tables in order for queries scan less records consequently set fewer locks.

If you enjoyed this article please recommend and share.

Bruno

Written by

Bruno

Wannabe beer geek. Programmer. Software Engineer. Looking for new ways to solve old problems.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade