Leveraging Semi-Consistent Read to Resolve MySQL Slow Logs

Background

A system experienced slow UPDATE operations with high Lock time in slow logs under MySQL 5.7.25 (RR isolation). Analysis revealed missing indexes on the name column, leading to full table scans and blocking.

Analysis

​Table Structure & Execution Plan

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Initial Execution Plan (No Index on `name`)
EXPLAIN UPDATE test SET name='test' WHERE name='a';
-- Output: Type=index, Key=PRIMARY, Rows=2.3M (Full Table Scan)

‍

Index Addition & Optimizer Decision​
Adding an index on name did not change the execution plan due to low cardinality. The optimizer chose the PRIMARY key scan (cost=475k) over the name index (cost=664k).

​Optimizer Trace Insight

‍

SET optimizer_trace="enabled=on";
UPDATE test SET name='test' WHERE name='a';
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

‍

Trace showed the cost-based decision favored full scans over index scans for low-selectivity predicates.

Solution

​Switch to RC Isolation Level​
Enabling ​RC (Read Committed)​​ isolation leverages ​semi-consistent reads, reducing lock contention:

1. Set Isolation Level:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

‍

2. Update Behavior:

1. Only locks matching rows (not entire table).
2. Avoids phantom reads but allows non-repeatable reads.

Result​
Concurrent updates on different name values no longer block each other, cutting execution time by ~50%.

Key Concepts

• ​Semi-Consistent Read:
  In RC isolation, InnoDB skips locking rows that don’t match the WHERE clause during UPDATE/DELETE, reducing lock overhead.
• ​Locking Mechanism:
  Without semi-consistency, RR acquires shared locks on scanned rows, causing contention even for non-matching rows.

Optimization Checklist

1. ​Isolation Level: Prefer RC for write-heavy workloads with low isolation needs.
2. ​Indexing: Add selective indexes to avoid full table scans.
3. ​Query Refactoring: Use covering indexes or partitioning for large datasets.

Conclusion

By combining ​RC isolation​ with ​semi-consistent reads, MySQL can significantly reduce lock contention in scenarios with low-selectivity predicates. Always balance consistency requirements against concurrency needs.

‍