How Do MySQL Locks Cause Deadlocks? Real‑World Cases and Solutions

1. What Is a Deadlock?

A deadlock occurs when multiple threads each wait for resources held by the other, creating a circular wait that blocks progress. For example, thread A holds lock B and requests lock A while thread B holds lock A and requests lock B.

2. Types of Locks in MySQL

MySQL provides several lock granularities. Using a user‑message table as an example, the table definition is:

CREATE TABLE `t_user_message` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'sender id',
  `object_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'receiver id',
  `relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'relation id',
  `is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'read flag (0 unread, 1 read)',
  `sid` int unsigned NOT NULL DEFAULT '0' COMMENT 'message count',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT 'status (0 invalid, 1 valid)',
  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'message content',
  `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'type (0 text, 1 voice, 2 image, 3 video, 4 emoji, 5 link)',
  `ext_json` varchar(1000) NOT NULL DEFAULT '' COMMENT 'extra field',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) USING BTREE COMMENT 'sender id index',
  KEY `idx_object_id` (`object_id`) USING BTREE COMMENT 'receiver id index',
  KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT 'relation id index'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='User message table';

Locks can be distinguished by granularity:

Row lock : locks only the specific row(s) accessed, e.g., SELECT * FROM t_user_message WHERE user_id=1001 FOR UPDATE; Table lock : locks the entire table, e.g.,

SELECT * FROM t_user_message FOR UPDATE;

3. Exclusive vs. Shared Locks

Shared lock : multiple transactions can read the locked rows simultaneously.

Exclusive lock : only one transaction can lock the rows for write; other transactions must wait.

4. Using Exclusive and Shared Locks

In InnoDB, UPDATE, INSERT, and DELETE automatically acquire exclusive locks. A plain SELECT does not lock unless special keywords are used.

To acquire an exclusive lock with a SELECT, add FOR UPDATE: SELECT * FROM t_user_message FOR UPDATE; To acquire a shared lock, use LOCK IN SHARE MODE:

SELECT * FROM t_user_message LOCK IN SHARE MODE;

5. Difference Between LOCK IN SHARE MODE and FOR UPDATE

Two MySQL sessions were used to demonstrate the behavior.

Session A disables autocommit and executes LOCK IN SHARE MODE, acquiring a shared lock on the whole table. Session B does the same and can still read without blocking.

When Session B later issues an UPDATE (which acquires an exclusive lock), it blocks because Session A’s shared lock is still held. After Session A commits, Session B proceeds.

Images below illustrate the steps (shared‑lock test, FOR UPDATE test, and the resulting deadlock):

6. Deadlock Caused by LOCK IN SHARE MODE

Consider an account table:

CREATE TABLE `t_account` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `coin` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

A transaction that first reads the row with LOCK IN SHARE MODE and then updates the balance can deadlock when many concurrent threads perform the same pattern, especially under high concurrency in a RocketMQ consumer.

Illustration of the deadlock:

7. How to Avoid the LOCK IN SHARE MODE Deadlock

Three practical solutions are presented:

Raise the isolation level to READ COMMITTED and avoid explicit locks.

// Transaction example without explicit lock
SET autocommit=0;
SELECT * FROM t_account WHERE user_id=111;
UPDATE t_account SET coin=coin+100, version=version+1 WHERE user_id=111;
INSERT INTO `transaction_log` (id, business, foreign_key) VALUES (1, X'6F726465722D62697A', X'...');
COMMIT;

Replace LOCK IN SHARE MODE with an optimistic lock using a version column.

// Optimistic lock example
SET autocommit=0;
SELECT * FROM t_account WHERE user_id=111 AND version=#{version};
UPDATE t_account SET coin=coin+100, version=version+1 WHERE user_id=111 AND version=#{version};
INSERT INTO `transaction_log` (id, business, foreign_key) VALUES (1, X'6F726465722D62697A', X'...');
COMMIT;

Introduce a distributed lock (e.g., Redis lock) at the business layer. This brute‑force approach serializes the critical section and eliminates the deadlock.

Regardless of the method, developers should be cautious when adding explicit locks to SELECT statements.

8. Types of Row Locks

Record Lock : locks a single row (the usual row lock).

Gap Lock : locks a range between index records, preventing inserts into the gap; appears in REPEATABLE READ or higher isolation levels.

Next‑Key Lock : a combination of record and gap lock; the default InnoDB lock for range scans under REPEATABLE READ.

9. Row‑Lock Incident Case

A badly designed message‑update flow caused a Record Lock deadlock on t_user_message. Two concurrent transactions each updated different rows but locked overlapping index ranges, leading to a deadlock.

Key statements:

START TRANSACTION;
UPDATE t_user_message SET is_read=1 WHERE user_id=1003 AND object_id=1004;
UPDATE t_user_message SET is_read=1 WHERE user_id=1001 AND object_id=1002;
COMMIT;

SET autocommit=0;
START TRANSACTION;
UPDATE t_user_message SET status=2 WHERE user_id=1001 AND object_id=1002;
UPDATE t_user_message SET status=2 WHERE user_id=1003 AND object_id=1004;
COMMIT;

Adjusting the order of updates and reducing transaction scope mitigated the issue (see diagram).

10. Gap‑Lock Blocking Example

When the session isolation level is set to REPEATABLE READ, a SELECT … FOR UPDATE on a range can create a gap lock. An INSERT into that gap blocks until the original transaction commits.

Illustrations:

11. MySQL Deadlock Detection

For MySQL 5.7/5.8, deadlocks can be inspected via the InnoDB lock tables:

SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

In MySQL 8.0 the tables changed; use the performance schema instead:

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

These queries help identify the waiting and holding transactions responsible for a deadlock.