Why Did MySQL Deadlock Occur? A Deep Dive into InnoDB Locking and Index Design

1. Symptom

During a nightly deployment a MySQL deadlock alarm flooded the monitoring system. The error message was:

{"errorCode":"SYSTEM_ERROR","errorMsg":"nested exception is org.apache.ibatis.exceptions.PersistenceException: Error updating database. Cause: ERR-CODE: [TDDL-4614 [ERR_EXECUTE_ON_MYSQL] Deadlock found when trying to get lock; The error occurred while setting parameters
### SQL: update fund_transfer_stream set gmt_modified=now(),state = ? where fund_transfer_order_no = ? and seller_id = ? and state = 'NEW'"}

The log pinpointed the affected database and table.

2. Environment

MySQL 5.7 with InnoDB engine, transaction isolation level READ‑COMMITTED.

Table definition (irrelevant columns omitted):

CREATE TABLE `fund_transfer_stream` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `gmt_create` datetime NOT NULL COMMENT 'creation time',
  `gmt_modified` datetime NOT NULL COMMENT 'modification time',
  `pay_scene_name` varchar(256) NOT NULL COMMENT 'payment scene name',
  `pay_scene_version` varchar(256) DEFAULT NULL COMMENT 'payment scene version',
  `identifier` varchar(256) NOT NULL COMMENT 'unique identifier',
  `seller_id` varchar(64) NOT NULL COMMENT 'seller ID',
  `state` varchar(64) DEFAULT NULL COMMENT 'status',
  `fund_transfer_order_no` varchar(256) DEFAULT NULL COMMENT 'order number',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_scene_identifier` (`identifier`),
  KEY `idx_seller` (`seller_id`),
  KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='fund flow';

The table has one clustered (primary) index and two secondary indexes. idx_seller_transNo is a prefix index on the first 20 characters of fund_transfer_order_no.

3. Deadlock Log

Retrieve detailed status with: show engine innodb status; Relevant excerpt (simplified):

*** (1) TRANSACTION:
TRANSACTION 173268495 ACTIVE 0 sec fetching rows
... UPDATE ... where state='NEW' AND seller_id='38921111' AND fund_transfer_order_no='99010015000805619031958363857'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index idx_seller_transNo ... lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY ... lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 173268500 ACTIVE 0 sec fetching rows
... UPDATE ... where ... fund_transfer_order_no='99010015000805619031957477256'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY ... lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index idx_seller_transNo ... lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Transaction 1 holds a lock on idx_seller_transNo and waits for the primary‑key lock.

Transaction 2 holds the primary‑key lock and waits for idx_seller_transNo.

Both locks are exclusive record locks (X) without gaps.

4. Investigation

Spring transaction code:

@Transactional(rollbackFor = Exception.class)
public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {
    fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
    return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo, "PROCESSING");
}

Two UPDATE statements are executed in the same transaction:

-- updateFundStreamId
UPDATE fund_transfer_stream
SET gmt_modified = NOW(),
    fund_transfer_order_no = #{fundTransferOrderNo}
WHERE id = #{id} AND seller_id = #{sellerId};

-- updateStatus
UPDATE fund_transfer_stream
SET gmt_modified = NOW(),
    state = #{state}
WHERE fund_transfer_order_no = #{fundTransferOrderNo}
  AND seller_id = #{sellerId}
  AND state = 'NEW';

Execution plans (captured after the deadlock) show: updateFundStreamId uses the PRIMARY index. updateStatus uses the secondary index idx_seller_transNo because the optimizer estimates a lower cost for the prefix index.

Root cause: the prefix index idx_seller_transNo stores only the first 20 characters of fund_transfer_order_no. Two different orders with identical 20‑character prefixes (e.g., 99010015000805619031) map to the same secondary index entry, causing the two transactions to lock the same secondary index row while each also needs the primary‑key row of the other transaction.

5. Locking Mechanism

In InnoDB, row‑level locks are implemented as index locks. When a statement accesses a secondary index, MySQL first locks the secondary index entry, then locks the corresponding primary‑key rows. The primary index (clustered) stores the full row; secondary indexes store only the primary‑key value.

Deadlock occurs because the two sessions acquire locks in opposite order:

Transaction 1: lock PRIMARY → lock secondary idx_seller_transNo.

Transaction 2: lock secondary idx_seller_transNo → lock PRIMARY.

This circular wait triggers the deadlock.

6. Mitigation

Modify the index : increase the prefix length of idx_seller_transNo (e.g., to 50 characters) or drop the prefix entirely so that the full fund_transfer_order_no is indexed. This prevents different orders from colliding on the same secondary index entry.

Change the application code :

Perform all updates by primary key ( id) so that only the clustered index is used.

Avoid multiple UPDATE statements that modify the same row within a single transaction.

Even after extending the prefix, the optimizer may still choose a shorter index (e.g., idx_seller), so primary‑key‑based updates are the most reliable fix.

7. References

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

http://hedengcheng.com/?p=771

https://www.hollischuang.com/archives/914