Common Deadlock Scenarios in MySQL: REPLACE INTO Statement Concurrency

By Zongzhi Chen

MySQL Replace Into Issue

Concurrent execution of REPLACE INTO statements in MySQL can cause deadlocks.

In the previous article, we discussed how unique key checks in MySQL necessitate adding gap locks at the READ COMMITTED isolation level, which can lead to some unexpected lock waits.

Another frequent problem is deadlocks triggered by unique key checks, a common issue we often get feedback on from users online. In this article, we will examine several cases of such deadlocks.

REPLACE INTO operations are commonly used when it's unclear whether data already exists in the table or if there's a potential unique key conflict. Thus, REPLACE INTO or INSERT ON DUPLICATE KEY UPDATE statements are used to automatically update the corresponding row upon conflict.

However, these operations can lead to deadlocks in concurrent scenarios when there is a unique key involved. Let's explore a simple case to understand why this happens:

Deadlock scenarios like these can be replicated using GDB and a script.

create table t(a int AUTO_INCREMENT, b int, PRIMARY KEY (a), UNIQUE KEY (b));

insert into t(a, b) values (100, 8);
    
session1:
replace into t(a, b) values (10, 8);

session2:
replace into t(a, b) values (11, 8);
(40001): Deadlock found when trying to get lock; try restarting transaction

Or you can use the following script to reproduce a random scenario without GDB:

#! /bin/bash

MYSQL="mysql -h127.0.0.1 -P2255 -uroot test"

$MYSQL -e "create table t(a int AUTO_INCREMENT, b int, PRIMARY KEY (a), UNIQUE KEY (b))"

while true
do

  $MYSQL -e "replace into t(b) values (8)" &
  $MYSQL -e "replace into t(b) values (8)" &
  $MYSQL -e "replace into t(b) values (8)" &

  wait;
done

When Session 1 and Session 2 are concurrently inserted, a deadlock is prone to occur. It is similar to the scenario where users insert data concurrently.

The deadlock information HOLDS THE LOCK and WAITING FOR THIS LOCK TO BE GRANTED in the preceding figure is wrong and misleading. The official version has since been corrected in newer releases. HOLDS THE LOCK is inaccurate because the transaction has not yet obtained the X lock.

We see that Trx 1 is waiting for the next-key X lock on (8, 100).

Meanwhile, Trx 2 holds the next-key X lock on (8, 100) but is waiting for the insert_intention lock on the same record.

So why does a deadlock occur?

Let's examine the process of executing a single REPLACE INTO statement.

In general, if the first insertion attempt of a REPLACE INTO operation encounters a unique index conflict, the operation must either execute an UPDATE or perform a DELETE followed by INSERT. However, the transaction locks added during the failed first insert are not released immediately; they are held until the transaction is committed. This behavior is in place to uphold the guarantees of MySQL's Two-Phase Locking (2PL) mechanism.

The pseudocode for the REPLACE INTO statement is roughly as follows:

For all the REPLACE INTO or INSERT ON DUPLICATE KEY UPDATE statements, the following execution method is used: Sql_cmd_insert_values => execute_inner() 

Here, the REPLACE INTO or INSERT ON DUPLICATE KEY UPDATE statement is executed in this loop.

  if (duplicate_handling == DUP_REPLACE || duplicate_handling == DUP_UPDATE) {
    DBUG_ASSERT(duplicate_handling != DUP_UPDATE || update != NULL);
    while ((error = table->file->ha_write_row(table->record[0]))) {
        // ...
      if (duplicate_handling == DUP_UPDATE) {

    The branch here is the DUPLICATE KEY scenario where the ON DUPLICATE KEY UPDATE statement is executed.
    For ON DUPLICATE KEY UPDATE, when encountering an error, update through table->file->ha_update_row.
          } else /* DUP_REPLACE */ {
    duplicate_handling == DUP_REPLACE is to handle the error that occurs in the REPLACE INTO scenario.
    In the REPLACE INTO scenario, if the inserted key encounters a conflict, the handling is divided into two situations:
    For REPLACE INTO, if the inserted key conflicts with the last unique index and there is no foreign key constraint or DELETE trigger, when encountering an error, update through ha_update_row.

    Otherwise, update through DELETE + INSERT. However, more execution means more consumption.

    The specific code is as follows:
If ha_write_row() fails, delete_row() will be executed. After execution, it will go to this while loop for re-insertion.
    if ((error = table->file->ha_delete_row(table->record[1]))) goto err;
    /* Let us attempt do write_row() once more */
    }

When executing two REPLACE INTO statements, if Thread 1 encounters a unique index conflict during the first phase of the REPLACE INTO operation, it acquires the next-key lock for (8, 100).

Meanwhile, Thread 2 is also executing a REPLACE INTO statement. During the unique key conflict check, when executing the row_ins_scan_sec_index_for_duplicate() function, it requests the next-key lock for (8, 100). However, this lock is already held by Thread 1, causing Thread 2 to wait.

Then, Thread 1 proceeds with the UPDATE operation. In InnoDB, for secondary indexes, a DELETE operation is executed before the INSERT operation. When it comes to inserting, Thread 1 needs to acquire the insert intention lock of (8, 100). Currently, InnoDB considers it a conflict when the insert intention lock check finds that the corresponding record is either being waited on or already held as a next-key lock by a transaction. Since Thread 2 is waiting for the next-key lock on (8, 100), Thread 1 cannot acquire the insert intention lock and must wait.

This raises a question: why does the request for the insert intention lock fail if another transaction is already waiting for the next-key lock on the same record?

This issue is explained in the function rec_lock_check_conflict(). If the request for the intention lock is successful, the record will be inserted. However, the transaction waiting for the lock of this record will need to wait for two locks of two different records.

For example, if trx2 is waiting for the next-key lock of (4,10] and trx1 is allowed to insert record 7, according to the lock inheritance mechanism, record 7 will inherit the next-key lock of record 10. As a result, trx2 will wait for two locks of two records. To address this, the existing lock waiting wake-up mechanism needs to be changed. A transaction should only wait for one lock, and when that lock is released, the waiting transaction will be awakened accordingly.

To avoid such issues, in MySQL InnoDB, if other transactions are already waiting for the next-key lock, the request for the insert intention lock will fail.

In the given scenario, Thread 2 is waiting for Thread 1 to release the next-key lock, while Thread 1 is waiting for Thread 2 to acquire and release the next-key lock. This situation leads to a deadlock, where Thread 1 and Thread 2 are waiting for each other.

Community

Common Deadlock Scenarios in MySQL: REPLACE INTO Statement Concurrency

MySQL Replace Into Issue

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for MySQL

Database for FinTech Solution

Oracle Database Migration Solution

Database Migration Solution