Understanding InnoDB Locks: From Record Locks to Deadlocks

1. Basic Lock Concepts

Database transactions must satisfy the ACID properties (Atomicity, Consistency, Isolation, Durability). MySQL provides four isolation levels: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ (default), and SERIALIZABLE. The default level is REPEATABLE-READ, which uses next‑key locks to prevent phantom rows.

2. InnoDB Lock Types

2.1 Shared (S) and Exclusive (X) Locks

InnoDB implements two standard row‑level locks: shared (S) locks allow multiple transactions to read a row, while exclusive (X) locks allow a transaction to update or delete the row.

S lock: If transaction T1 holds an S lock on row r, other transactions may also hold S locks on r but cannot acquire an X lock.

X lock: If T1 holds an X lock on r, no other transaction can obtain an S or X lock on r until T1 releases it.

2.2 Intention Locks

Intention locks are table‑level locks that indicate a transaction’s intention to acquire row‑level locks. They come in two forms: IS (Intention Shared) – set before acquiring an S lock on a row. IX (Intention Exclusive) – set before acquiring an X lock on a row.

Before a transaction can obtain a row‑level S or X lock, it must first acquire the corresponding IS or IX lock on the table.

2.3 Lock Compatibility Matrix

The following matrix (image) shows which lock types are compatible. If two locks are compatible, the second transaction can acquire its lock without waiting; otherwise it must wait for the first lock to be released.

2.4 Gap Locks and Next‑Key Locks

Gap locks protect the gaps between index records (or before the first and after the last record) and exist only in certain isolation levels. A next‑key lock is the combination of a record lock and the surrounding gap lock. In REPEATABLE-READ, InnoDB uses next‑key locks for non‑unique index scans to prevent phantom rows.

3. Demonstrating Locks with a Test Table

The example table test has two columns: id (primary key) and code (non‑unique index). It is pre‑populated with rows (1,1) and (10,10).

To observe lock behavior, multiple transactions are opened. The first transaction acquires a lock (e.g., SELECT id FROM test WHERE id=10 FOR UPDATE) and is left open without commit. A second transaction then attempts an UPDATE or INSERT that conflicts with the held lock, causing the second transaction to wait. Engine status is inspected with: SHOW ENGINE INNODB STATUS; Snapshots of the engine status illustrate X‑record locks, gap locks, and waiting states.

4. Phantom Reads, Lost Updates, and Locking Strategies

4.1 Phantom Reads

A phantom read occurs when the same SELECT statement returns different result sets in the same transaction because another transaction inserted a new row that matches the query condition. In REPEATABLE-READ, next‑key locks prevent phantom rows; in READ-COMMITTED, phantom reads can appear.

4.2 Lost Updates

When two concurrent transactions update the same row without proper coordination, the later commit can overwrite the earlier one. This “lost update” problem can be mitigated with optimistic locking (using a version column in the WHERE clause) or pessimistic locking (SELECT … FOR UPDATE before the UPDATE).

4.3 Optimistic vs. Pessimistic Locking

Optimistic lock: Add a version check in the UPDATE’s WHERE clause; if the version has changed, the update fails.

Pessimistic lock: Acquire an X lock with SELECT ... FOR UPDATE before performing the UPDATE, ensuring no other transaction can modify the row.

5. Common Deadlock Scenarios

5.1 Duplicate‑Key‑Induced Deadlocks

When multiple transactions insert rows that conflict on a unique key, InnoDB may detect a deadlock. Two patterns are described:

Rollback‑triggered: Transaction A inserts a row and later rolls back; Transactions B and C, which attempted the same insert, acquire S locks during duplicate‑key checking, then each tries to acquire an X lock, leading to a circular wait.

Commit‑triggered: Similar to the rollback case but the deadlock occurs at commit time.

5.2 Gap‑Lock vs. Insert‑Intention‑Lock Deadlock

Transaction A holds an X‑mode gap lock on a range; Transaction B attempts an insert and must acquire an insert‑intention lock ( IX). Because IX is incompatible with the existing X gap lock, both transactions wait on each other, forming a deadlock.

5.3 Update‑Insert Deadlock

When one transaction updates a row (acquiring X and next‑key locks) while another transaction attempts to insert into the same index range, the insert‑intention lock conflicts with the gap lock held by the updater, causing a deadlock.

6. Summary

InnoDB’s locking system combines row‑level, gap, and intention locks to enforce ACID guarantees. Understanding lock compatibility, isolation‑level effects, and the interplay between different lock types is essential for avoiding phantom reads, lost updates, and deadlocks. Proper use of optimistic or pessimistic locking strategies can further safeguard data integrity in high‑concurrency environments.