MySQL InnoDB Lock Monitoring: Detecting and Killing Blocking Transactions

InnoDB row locking is usually invisible — until it causes deadlocks or long-running transactions that block other queries. Here is how to monitor and diagnose lock contention.

Detect Active Locks

-- MySQL 8.0: performance_schema.data_locks
SELECT
  ENGINE_LOCK_ID,
  ENGINE_TRANSACTION_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS
FROM performance_schema.data_locks
WHERE LOCK_STATUS = 'WAITING';

Find Blocking Transaction

SELECT
  waiting.trx_id AS waiting_id,
  waiting.trx_query AS waiting_query,
  blocking.trx_id AS blocking_id,
  blocking.trx_query AS blocking_query,
  blocking.trx_started,
  TIMESTAMPDIFF(SECOND, blocking.trx_started, now()) AS blocking_seconds
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx waiting  ON waiting.trx_id  = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx blocking ON blocking.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

Kill a Blocking Transaction

-- Find the process ID
SELECT trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE trx_id = 'blocking_trx_id_here';

-- Kill it
KILL 1234;

InnoDB Status: Lock Section

SHOW ENGINE INNODB STATUS\G
-- Look for TRANSACTIONS section:
-- '---TRANSACTION X, ACTIVE Y sec'
-- 'LOCK WAIT Z lock struct(s), heap size'
-- 'MySQL thread id M, OS thread handle N'

Prevent Lock Escalation

-- Set lock wait timeout (default 50 seconds is too long)
SET GLOBAL innodb_lock_wait_timeout = 10;

-- Detect deadlocks in application (check errno 1213)
-- Implement retry logic for deadlock errors

-- Use SELECT ... FOR UPDATE only when you will immediately update
-- Use SELECT ... FOR SHARE for read locks that do not need write exclusivity

Operational Lock-Triage Runbook

Start with evidence, not a kill command. A waiting transaction can be blocked by more than one held lock, and the session at the head of a wait chain may be idle with trx_query set to NULL. Capture the wait graph, transaction age, connection owner, current or last statement, locked object, and application request identifier before intervening. The performance_schema.data_lock_waits table exposes requesting and blocking lock IDs and transaction IDs without requiring extra lock instrumentation. On installations that include the sys schema, sys.innodb_lock_waits provides a more readable view with waiting and blocking process IDs.

Separate Row Locks from Metadata Locks

data_locks covers data locks held or requested by storage-engine transactions. An ALTER TABLE waiting behind a long transaction, or new queries piling up behind pending DDL, can instead be a metadata-lock incident. Inspect performance_schema.metadata_locks or sys.schema_table_lock_waits for that case. Also distinguish a deadlock from a long wait: with deadlock detection enabled, InnoDB chooses a victim and rolls one transaction back automatically; a persistent wait usually has a live blocker that is not part of a cycle.

SELECT waiting_pid, blocking_pid, CONCAT(locked_table_schema,'.',locked_table_name) AS locked_table, waiting_query, blocking_query, wait_age, blocking_trx_age FROM sys.innodb_lock_waits ORDER BY wait_age_secs DESC;
SELECT object_schema, object_name, lock_type, lock_status, owner_thread_id FROM performance_schema.metadata_locks WHERE lock_status = 'PENDING';

Choose the Least-Damaging Intervention

Identify the MySQL processlist ID, not an internal InnoDB transaction ID, before using KILL. KILL QUERY stops only the executing statement and leaves the connection alive; it may not release locks held by an explicit transaction. KILL CONNECTION terminates the session and causes its active transaction to roll back, which releases locks only as rollback progresses. Estimate rollback size from transaction age and rows modified, contact the workload owner when possible, and verify that the target is not a replication applier, backup, migration, or other critical system session. Never execute a generated sql_kill_blocking_connection column blindly.

Timeout and Retry Semantics

A lock wait timeout normally rolls back the current statement, not the entire transaction. The application must explicitly roll back or make a deliberate decision before reusing that connection; otherwise earlier changes and locks can remain. Set innodb_lock_wait_timeout at session scope according to the workload's latency budget rather than imposing one global value on batch and OLTP traffic. Error 1213 indicates a deadlock victim and error 1205 a lock wait timeout. Retry the whole idempotent transaction with bounded exponential backoff and jitter, not only the last SQL statement. Place a retry ceiling around the business operation so contention cannot become an infinite retry storm.

Prevent Recurrence

Use the same table and row access order across competing transactions, keep user interaction and remote API calls outside transactions, and index predicates used by UPDATE, DELETE, and locking reads so fewer records and gaps are examined. Alert on transaction age and lock-wait age as well as wait count. If deadlocks are frequent, temporarily enable innodb_print_all_deadlocks so every deadlock reaches the error log; disable it after evidence is collected if log volume becomes excessive.

Validate Recovery

After intervention, confirm the wait edge disappeared, the killed transaction finished rollback, queue latency returned to baseline, replication remains healthy, and the application either committed or retried the affected unit of work. Preserve the captured wait graph and exact SQL digest for root-cause review. A successful kill is incident containment, not resolution; the durable fix is usually transaction scoping, access-order consistency, or a supporting index.

Official MySQL References

• Performance Schema data_lock_waits
• sys.innodb_lock_waits view
• Metadata lock wait views
• KILL statement behavior
• InnoDB deadlock handling