lock wait | BestHub

Aikesheng Open Source Community

Apr 16, 2025 · Databases

Troubleshooting MySQL Transaction Lock Waits: Diagnosis and Resolution

This article explains how to reproduce a MySQL InnoDB lock‑wait scenario, use system tables such as information_schema.processlist, innodb_trx, sys.innodb_lock_waits and performance_schema to locate the blocking transaction, and finally resolve the issue by killing the offending session, while also providing quick‑check queries and parameter tuning advice.

Database TroubleshootingInnoDBMySQL

0 likes · 9 min read

Troubleshooting MySQL Transaction Lock Waits: Diagnosis and Resolution

Aikesheng Open Source Community

Jul 24, 2024 · Databases

Understanding InnoDB Deadlock Detection and Resolution in MySQL 8.0

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.

InnoDBMySQLdeadlock detection

0 likes · 9 min read

Understanding InnoDB Deadlock Detection and Resolution in MySQL 8.0

Aikesheng Open Source Community

Jul 17, 2024 · Databases

Understanding InnoDB Deadlock Detection Thread and Lock‑Wait Snapshot Construction

This article explains how InnoDB simulates deadlocks, how the ib_srv_lock_to background thread checks for deadlocks, how it builds lock‑wait snapshots, constructs lock‑wait graphs, calculates transaction weights, and finally resolves deadlocks, illustrated with detailed SQL examples and code snippets.

Database InternalsInnoDBMySQL

0 likes · 14 min read

Understanding InnoDB Deadlock Detection Thread and Lock‑Wait Snapshot Construction

Efficient Ops

Dec 12, 2023 · Databases

How to Diagnose MySQL Lock Waits and Transaction Timeouts with MyAWR

This article explains how commercial banks can analyze MySQL lock‑wait events and transaction timeouts using MDL metadata lock monitoring, innodb_lock_wait_timeout, custom MyAWR collection, SQL de‑parameterization, and post‑mortem queries to pinpoint blocking SQL and its source.

MyAWRMySQLPerformance analysis

0 likes · 19 min read

How to Diagnose MySQL Lock Waits and Transaction Timeouts with MyAWR

Tencent Database Technology

Oct 27, 2022 · Databases

Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads

The article explains why a seemingly non‑conflicting UPDATE on a secondary index can cause lock‑wait alerts under RC isolation, detailing the missing end‑range condition push‑down, the InnoDB row‑search flow, semi‑consistent read behavior, and differences between primary‑key and secondary‑index locking.

Database InternalsIndex Condition PushdownInnoDB

0 likes · 12 min read

Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads

Aikesheng Open Source Community

Feb 21, 2021 · Databases

Using MySQL Events and Stored Procedures to Monitor InnoDB Lock Waits

This article explains how to replace shell‑script based lock‑wait monitoring with MySQL Event and Stored Procedure mechanisms, showing step‑by‑step creation of a monitoring database, procedure, scheduled event, and how to interpret the generated lock‑wait log table for troubleshooting.

Event SchedulerInnoDBMySQL

0 likes · 6 min read

Using MySQL Events and Stored Procedures to Monitor InnoDB Lock Waits

NetEase Game Operations Platform

Oct 12, 2019 · Databases

Understanding MySQL 8.0.1 NOWAIT and SKIP LOCKED Features for Reducing Lock Wait

This article explains the new NOWAIT and SKIP LOCKED keywords introduced in MySQL 8.0.1, describes their background, functionality, and internal implementation, and demonstrates their practical impact on lock‑wait scenarios through a fictional flash‑sale case study with code examples.

Database ConcurrencyInnoDBMySQL

0 likes · 9 min read

Understanding MySQL 8.0.1 NOWAIT and SKIP LOCKED Features for Reducing Lock Wait