MySQL Deadlock Analysis and Index Optimization Using ICP

1 Problem Phenomenon

After publishing an article about INSERT concurrency deadlocks, multiple deadlock reports were received. A concrete case is described below:

R&D reported a deadlock in the application and provided the following diagnostic information:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-07-04 06:02:40 0x7fc07dd0e700
*** (1) TRANSACTION:
TRANSACTION 182396268, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1
MySQL thread id 59269692, OS thread handle 140471135803136, query id 3738514953 192.168.0.215 user1 updating
delete from ltb2 where c = 'CCRSFD07E' and j = 'Y15' and b >= '20230717' and d != '1' and e != '1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396268 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 182396266, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1729
mysql tables in use 1, locked 1
28 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1
MySQL thread id 59261188, OS thread handle 140464721291008, query id 3738514964 192.168.0.214 user1 updating
update ltb2 set f = '0', g = '0', is_value_date = '0', h = '0', i = '0' where c = '22115001B' and j = 'Y4' and b >= '20230717'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396266 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396266 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------

The space id 603 page no 86 n bits 248 indicates the table‑space ID, the page number within the space, and the bit position in the lock bitmap (not a page offset). Normally the record offset is shown as heap no , which is absent in this example.

Basic Environment Information

Database version: Percona MySQL 5.7

Transaction isolation level: Read‑Committed

Table structure and indexes:

CREATE TABLE `ltb2` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `j` varchar(16) DEFAULT NULL COMMENT '',
  `c` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `b` date NOT NULL DEFAULT '2019-01-01' COMMENT '',
  `f` varchar(1) NOT NULL DEFAULT '' COMMENT '',
  `g` varchar(1) NOT NULL DEFAULT '' COMMENT '',
  `d` varchar(1) NOT NULL DEFAULT '' COMMENT '',
  `e` varchar(1) NOT NULL DEFAULT '' COMMENT '',
  `h` varchar(1) NOT NULL DEFAULT '' COMMENT '',
  `i` varchar(1) DEFAULT NULL COMMENT '',
  `LAST_UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uidx_1` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=270983 DEFAULT CHARSET=utf8mb4 COMMENT='';

Key Information Summary

Transaction T1

Statement

delete from ltb2 where c = 'code001' and j = 'Y15' and b >= '20230717' and d != '1' and e != '1'

Related object and record

space id 603 page no 86 n bits 248 index PRIMARY of table

testdb

.

ltb2

Held lock

Unknown

Waiting lock

lock_mode X locks rec but not gap waiting

Transaction T2

Statement

update ltb2 set f = '0', g = '0', is_value_date = '0', h = '0', i = '0' where c = '22115001B' and j = 'Y4' and b >= '20230717'

Related object and record

space id 603 page no 86 n bits 248 index PRIMARY of table

testdb

.

ltb2

Held lock

lock_mode X locks rec but not gap

Waiting lock

lock_mode X locks rec but not gap waiting

The deadlock occurs on the primary‑key index, even though the query predicates do not use the primary‑key column.

2 SQL Execution Situation

Execution Plan

Both SQL statements use columns b and c in the WHERE clause, and these columns form the unique index uidx_1(b,c) . Simplify the SQL to a SELECT statement and check the execution plan:

mysql> desc select * from ltb2 where b >= '20230717' and c = 'code001';
# partial result
+------+-------------------+------+-------+
| type | possible_keys     | key  | Extra |
+------+-------------------+------+-------+
| ALL  | uidx_1            | NULL | Using where |
+------+-------------------+------+-------+

From MySQL 5.6 onward, you can view the execution plan of INSERT/UPDATE/DELETE directly, but the author prefers to use SELECT for safety.

The possible index is uidx_1(b,c) , yet the optimizer chooses a full table scan because the condition on b is a range ( b >= '20230717' ) rather than an equality, so only the leftmost column b can be used.

Data distribution check:

mysql> select count(1) from ltb2;
+----------+
| count(1) |
+----------+
|      4509 |
+----------+

mysql> select count(1) from ltb2 where b >= '20230717';
+----------+
| count(1) |
+----------+
|      1275 |
+----------+

Approximately 28% of rows satisfy the b condition, which is large enough for the optimizer to avoid using the index.

ICP Feature

When the condition value is increased (but still below the column's maximum), the optimizer can use Index Condition Push‑Down (ICP):

mysql> desc select * from ltb2 where b >= '20990717';
+----------+---------+-------------------+
| key_len  | rows    | Extra             |
+----------+---------+-------------------+
| 3        | 64      | Using index condition |
+----------+---------+-------------------+

ICP works as follows:

Fetch the next index tuple (not the full row).

Test the WHERE conditions that can be evaluated using only index columns; skip the row if they fail.

If they pass, locate and read the full row.

Apply the remaining WHERE conditions on the full row.

Adding the column c to the predicate makes the optimizer use the index:

mysql> desc select * from ltb2 where b >= '20990717' and c = 'code001';
+----------+---------+-------------------+
| key_len  | rows    | Extra             |
+----------+---------+-------------------+
| 133      | 64      | Using Index condition |
+----------+---------+-------------------+

Execution Plan Trace

Using SET optimizer_trace='enabled=on' to trace the plan generation for three SQL variants (SQL‑1, SQL‑2, SQL‑3). The trace shows that the optimizer only recognises column b during the cost estimation phase, so both SQL‑1 and SQL‑2 have identical cost values.

SQL‑1 Trace (with b and c)

... "index": "uidx_1", "rows": 64, "cost": 77.81, "chosen": true ... "resulting_rows": 64, "cost": 90.61, "chosen": true ...

SQL‑2 Trace (only b)

... "index": "uidx_1", "rows": 64, "cost": 77.81, "chosen": true ... "resulting_rows": 64, "cost": 90.61, "chosen": true ...

Because the optimizer estimates the same cost for both, the final decision depends on the execution phase where ICP can be applied.

SQL‑3 Trace (full table scan vs index scan)

"table_scan": {"rows": 4669, "cost": 1018.9}
"range_scan": {"rows": 1273, "cost": 1528.6, "cause": "cost"}
"best_access_path": {"access_type": "scan", "rows_to_scan": 4669, "cost": 1016.8, "chosen": true}

The optimizer chooses a full table scan because the estimated cost of the index scan is higher.

Forcing the index with FORCE INDEX (uidx_1) yields:

+----------+---------+-------------------+
| key_len  | rows    | Extra             |
+----------+---------+-------------------+
| 133      | 1273    | Using Index condition |
+----------+---------+-------------------+

Summary

Because column b is the leftmost column of the unique index and the condition on it is a range, the optimizer can only "use" column b . It estimates row count and cost based on that column alone. If the estimated cost of using the index is lower, the optimizer will use the index together with ICP; otherwise it will fall back to a full table scan.

3 SQL Optimization Plan

After identifying the cause, adjust the index order to make the range column the leftmost column:

alter table ltb2 drop index uidx_1;
alter table ltb2 add index uidx_1(c,b);
alter table ltb2 add index idx_(b);

Why the Deadlock Occurs

Even after the query cannot use the index and performs a full table scan, a deadlock still appears. Based on the INSERT deadlock analysis article, the following speculative timeline is presented:

T1

Transaction 2 executes UPDATE, acquires LOCK_X, LOCK_REC_NOT_GAP on the target row.

T2

Transaction 1 executes DELETE, attempts to acquire the same lock, gets blocked, and creates a lock‑wait record.

T3

Transaction 2 continues, needs another lock ( lock_mode X locks rec but not gap ) on the same row, gets blocked by the lock‑wait created by Transaction 1, leading to a circular wait and deadlock.

Further questions for investigation:

Are the lock acquisition points correct?

Where does UPDATE acquire the second lock?

Does the hypothesis hold, and what is the exact logic?

If not, what is the real deadlock cause?

Are there differences between full‑table‑scan and index‑based execution regarding lock handling?

Besides index adjustment, what other methods can avoid the issue?

Reading Recommendations

How to Avoid INSERT Deadlocks under RC Isolation Level

Fault Analysis | Master‑Slave Data Inconsistency Without Errors

Technical Translation | Observability in MySQL 8 Replication

Technical Share | Setting Up a MySQL Source‑Code Learning Environment on Windows

Technical Translation | History and Differences of MySQL and MariaDB Version Management

Technical Translation | Overview of MySQL's New Version Model

Technical Translation | Hidden Features in MySQL 8.0

Event Notice: Aikexing Technology Conference – Shanghai

Aikexing will hold a technical conference on September 21 in Shanghai titled “New Opportunities and Development Trends of Domestic Databases and AI Databases”. The event focuses on ActionDB (based on OceanBase) and TensorDB (vector database) and aims to share industry trends and technical features.

Agenda : (image omitted)

Offline Check‑in : Hotel 2nd floor, Meeting Rooms 1‑3, 13:00‑14:00, bring registration SMS or screenshot.

Venue : Hyatt Jiaxuan Hotel, Shanghai Tianshan Plaza, No. 421 Ziyun Road, Changning District.

Transportation :

Metro: Line 2 to Loushan Road Station, Exit 2, walk 683 m.

Driving: Paid parking at B3 level, elevator to 1st floor.

2023 OSCAR Open‑Source Industry Conference – Open Market

Aikexing Open‑Source Community will have a booth at the Open Market. Completing the booth activities earns community gifts.

Conference name: 2023 OSCAR Open‑Source Industry Conference

Date: September 21, 2023

Location: Liting Huayuan Hotel, Haidian District, Beijing

About SQLE

SQLE is an open‑source SQL audit tool from Aikexing Open‑Source Community, supporting multi‑scenario review, standardized release processes, native MySQL audit, and extensible database types.

GitHub: https://github.com/actiontech/sqle

Documentation: https://actiontech.github.io/sqle-docs/

Website: https://opensource.actionsky.com/sqle/

WeChat technical group: add admin’s WeChat ID “ActionOpenSource”.