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.

InnoDBLock WaitSQL

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.

Deadlock DetectionInnoDBLock Wait

0 likes · 9 min read

Understanding InnoDB Deadlock Detection and Resolution in MySQL 8.0

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.

Lock WaitSQL Optimizationmyawr

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.

InnoDBLock WaitSemi‑consistent Read

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 SchedulerInnoDBLock Wait

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 ConcurrencyInnoDBLock Wait

0 likes · 9 min read

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

Java High-Performance Architecture

Sep 1, 2016 · Databases

How to Simulate and Detect InnoDB Lock Waits in MySQL

This guide demonstrates how to simulate a blocking situation in MySQL using two client connections, set a long lock wait timeout, execute conflicting UPDATE queries, and then query the InnoDB lock wait tables to identify blocked and blocking threads along with wait duration.

InnoDBLock WaitSQL

0 likes · 3 min read

How to Simulate and Detect InnoDB Lock Waits in MySQL