Analyzing MySQL Deadlock Cases and Prevention Strategies

During a holiday period an unexpected MySQL error {"message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction; ..."} appeared, prompting a deep dive into the cause of the deadlock.

The investigation began by examining the InnoDB deadlock logs. Two transactions were identified, each executing an UPDATE user_feed_26 statement with different WHERE clauses but sharing the same composite index idx_user_id(user_id, action, notification, feed_target) . Transaction 1 waited for a record lock on idx_user_id , while Transaction 2 already held that lock and waited for a different lock, creating a circular wait.

Key excerpts from the logs show the lock details:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id ... lock_mode X locks gap before rec insert intention waiting

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id ... lock_mode X locks gap before rec

Although the WHERE conditions did not overlap, both statements accessed the same index range, causing gap locks that conflicted.

To reproduce 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 DEFAULT CHARSET=utf8 COMMENT='用户推送表';

With a small dataset the update used the primary key and no deadlock occurred. After inserting about one million rows, the optimizer chose the composite index, and the deadlock was reproduced.

The article then explains MySQL locking fundamentals: the default next‑key lock (a half‑open interval) and how it degrades to row locks on unique indexes or gap locks on non‑unique indexes when the search range extends beyond the exact match. An illustrative example shows how a query for a non‑existent value creates a gap lock.

Based on this understanding, several mitigation strategies are recommended:

Update rows via a unique (usually primary) key after selecting the target IDs.

Avoid running multiple large read‑write scripts concurrently; stagger scheduled jobs.

Consider lowering the isolation level from REPEATABLE READ to READ COMMITTED to reduce gap‑lock usage when phantom reads are acceptable.

Finally, a concise example demonstrates the safe update pattern:

SELECT id FROM table WHERE a=? AND b=?;
UPDATE table SET column=xxx WHERE id=?;

References and additional reading links are provided at the end of the article.