This article explains MySQL deadlocks with a concrete example of two transactions updating rows in opposite order, demonstrates deadlock detection via SHOW ENGINE INNODB STATUS, and lists prevention techniques including small transactions, proper isolation levels, lock wait timeouts, consistent operation order, and indexing.
The OceanBase team’s LCL+ algorithm, a lock‑chain‑length based distributed deadlock detection and elimination method, eliminates the need for a global wait‑for graph, achieves single‑victim unlocks, and dramatically improves throughput while reducing tail latency in production database clusters.
Thread dumps provide a snapshot of all active JVM threads, including their state, stack trace, and metadata, enabling developers to diagnose performance issues such as slow responses, high CPU usage, deadlocks, and thread pool inefficiencies by examining key fields and applying practical analysis techniques.
This article explains how MySQL's InnoDB deadlock detection thread works, when it checks for deadlocks based on the innodb_deadlock_detect variable, how it discovers lock‑wait cycles, performs a secondary verification using snapshot and slot information, and finally summarizes the overall process.
This article summarizes UnionPay’s evolution of a distributed MySQL middleware platform, detailing its multi‑stage architecture, 2‑phase commit transaction handling, deadlock detection, SQL parsing optimizations, complex query strategies, and deployment considerations for high‑availability and performance.
This article explains MySQL's Metadata Lock (MDL) subsystem, describing how MDL protects metadata, the lock types and modes, the transaction‑level implementation introduced in MySQL 5.5 and refined in 5.7, the lock acquisition and release algorithms, deadlock detection mechanisms, and the lock‑free hash table used to store lock objects.
This article details a performance comparison of MySQL 5.6.19 and Percona 5.6.25 with and without InnoDB deadlock detection, using sysbench to simulate flash‑sale stock‑decrement scenarios, and shows how turning off deadlock checks and enabling thread‑pool can boost throughput by over a hundred times.
