Analyzing MySQL 8.0 INSERT … ON DUPLICATE KEY UPDATE Deadlock and Source‑Code Investigation

1 Problem Background

In a business scenario where an INSERT INTO ... ON DUPLICATE KEY UPDATE statement is used to insert or update data, a batch cancellation of logistics orders was implemented with multiple threads, each thread handling one logistics cancellation. During testing, cancelling two logistics simultaneously caused a MySQL deadlock.

2 Problem Reproduction

2.1 Using SHOW ENGINE INNODB STATUS to Find the Deadlock Statement

The deadlock log is obtained with the SHOW ENGINE INNODB STATUS command (MySQL version 5.7). The log shows the two conflicting transactions and the lock information.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-12-05 21:18:45 0x7fecb4759700
*** (1) TRANSACTION:
TRANSACTION 1366772472, ACTIVE 1 sec inserting
... (log details omitted for brevity) ...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6314 page no 4758 n bits 192 index PRIMARY of table `dbzz_hunter_partner`.`recycle_order_extend` trx id 1366772472 lock_mode X locks gap before rec insert intention waiting
...

The log makes it easy to locate the SQL statements that caused the deadlock.

2.2 Reproducing the Issue

After importing the test data into a local MySQL 8.0 instance, the following steps are executed to reproduce the problem.

Step

Transaction A

Transaction B

1

BEGIN;

BEGIN;

2

INSERT INTO recycle_order_extend (id, recycle_order_id, param_id, value_ids, es_type) VALUES (376510847366725657, 376473601396703447, 100030, '[]', 1), (376510847369871385, 376473601396703447, 100031, '[]', 1) ON DUPLICATE KEY UPDATE value_ids=values(

value_ids

), update_time=NOW();

-

3

-

INSERT INTO recycle_order_extend (id, recycle_order_id, param_id, value_ids, es_type) VALUES (376510847366725657, 376473627618443479, 100030, '[]', 1), (376510847369871385, 376473627618443479, 100031, '[]', 1) ON DUPLICATE KEY UPDATE value_ids=values(

value_ids

), update_time=NOW();

4

INSERT INTO recycle_order_extend (id, recycle_order_id, param_id, value_ids, es_type) VALUES (376510847508283417, 376473608578400471, 100030, '[]', 1), (376510847509331993, 376473608578400471, 100031, '[]', 1) ON DUPLICATE KEY UPDATE value_ids=values(

value_ids

), update_time=NOW();

-

The phenomenon is that Transaction A blocks Transaction B, but no deadlock occurs in this simple test.

Running SELECT engine_transaction_id, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks; shows that Transaction B is waiting for a gap lock held by Transaction A.

3 Problem Thinking

Although the simple test only shows blocking, a more realistic multi‑threaded test reproduces a deadlock where each thread executes only one INSERT statement before the deadlock occurs. The lock involved is a record lock on the PRIMARY index.

4 MySQL 8.0 Source Code

4.1 Preparation

1. MySQL 8.0 source code and CLion IDE. 2. Configure the toolchain (example CMake flags shown). 3. Build the project with CMake. 4. Initialise MySQL with --initialize-insecure . 5. Start MySQL using a custom --defaults-file .

4.2 Source‑Code Reading

The following simplified call chain was discovered by debugging:

-sql_insert.cc#Sql_cmd_insert_values::execute_inner → iterate over VALUES
  -handler.cc#ha_write_row → operate on a single row
    -ha_innodb.cc#ha_innobase::write_row → store a row in InnoDB
      -row0mysql.cc#row_insert_for_mysql → perform INSERT
        -row0ins.cc#row_ins_index_entry_step → insert each index separately
          -row0ins.cc#row_ins_clust_index_entry → insert clustered index
          -row0ins.cc#row_ins_sec_index_multi_value_entry → insert secondary indexes
      -row0mysql.cc#row_mysql_handle_errors → handle duplicate‑key case
        -trx0roll.cc#trx_rollback_to_savepoint → rollback to savepoint
          -que0que.cc#que_run_threads_low → schedule a thread (undo thread) to execute rollback, which adds gap locks to prevent phantom reads
    -handler.cc#handler::ha_index_read_idx_map → lock existing primary‑key record
    -handler.cc#ha_update_row → update a row
      -row0upd.cc#row_upd → modify clustered and secondary indexes

A more detailed version of the call chain includes functions such as sql_parse.cc#mysql_execute_command , sql_select.cc#Sql_cmd_dml::execute , and many InnoDB internal routines ( btr0cur.cc#btr_cur_optimistic_insert , lock0lock.cc#lock_rec_insert_check_and_lock , etc.).

4.3 Debugging the Locking Process

The following INSERT statement is used for debugging:

INSERT INTO recycle_order_extend (id, recycle_order_id, param_id, value_ids, es_type)
VALUES
  (376510847366725657, 376473601396703447, 100030, '[]', 1),
  (376510847369871385, 376473601396703447, 100031, '[]', 1)
ON DUPLICATE KEY UPDATE value_ids=VALUES(`value_ids`), update_time=NOW();

During execution the following lock types are observed (simplified):

TABLE | IX | NULL | null
RECORD | X | uniq_recycle_param | 376473601396703447, 100030, 376501984043204793
RECORD | X,GAP | PRIMARY | 2783565478700000000
... (subsequent lock steps omitted for brevity) ...

The key observations are:

The engine iterates over each VALUES row and acquires locks per row.

Gap locks are added when handling duplicate‑key errors ( row0mysql.cc#row_mysql_handle_errors ).

Locks are taken in small batches, not for the whole statement at once.

Mini‑transactions protect page‑level structures with RW‑X‑LATCH locks, ensuring that lock acquisition on the same record is not concurrent.

5 Problem Verification

By inserting a 1‑second sleep in row0mysql.cc#row_mysql_handle_errors , both Transaction A and Transaction B can acquire the same gap lock, reproducing the deadlock.

5.1 Reproducing a Deadlock with Two Statements in Transaction A and One in Transaction B

Step 1 (Tx A): INSERT ... VALUES (row1), (row2) ON DUPLICATE KEY UPDATE ...; // stop at breakpoint
Step 2 (Tx B): INSERT ... VALUES (row3), (row4) ON DUPLICATE KEY UPDATE ...; // runs while Tx A is paused
Step 3 (Tx A): INSERT ... VALUES (row5), (row6) ON DUPLICATE KEY UPDATE ...; // resumes and deadlock occurs

After Step 3 a deadlock is observed because both transactions hold gap locks on the same index.

5.2 Reproducing a Deadlock with One Statement in Each Transaction

Step 1 (Tx A): INSERT ... VALUES (row1), (row2) ON DUPLICATE KEY UPDATE ...; // breakpoint
Step 2 (Tx B): INSERT ... VALUES (row3), (row4) ON DUPLICATE KEY UPDATE ...; // runs while Tx A is paused

When Tx B executes, a deadlock occurs for the same reason – both transactions have obtained the conflicting gap lock.

6 References

MAC compilation and debugging of MySQL 8.0 source code (https://blog.csdn.net/qq_40161813/article/details/127823025)

Detailed execution flow of a single INSERT in MySQL 8.0 (https://blog.csdn.net/zgaoq/article/details/120371555)

About the author: Chen Kai, Java Development Engineer at XianKeHui.