Why Two Non‑Overlapping UPDATE Statements Trigger a MySQL Deadlock and How to Prevent It

Background

During the National Day holiday an unexpected MySQL deadlock alarm appeared. The log showed two UPDATE statements on user_feed_26 that seemed unrelated, prompting a deep dive into why the deadlock occurred.

Log Analysis

The first transaction log ( TRANSACTION 6286508066) contains:

TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1
MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating
update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting

The second transaction log ( TRANSACTION 6286508067) shows:

TRANSACTION 6286508067, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4980
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1136, 22 row lock(s), undo log entries 3
MySQL thread id 189619144, OS thread handle 140620050204416, query id 1148803197 10.200.17.37 pt_user updating
UPDATE `user_feed_26` SET `notification` = '1' , `mtime` = '2020-10-03 09:11:11' WHERE `user_id` = '2000000126212250' AND `action` in ('deal','price_changed','ting_shou','house_new_picture','house_new_vr','price_changed_rise','on_shelf_again') AND `notification` = '0'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec

Both transactions wait for a record lock on the same index idx_user_id, creating a circular wait and thus a deadlock.

Reproducing the Issue

A test table was created:

CREATE TABLE `user_feed_26` (
  `feed_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  ...
  PRIMARY KEY (`feed_id`),
  KEY `idx_user_id` (`user_id`,`action`,`notification`,`feed_target`)
) ENGINE=InnoDB AUTO_INCREMENT=371826027 DEFAULT CHARSET=utf8 COMMENT='用户推送表';

With a small data set the two UPDATE statements only caused lock waiting, not a deadlock, because the optimizer chose the primary‑key index. After inserting ~1 million rows, the optimizer switched to the composite index idx_user_id, and the deadlock reappeared.

MySQL Locking Principles

Lock granularity is a next‑key lock (a half‑open interval).

Only the rows accessed during the search are locked.

Optimizations:

Equality search on a unique index degrades next‑key lock to a row lock.

Equality search on a non‑unique index may degrade to a gap lock when the right‑hand boundary of the searched interval does not satisfy the equality condition.

In the presented case the UPDATE statements caused gap locks on idx_user_id, leading to mutual waiting.

Root Cause Illustration

When transaction 2 updates rows where action = 'deal', it acquires a gap lock on the interval (1, 3). Transaction 1, updating rows where action = 'district_weekly', acquires a gap lock on (5, +∞) and waits for an insert‑intention lock on (1, 7). Transaction 2 later tries to update rows where action = 'on_shelf_again' and waits for (5, +∞), completing the circular wait.

Prevention Strategies

Update rows via a unique key (usually the primary key). First SELECT the primary‑key values that satisfy the condition, then UPDATE by that key.

Avoid running multiple heavy read‑write scripts on the same table simultaneously; stagger scheduled jobs.

Consider lowering the isolation level from REPEATABLE READ (RR) to READ COMMITTED (RC) if phantom reads are acceptable, which reduces gap‑lock generation.

Example of a safe update pattern:

SELECT id FROM user_feed_26 WHERE user_id = ? AND action = ? AND notification = 0;
UPDATE user_feed_26 SET notification = 1, mtime = NOW() WHERE id = ?;

Reference

For further reading see: https://www.cnblogs.com/suminem/p/13267828.html