Understanding MySQL InnoDB Locks: Types, Queries, and Common Pitfalls

Querying InnoDB Locks

InnoDB lock information is stored in the performance_schema tables data_locks (current locks) and data_lock_waits (waiting relationships). Query these tables to see which locks exist and which sessions are blocked.

data_locks fields

ENGINE : storage engine, usually InnoDB

ENGINE_LOCK_ID : lock identifier

ENGINE_TRANSACTION_ID : transaction ID requesting the lock

THREAD_ID : thread ID requesting the lock (different from processlist.id)

EVENT_ID : event identifier

OBJECT_SCHEMA : schema of the table

OBJECT_NAME : table name

PARTITION_NAME : partition name (NULL for non‑partitioned tables)

SUBPARTITION_NAME : sub‑partition name (NULL for non‑partitioned tables)

INDEX_NAME : index name

OBJECT_INSTANCE_BEGIN : memory address of the lock

LOCK_TYPE : type of lock (e.g., RECORD for row lock, TABLE for table lock)

LOCK_MODE : lock mode – S (shared), X (exclusive), IS (intention shared), IX (intention exclusive), GAP (gap lock), REC_NOT_GAP (record‑only lock), INSERT_INTENTION, AUTO_INC LOCK_STATUS : GRANTED or WAITING LOCK_DATA : for record locks – primary‑key value, secondary‑index values, or the string “supremum pseudo‑record” for virtual records

data_lock_waits fields

ENGINE : storage engine, usually InnoDB

REQUESTING_ENGINE_LOCK_ID : lock ID of the waiting transaction

REQUESTING_ENGINE_TRANSACTION_ID : transaction ID of the waiting transaction

REQUESTING_THREAD_ID : thread ID of the waiting transaction (join with threads for more info)

REQUESTING_EVENT_ID : event ID of the waiting transaction

REQUESTING_OBJECT_INSTANCE_BEGIN : memory address of the waiting lock

BLOCKING_ENGINE_LOCK_ID : lock ID of the blocking transaction

BLOCKING_ENGINE_TRANSACTION_ID : transaction ID of the blocking transaction

BLOCKING_THREAD_ID : thread ID of the blocking transaction

BLOCKING_EVENT_ID : event ID of the blocking transaction

BLOCKING_OBJECT_INSTANCE_BEGIN : memory address of the blocking lock

InnoDB Lock Types

Table Locks

To obtain a real InnoDB table lock you must disable autocommit first ( SET autocommit=0) and then execute LOCK TABLES. In data_locks the LOCK_TYPE column will be TABLE and LOCK_MODE will be X (write) or S (read).

SET autocommit=0;
LOCK TABLE emp READ, tb_user WRITE;
SELECT ENGINE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE
FROM performance_schema.data_locks;

Intention Locks

Before a row lock InnoDB acquires an intention lock on the table: IS for a shared row lock, IX for an exclusive row lock. In data_locks these appear as LOCK_TYPE = TABLE with LOCK_MODE = IS or IX.

Record (Row) Locks

Row locks are created only for INSERT, UPDATE, DELETE, SELECT … FOR UPDATE, or SELECT … FOR SHARE. The LOCK_TYPE is RECORD. Possible LOCK_MODE values are: X – exclusive lock S – shared lock X,REC_NOT_GAP – exclusive lock on the record only (no gap) S,REC_NOT_GAP – shared lock on the record only INSERT_INTENTION – lock used during insert AUTO_INC – auto‑increment ID lock

Example under READ COMMITTED isolation:

SET autocommit=0;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM passjava_test_lock WHERE b='b20' FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_NAME;

The result shows LOCK_MODE = X,REC_NOT_GAP for the matching primary‑key and index record, indicating that only the record itself is locked.

Isolation Level Effects

READ COMMITTED locks only the matching record; gaps are not locked.

REPEATABLE READ locks the matching record and the gap before it when the index lookup does not find a matching row (to prevent phantom reads). The LOCK_MODE will contain GAP (e.g., X,GAP).

Example where a unique index is used and the query matches a row (hit):

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM passjava_test_lock WHERE a='a20' AND c='c20' FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_NAME;
COMMIT;

The lock mode is X,REC_NOT_GAP because the unique index precisely identifies the row.

Example where the unique index does not match (miss):

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM passjava_test_lock WHERE a='a15' AND c='c15' FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_NAME;
COMMIT;

Because no row exists, InnoDB locks the gap before the next record. The LOCK_MODE is X,GAP and LOCK_DATA shows the right‑hand record (e.g., ('a20','c20','pk20')), which represents the gap.

GAP Locks

GAP locks protect the interval between two adjacent index records. In data_locks they appear with LOCK_MODE = X,GAP or S,GAP. The LOCK_DATA column stores the right‑hand record of the interval because MySQL needs a concrete row to represent the gap.

Example:

SELECT * FROM test_lock WHERE b='b15' FOR UPDATE;

Even though b='b15' does not exist, InnoDB creates a GAP lock on the interval between b='b10' and b='b20'. The lock entry shows LOCK_MODE = X,GAP and LOCK_DATA = ('b20','pk20'), indicating that the gap before b='b20' is locked.

Next‑Key Locks

A next‑key lock is the combination of a record lock and the preceding GAP lock. In data_locks it is shown as LOCK_TYPE = RECORD and LOCK_MODE = X (or S) without an explicit GAP suffix, because the X (or S) mode implicitly includes the gap.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM passjava_test_lock WHERE b='b20' FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_NAME, INDEX_NAME;
COMMIT;

The entry for the matching row shows LOCK_MODE = X (next‑key lock) and LOCK_DATA = ('b20','pk20'). Another entry on the same index shows LOCK_MODE = X,GAP with LOCK_DATA = ('b30','pk30'), representing the gap after the locked row.

Lock Compatibility Matrix

X conflicts with X, IX, S, IS

IX conflicts with X and S, but is compatible with IX and IS

S conflicts with X and IX, but is compatible with S and IS

IS conflicts with X, but is compatible with IX, S, and IS

Exclusive locks (X) conflict with all other modes; intention locks (IX, IS) are compatible with each other; shared locks (S) are compatible only with other shared or intention‑shared locks.

Auto‑Increment ID Lock

When a statement inserts a row that uses an AUTO_INCREMENT column, InnoDB acquires an auto‑increment lock. The behavior is controlled by the innodb_autoinc_lock_mode system variable.

Summary of Lock Compatibility

Table locks (X) block any other lock on the same table.

Intention locks (IX, IS) coexist with each other and with row locks.

GAP locks are fully compatible; multiple sessions can hold GAP locks on the same interval.

Next‑key locks combine a record lock with a GAP lock; their GAP part follows the GAP‑lock compatibility rules.

Row locks follow the classic shared/exclusive rule: many S locks can coexist, but a single X lock excludes all others.