Resolving MySQL 5.7 Deadlocks via Index Optimization & ICP Insights

Problem Description​​

Developers reported a deadlock during application updates. The diagnostic details revealed conflicting transactions on the ltb2 table:

-- Transaction 1 (Blocked):
DELETE FROM ltb2 
WHERE c = 'CCRSFD07E' AND j = 'Y15' AND b >= '20230717' AND d != '1' AND e != '1';

-- Transaction 2 (Holding Lock):
UPDATE ltb2 
SET f = '0', g = '0', is_value_date = '0', h = '0', i = '0' 
WHERE c = '22115001B' AND j = 'Y4' AND b >= '20230717';

‍

Both transactions targeted the primary key index on space id 603, leading to a deadlock due to incompatible lock requests.

​​Key Observations​​

1. Execution Plan Anomaly​​:
Despite b and c forming a unique index (uidx_1), the optimizer chose a full table scan instead of leveraging the index for queries like:

SELECT * FROM ltb2 WHERE b >= '20230717' AND c = 'code001';

‍

This was puzzling given that b is the leading column in uidx_1.

​​2. Index Condition Pushdown (ICP) Misuse​​:
While ICP activated for some queries (e.g., b >= '20990717'), it failed to optimize the critical DELETE/UPDATE statements. The optimizer underestimated the impact of non-equality conditions on index usage.

3. Data Distribution Impact​​:
Approximately 28% of rows matched b >= '20230717', exceeding the typical threshold for index efficiency. However, this alone didn’t justify avoiding the index entirely.

Root Cause Analysis via OPTIMIZER TRACE​​

Enabling OPTIMIZER_TRACE exposed discrepancies in cost estimation:

• The optimizer prioritized full table scans over index scans due to inaccurate row count predictions for range queries on b.
• ICP’s benefits were overlooked because the query’s complex conditions confused cost-based optimization.

​​Solution​​

1. ​​Index Restructuring​​:
Added a composite index uidx_1(c, b) to align with query patterns and a secondary index idx_b(b) to cover edge cases.

ALTER TABLE ltb2 DROP INDEX uidx_1;
ALTER TABLE ltb2 ADD INDEX uidx_1(c, b);
ALTER TABLE ltb2 ADD INDEX idx_b(b);

‍

2. Query Refactoring​​:
Modified DELETE statements to explicitly reference indexed columns first, ensuring ICP activation.

​​Deadlock Explanation​​
The deadlock arose from conflicting lock acquisitions during full table scans:

1. Transaction T2 acquired an exclusive lock on a primary key record.
2. Transaction T1, blocked by T2’s lock, queued a conflicting lock request.
3. T2 attempted additional locks while processing, triggering a cyclic dependency.