Analysis of InnoDB Deadlock in Pre‑Production Inventory Updates and Locking Mechanisms

In a pre‑release environment, a message‑driven transaction that writes inventory caused MySQL to encounter a deadlock, resulting in write failures.

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: rpc error: code = Aborted desc = Deadlock found when trying to get lock;

Initial investigation revealed that two requests attempted to write inventory at the same moment, leading to mutual deadlock despite a one‑second time difference.

The transaction logic can be represented by the following pseudo‑code:

start transaction // 1. Query data data = select for update(tenantId, storeId, skuId); if (data == null) { // Insert data insert(tenantId, storeId, skuId); } else { // Update data update(tenantId, storeId, skuId); } end transaction

The underlying table uses the InnoDB engine with Repeatable Read isolation level, and its index structure is defined as:

CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID', `user_id` bigint(20) DEFAULT NULL COMMENT 'User ID', `mobile_num` bigint(20) NOT NULL COMMENT 'Mobile number', PRIMARY KEY (`id`), UNIQUE KEY `IDX_USER_ID` (`user_id`), KEY `IDX_MOBILE_NUM` (`mobile_num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User information table';

Understanding InnoDB locking begins with row‑level locks, which can be shared (S) or exclusive (X). The lock compatibility matrix shows that shared locks are compatible with other shared locks, while exclusive locks conflict with both shared and exclusive locks.

Under Repeatable Read, InnoDB uses next‑key locks and gap locks. For example, a unique index equality query such as:

select * from user where id = 5 for update

or

select * from user where user_id = 5 for update

locks only the matching index record (record lock). If the queried value does not exist, e.g.,

select * from user where id = 6 for update

the engine acquires a gap lock on the interval (5,8].

Range queries on a unique index, like

select * from user where id >= 4 and id < 8 for update

result in gap locks on the surrounding intervals, ultimately locking (1,9).

For non‑unique indexes, both a next‑key lock on the matching interval and a gap lock on the subsequent interval are taken. For instance:

select * from user where mobile_num = 6 for update

locks (3,6] as a next‑key lock and (6,7) as a gap lock.

If the value is absent:

select * from user where mobile_num = 8 for update

the engine locks (7,9] as a gap lock.

Non‑unique range queries such as

select * from user where mobile_num >= 6 and mobile_num < 8 for update

acquire a next‑key lock on (3,6] and a gap lock on (7,9).

InnoDB also employs intention locks (IS, IX) at the table level to indicate forthcoming row‑level locks. Shared intention locks (IS) precede row‑level shared locks, while exclusive intention locks (IX) precede row‑level exclusive locks. The compatibility matrix for table‑level locks shows that IX is compatible with other IX and IS locks, but not with X or S locks.

Insert intention locks are a special form of gap lock set before an INSERT operation.

Process analysis of the two conflicting transactions:

1. Both transactions start simultaneously. 2. Transaction A executes SELECT ... FOR UPDATE , acquiring an IX intention lock on the table and an X lock (which degrades to a gap lock because the row does not exist). 3. Transaction B performs the same steps; its IX lock is compatible, and it also obtains a gap lock. 4. When Transaction A attempts to INSERT, it must acquire an insert intention lock, but Transaction B already holds an X gap lock on the same interval, causing a conflict. 5. Transaction B faces the same conflict when it tries to INSERT. 6. Both transactions wait for each other, and MySQL’s deadlock detector aborts one, reporting a deadlock error.

The article notes that if the queried row exists, the deadlock does not occur because the lock mode differs.

Proposed solutions include:

Using CAS combined with a distributed lock (keyed by store_skuId_version ) to serialize concurrent writes before the transaction begins.

Rewriting the transaction flow to first read without locking, then conditionally acquire a SELECT ... FOR UPDATE only when an update is needed.

Changing the isolation level to Read Committed (RC), which reduces the use of next‑key locks, leaving only record locks and thus avoiding the deadlock scenario.

References:

InnoDB Locking Documentation: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

Various blog posts and articles on MySQL locking behavior.