How to Simulate and Detect InnoDB Lock Waits in MySQL

Database monitoring often focuses on lock‑wait situations; InnoDB is the dominant storage engine. This article walks through creating a simulated block and then querying the lock‑wait status.

Simulate Blocking State

Open two MySQL client sessions to the same server and obtain their connection IDs (e.g., client1 = 5, client2 = 6).

Increase the global lock wait timeout to make testing easier: set global innodb_lock_wait_timeout=200; In client1 start a transaction and lock rows:

begin;

select film_id from film for update;

Client1 returns data normally.

In client2 start a transaction and try to lock a different column of the same table:

begin;

select title from film for update;

Client2 does not return results and stays in a waiting state, confirming the simulated block.

Query Blocking

Run the following query against information_schema to list blocked and blocking threads and the duration of the wait:

select
  b.trx_mysql_thread_id as '被阻塞线程',
  b.trx_query as '被阻塞SQL',
  c.trx_mysql_thread_id as '阻塞线程',
  c.trx_query as '阻塞SQL',
  (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间'
from information_schema.innodb_lock_waits a
join information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id
join information_schema.innodb_trx c on a.blocking_trx_id=c.trx_id
where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) > 10;

The result shows the blocked thread, its SQL, the blocking thread, its SQL, and the wait time in seconds. The threshold value (here 10 ) can be adjusted as needed.

Thus, by simulating a lock and querying the InnoDB lock‑wait tables, you can effectively monitor and diagnose blocking issues in MySQL.