Visualizing and Managing InnoDB Row Locks and Record Locks
InnoDB row locks and record locks are the primary mechanism for ensuring transaction isolation in MySQL, and understanding them in detail is essential for diagnosing both deadlocks and lock wait timeouts. InnoDB never locks entire tables for DML (unlike MyISAM) — instead, it acquires locks on specific index records, with the lock granularity determined by the query predicate and the isolation level. Record locks appear in the LATEST DETECTED DEADLOCK and TRANSACTIONS sections of SHOW ENGINE INNODB STATUS with detailed information about the locked index, the physical page, and the lock mode. This guide provides a complete reference for InnoDB lock modes, explains how to read the RECORD LOCKS output in detail, and shows how to use the performance_schema.data_locks table in MySQL 8.0 for real-time lock visibility.
InnoDB Lock Mode Reference
S, X, IS, IX: Shared vs Exclusive Lock Compatibility Matrix
InnoDB uses four lock modes organised in a two-level hierarchy: IS (intention shared), IX (intention exclusive), S (shared), and X (exclusive). Intention locks (IS and IX) are table-level placeholders that indicate a transaction intends to acquire row-level S or X locks within the table — they are automatically acquired by InnoDB before taking any row locks and allow fast table-level lock compatibility checks. S locks allow concurrent readers (multiple transactions can hold S on the same row), while X locks require exclusive access (blocking all other S and X lock requests). The compatibility matrix is: IS/IS compatible, IS/IX compatible, IS/S compatible, IX/IX compatible, but IS/X, IX/S, IX/X, S/X, and X/X are incompatible. Understanding this matrix explains why two concurrent UPDATE statements deadlock when each acquires an IX intention lock and then tries to escalate to X on the same row.
Gap Lock, Insert Intention Lock, and Next-Key Lock
Beyond record locks (S and X on existing rows), InnoDB uses gap locks, insert intention locks, and next-key locks. A gap lock locks the space between two adjacent index values, preventing any other transaction from inserting into that gap — crucial for REPEATABLE READ phantom prevention. Gap locks are compatible with each other (two transactions can both hold gap locks on the same range). An insert intention lock is a special gap lock taken by INSERT before inserting into a gap — it is incompatible with existing gap locks on the same range, causing INSERT statements to wait behind transactions holding gap locks. A next-key lock combines a record lock on an index entry with a gap lock on the interval immediately preceding it. Under READ COMMITTED isolation, gap locks and next-key locks are not used — only plain record locks, which reduces deadlock frequency significantly.
Reading Record Lock Details in InnoDB Status
Record Lock on Index, Space, Page, and Heap Number
A RECORD LOCKS line in SHOW ENGINE INNODB STATUS appears as: "RECORD LOCKS space id N page no M n bits K index `idx_name` of table `db`.`table` trx id P lock mode X [GAP]". The space id identifies the tablespace file (ibdata1 = space 0, individual .ibd files start at space 1). The page no identifies the B-tree page within the tablespace. The n bits value is the size of the lock bitmap on that page — a bitmap with one bit per record position. The index name identifies which index the lock is on: a lock on the clustered index (PRIMARY) is the actual row lock, while a lock on a secondary index may trigger an additional lock on the corresponding clustered index record. The lock mode X GAP indicates an exclusive gap lock; X NOT GAP indicates a plain record lock without a gap component.
Physical Record Field Values and Their Debugging Use
The physical record data printed below each RECORD LOCKS header provides the actual index key values of the locked rows, enabling correlation between InnoDB lock output and application data. Each field is printed as a hex string followed by a SQL-readable representation where possible. For integer primary keys, the value is a 4- or 8-byte big-endian integer: "0x0000000000000001" represents primary key value 1. For VARCHAR fields, the format is a 2-byte length prefix followed by the UTF-8 encoded bytes. A "supremum pseudo-record" lock entry (heap no 1) indicates a gap lock at the upper end of the index — no physical row exists there, but the lock prevents insertions beyond the last actual row. These physical record values are the most reliable way to identify which specific data rows are involved in a deadlock or lock wait.
Querying the Lock Subsystem Directly
performance_schema.data_locks Table in MySQL 8.0
MySQL 8.0 introduced performance_schema.data_locks and performance_schema.data_lock_waits as real-time, structured replacements for the lock information previously only available in SHOW ENGINE INNODB STATUS text output. data_locks contains one row per currently held or requested lock, with columns ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE (TABLE or RECORD), LOCK_MODE (S, X, IS, IX with optional GAP/REC NOT GAP qualifiers), LOCK_STATUS (GRANTED or WAITING), and LOCK_DATA (the actual key value being locked, as a readable string). This structured format makes it trivial to identify all locks held by a specific transaction: SELECT * FROM performance_schema.data_locks WHERE ENGINE_TRANSACTION_ID = N ORDER BY LOCK_TYPE, LOCK_MODE.
sys.innodb_lock_waits View for Rapid Diagnosis
The sys.innodb_lock_waits view (available since MySQL 5.7.9) provides a ready-made diagnostic query that joins performance_schema.data_lock_waits, performance_schema.data_locks, and information_schema.INNODB_TRX to produce a human-readable lock wait chain report. SELECT wait_age_secs, locked_table_schema, locked_table_name, locked_index, wait_query, blocking_query, blocking_trx_age FROM sys.innodb_lock_waits ORDER BY wait_age_secs DESC LIMIT 10 returns the current lock wait situation with blocking queries and their ages in a single query. For incident response, this query should be the first thing executed when investigating a lock wait timeout — it identifies the blocker, the blocked query, and the lock location within seconds, eliminating the need to manually parse SHOW ENGINE INNODB STATUS text.