Analyzing Slow UPDATE Locks in MySQL 5.7 and Optimizing with Indexes and Optimizer Trace

The article investigates why an UPDATE statement on a MySQL 5.7 InnoDB table with RR isolation experiences high lock time, demonstrates how missing indexes cause full‑table scans and blocking, and shows how adding an index and using optimizer trace can diagnose and resolve the issue.

InnoDBOptimizer_traceRR Isolation

0 likes · 8 min read

Analyzing Slow UPDATE Locks in MySQL 5.7 and Optimizing with Indexes and Optimizer Trace

Youzan Coder

Jan 30, 2019 · Operations

Why Do MySQL RR Transactions Deadlock? A Deep Dive into Index Locking

This article examines two MySQL deadlock scenarios under REPEATABLE READ isolation—one with a non‑unique index and another with a unique index—by presenting table definitions, test data, raw deadlock logs, and step‑by‑step analysis of lock acquisition and waiting, ultimately offering practical recommendations to avoid such deadlocks.

Database operationsInnoDBRR Isolation

0 likes · 12 min read

Why Do MySQL RR Transactions Deadlock? A Deep Dive into Index Locking