2

MySQL's REPLACE INTO causes deadlocks during high-concurrency batch data insertion. What locks does REPLACE INTO acquire during execution, and why do deadlocks occur?

Known Clues:

  1. The scenario where data already exists before insertion is ruled out.

However, concurrent updates to the inserted data by other threads (before or after insertion) are possible, though updates are single-row operations. 2. Table structure (200 million rows):

CREATE TABLE downlink_record (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  message_id varchar(128) NOT NULL,
  template_id varchar(20) NOT NULL,
  address varchar(256) NOT NULL,
  sign varchar(64) DEFAULT NULL,
  sign_id varchar(64) DEFAULT NULL,
  order_id varchar(256) DEFAULT NULL,
  sms_type int(11) DEFAULT NULL,
  amount int(11) DEFAULT NULL,
  report_num int(11) DEFAULT NULL,
  agent_name varchar(128) DEFAULT NULL,
  account_name varchar(128) DEFAULT NULL,
  team_id varchar(64) DEFAULT NULL,
  country_name varchar(20) DEFAULT NULL,
  router_name varchar(128) DEFAULT NULL,
  operator_name varchar(128) DEFAULT NULL,
  error_code varchar(128) DEFAULT NULL,
  send_status int(11) DEFAULT NULL,
  verify_status int(11) DEFAULT NULL,
  retry_count int(11) DEFAULT NULL,
  create_time bigint(20) DEFAULT NULL,
  deliver_time bigint(20) DEFAULT NULL,
  report_time bigint(20) DEFAULT NULL,
  receive_time bigint(20) DEFAULT NULL,
  verify_time bigint(20) DEFAULT NULL,
  last_update_time bigint(20) DEFAULT NULL,
  content text,
  extra_info text,
  PRIMARY KEY (id),
  UNIQUE KEY idx_message_id_retry_count (message_id, retry_count),
  KEY idx_message_id (message_id(120)),
  KEY idx_team_id (team_id),
  KEY idx_order_agent (order_id(191), agent_name),
  KEY idx_deliver_time (deliver_time),
  KEY idx_receive_time (receive_time),
  KEY idx_msg_addr_temp_status (address, template_id, send_status),
  KEY idx_create_time (create_time),
  KEY idx_template_id_send_status_create_time (template_id, send_status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. All deadlocks involve the idx_message_id_retry_count index. Deadlock log:
2025-09-24 18:53:59 0x7fd24dbff700
*** (1) TRANSACTION:
TRANSACTION 55063751429, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 670729, OS thread handle 140553453934336, query id 6781831678 10.142.167.7 sms_x update
REPLACE INTO sms_downlink_record (...) VALUES (...) -- Truncated; no duplicate data between REPLACE operations
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 75 page no 16582916 n bits 344 index idx_message_id_retry_count of table sms.sms_downlink_record trx id 55063751429 lock_mode X insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 55063751430, ACTIVE 0 sec inserting, thread declared inside InnoDB 4998
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3
MySQL thread id 671750, OS thread handle 140541224285952, query id 6781831679 10.142.210.13 sms_x update
REPLACE INTO sms_downlink_record (...) VALUES (...) -- Truncated; no duplicate data between REPLACE operations
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 75 page no 16582916 n bits 344 index idx_message_id_retry_count of table sms.sms_downlink_record trx id 55063751430 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 75 page no 16582916 n bits 344 index idx_message_id_retry_count of table sms.sms_downlink_record trx id 55063751430 lock_mode X insert intention waiting

*** WE ROLL BACK TRANSACTION (1)

I tried running a concurrent replace into script locally, but I was never able to reproduce the issue. This deadlock problem occurred in our company's centrally managed database with access control, and I cannot arbitrarily write data to the production environment.

I would like to know under what circumstances a GAP lock or X lock will be added to replace into

Improve this question
1
  • REPLACE INTO causes deadlocks Yes. It does. At the very least use INSERT ... ON DUPLICATE KEY UPDATE instead. Instead of loading rows one at a time, insert them into a staging table and then use INSERT INTO target .... SELECT ... FROM staging .... If you send the batches from a client, send multiple rows at once with ROW. This article explains why REPLACE has issues. Commented Sep 26 at 12:47

1 Answer 1

Reset to default
3

The deadlock report you show says that both sessions are waiting for an insert intention lock.

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... lock_mode X insert intention waiting
                             ^^^^^^^^^^^^^^^^

In https://bugs.mysql.com/bug.php?id=25847, the InnoDB architect writes:

The only way to ensure that in InnoDB is to lock the 'supremum' record. InnoDB does not have 'predicate locking', which would allow us to lock just the non-existent record i = 1000000000.

In other words, InnoDB must be aggressive about locking the large gap all the way to the "supremum" which is the largest value in the column's domain, because it has no way of locking in a more targeted way.

That bug was reported in 2007, and remains unfixed. I expect it will never be fixed in InnoDB.

For the solution for deadlocks, you can use either of these methods:

  • No concurrency: Don't run REPLACE INTO sms_downlink_record... in more than one session at a time.

  • Pessimistic locking: If you do run such REPLACE operations in concurrent sessions, then wrap your code in some critical section enforcement, i.e. a global lock. You can do this in your application, or you can use MySQL's GET_LOCK() function. Because the sessions wait on the single global lock, they may be queued waiting for the lock, but they won't deadlock.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.