MySQL InnoDB row lock contention: finding who blocks whom

Queries that normally finish in milliseconds take seconds. Threads_running climbs while Questions stalls. SHOW PROCESSLIST shows active threads, yet the database is frozen. That is the shape of InnoDB row lock contention: transactions are waiting to release row-level locks, and the queue is growing.

Row lock contention differs from a metadata lock cascade. Metadata locks block DDL and DML at the table level and live in performance_schema.metadata_locks. Row locks are held by open InnoDB transactions and block at the row, gap, or next-key level. Rising Innodb_row_lock_current_waits alongside rising Threads_running signals a live contention crisis. The goal is to identify the blocker, the waiter, and the lock footprint so you can break the chain without guessing.

What this means

InnoDB holds row-level locks until commit or rollback. When a transaction needs a lock held by another, it joins a FIFO wait queue. The wait ends when the blocker releases the lock, innodb_lock_wait_timeout expires (default 50 seconds), or InnoDB detects a deadlock and rolls back the cheaper transaction.

Status metrics tell you the severity:

• Innodb_row_lock_waits: cumulative wait events.
• Innodb_row_lock_current_waits: instantaneous gauge of current waiters.
• Innodb_row_lock_time: cumulative milliseconds spent waiting.

A brief spike in waits with low average time is normal in busy OLTP. Sustained Innodb_row_lock_current_waits > 0 combined with an average wait time climbing above 1000 ms and rising Threads_running means contention is throttling the system. Compute the average from Innodb_row_lock_time / Innodb_row_lock_waits. Note that Innodb_row_lock_current_waits has a documented bug (MySQL Bug #94775) where it can report nonzero while no real waits exist; always confirm with performance_schema.data_lock_waits.

Lock waits are not deadlocks. Deadlocks are detected immediately and one transaction is rolled back. Lock waits persist until the blocker finishes. The blocking transaction may be idle with trx_query IS NULL and still hold every lock it acquired.

Common causes

Quick checks

Run these safe, read-only checks before changing anything.

-- Check row lock wait severity
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

-- Confirm real waiters exist (MySQL 8.0+)
SELECT COUNT(*) FROM performance_schema.data_lock_waits;

-- Convenience blocking tree (MySQL 8.0+)
SELECT * FROM sys.innodb_lock_waits\G

-- Find the oldest active transactions
SELECT trx_mysql_thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       trx_rows_modified, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC LIMIT 10;

-- Rule out metadata lock cascade
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
       LOCK_TYPE, LOCK_STATUS, COUNT(*) AS waiting
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING'
GROUP BY OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
         LOCK_TYPE, LOCK_STATUS;

-- Current processlist for context
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC LIMIT 20;

On MySQL 8.0, accessing performance_schema.data_locks and data_lock_waits requires only SELECT on performance_schema.*. This is stricter than the legacy MySQL 5.7 INFORMATION_SCHEMA.INNODB_LOCK_WAITS, which required the global PROCESS privilege. If you migrated scripts from 5.7, update grants.

Caution: sys.innodb_lock_waits is convenient but can stall or consume excessive memory under heavy lock volume on some MySQL 8.0 releases. If the view is slow, fall back to the manual data_lock_waits join.

How to diagnose it

1. Confirm the pattern from status metrics. Look for Innodb_row_lock_current_waits > 0, average wait time above 1000 ms, rising Threads_running, and a dropping Questions rate. That combination is the operational signature of an active lock-wait cascade. Compute the average wait time from Innodb_row_lock_time / Innodb_row_lock_waits.

2. Rule out metadata locks. Check performance_schema.metadata_locks for pending locks. If you see multiple sessions waiting on the same table with LOCK_STATUS = 'PENDING', you are dealing with a metadata lock cascade, not a row lock problem. Row-lock diagnostics will not help there.

3. Build the blocking tree. In MySQL 8.0+, join performance_schema.data_lock_waits to information_schema.innodb_trx twice: once for the blocker and once for the waiter.

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

In MySQL 5.7, use information_schema.INNODB_LOCK_WAITS and INNODB_LOCKS instead. Those tables were removed in MySQL 8.0.

4. Recover the blocking query when the session is idle. If blocking_query is NULL, the blocker is not running a statement but still holds locks. Use the blocking thread ID to find its last SQL:

SELECT t.THREAD_ID, s.SQL_TEXT
FROM performance_schema.threads t
LEFT JOIN performance_schema.events_statements_current s
  ON s.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID = <blocking_thread_id>;

-- If current is empty, try history:
SELECT THREAD_ID, SQL_TEXT, TIMER_START
FROM performance_schema.events_statements_history
WHERE THREAD_ID = <blocking_thread_id>
ORDER BY TIMER_START DESC LIMIT 5;

5. Inspect the lock footprint. Query performance_schema.data_locks to see whether the blocker holds row locks, gap locks, next-key locks, or table-level AUTO_INC locks. A single UPDATE that matches ten rows but holds thousands of next-key locks tells you the query is scanning a wide range because of a missing index or REPEATABLE READ isolation.

6. Decide on the intervention. The safest options, ordered from least to most disruptive:

   • Kill the blocking connection if it is idle or expendable. InnoDB rolls back its transaction and releases all locks. Waiters proceed immediately.
   • Kill a specific waiter if it is less important than the blocker.
   • Fix the root cause: add an index, reduce transaction scope, or change application lock ordering.

Warning: Killing a connection triggers a full transaction rollback. If the transaction modified a large number of rows, the rollback can be slow and resource-intensive.

flowchart TD
    A[Innodb_row_lock_current_waits > 0] --> B{Metadata locks pending?}
    B -->|Yes| C[Diagnose metadata lock cascade]
    B -->|No| D[Join data_lock_waits + innodb_trx]
    D --> E{blocking_query is NULL?}
    E -->|Yes| F[Lookup events_statements_history]
    E -->|No| G[Read blocking SQL]
    F --> H[Kill blocker, kill waiter, or fix query]
    G --> H

Metrics and signals to monitor

Do not rely on Innodb_row_lock_current_waits alone. Because of Bug #94775 it can lie. Always pair it with a direct query against performance_schema.data_lock_waits.

Fixes

Long-running idle transaction. Find the oldest transaction in INNODB_TRX and identify it in SHOW PROCESSLIST. If it is a developer session, monitoring connection, or leaked application connection, kill it:

Warning: KILL triggers a full transaction rollback. A large uncommitted write set can make rollback slow and I/O intensive.

KILL <trx_mysql_thread_id>;

The locks release when rollback completes and the wait queue drains. Long term, fix the application so it commits or rolls back promptly, and ensure connection pools return connections in a clean state.

Hot row contention. Reduce the time any transaction holds the hot rows. Options include batching updates to a counter table, splitting a single hot row into many rows, or moving the sequence/counter logic out of the database. Keep transactions short and do not hold locks while waiting on external services.

Missing index causing wide locks. Run EXPLAIN on the blocking query. If it scans a large range, add a covering index so InnoDB touches only the rows that must change. If the workload allows, switching from REPEATABLE READ to READ COMMITTED reduces gap locking, but it also changes MVCC semantics. Treat that as an application design decision, not a quick fix.

Foreign key and AUTO_INC contention. Ensure foreign key columns are indexed; InnoDB must lock the parent or child table for unindexed foreign key checks. For AUTO_INC contention on high-concurrency inserts, consider spreading inserts across time or partitioning the workload so the table-level AUTO_INC lock is not the bottleneck.

Prevention

• Alert on open transaction age from information_schema.INNODB_TRX. Any transaction older than 5 minutes in an OLTP workload is worth investigating.
• Keep long_query_time at 1 second or lower in production so queries that hold locks for a long time show up in the slow log.
• Maintain indexes on join columns, foreign keys, and range-filtered columns to avoid unintended gap locks.
• Schedule DDL during low-traffic windows or use online schema change tools to avoid compounding row waits with metadata locks.
• Review ORM and connection pool settings so transactions are not left open across user think time or external API calls.
• Stay on MySQL 8.0.40 or newer if you rely on sys.innodb_lock_waits for lock diagnostics.

How Netdata helps

• Charts Innodb_row_lock_current_waits and row lock wait rates alongside Threads_running, Questions, and Slow_queries on the same timeline, so you can distinguish a lock-wait cascade from CPU saturation or I/O stall.
• Alerts on sustained Innodb_row_lock_current_waits and high average wait time.
• Surfaces processlist and InnoDB status metrics without requiring manual SHOW GLOBAL STATUS sampling during an incident.

Related guides

• How MySQL actually works in production: a mental model for operators
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis
• MySQL connection exhaustion: detection, diagnosis, and prevention
• MySQL: got a packet bigger than ‘max_allowed_packet’ bytes – causes and fixes
• MySQL monitoring checklist: the signals every production instance needs
• MySQL monitoring maturity model: from survival to expert
• MySQL Threads_created climbing: thread cache churn and missing pooling
• MySQL Threads_connected vs Threads_running: which one to actually alert on
• MySQL ERROR 1040 (HY000): Too many connections - causes and fixes