Master MySQL InnoDB Locks: From Row Locks to Deadlock Prevention

Why Locks Are Needed

In everyday life you lock a door to avoid disturbance; similarly, MySQL uses locks to prevent concurrent transactions from interfering with each other and to maintain data consistency.

InnoDB Lock Types

Shared and Exclusive Locks

InnoDB implements two standard row‑level locks: shared (S) lock for reading and exclusive (X) lock for writing.

S lock: acquired when a transaction reads a record.

X lock: acquired when a transaction updates a record.

If transaction T1 holds an S lock on row R, another transaction T2 can also acquire an S lock on R, but an X lock request will be blocked.

If T1 holds an X lock on R, any S or X lock request from T2 will be blocked.

Intention Locks

Intention locks are table‑level locks that do not conflict with row‑level locks. They signal a transaction’s intention to acquire row locks later.

Intention Shared (IS) lock: set before acquiring S locks on rows.

Intention Exclusive (IX) lock: set before acquiring X locks on rows.

These locks allow the engine to quickly check for conflicting row locks without scanning every row.

Record Lock

A record lock is a simple row lock applied to a single index record, e.g., SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE when c1 is a primary key or unique index.

Gap Lock

Gap locks protect the interval between index records (or before the first/after the last) to prevent phantom reads. Example log entry: lock_mode X locks gap before rec.

Next‑Key Lock

Next‑key lock = record lock + gap lock; it locks a record and the preceding gap, forming a half‑open interval (5,10].

Insert‑Intention Lock

Set before inserting a row into a gap; it allows concurrent inserts into different gaps without blocking each other.

Auto‑Increment Lock

A special table‑level lock for AUTO_INCREMENT columns. Depending on innodb_autoinc_lock_mode (0‑traditional, 1‑consecutive, 2‑interleaved), the lock can be a lightweight mutex or a full table lock.

Deadlocks and Prevention

A deadlock occurs when two or more transactions hold locks the other needs, forming a cycle.

Prevention tips:

Access tables in a consistent order.

Keep transactions short; split large transactions.

Use lower isolation levels (RC instead of RR) when appropriate.

Optimize indexes to reduce lock contention.

Consider distributed or optimistic locks for high‑concurrency scenarios.

Optimistic vs. Pessimistic Locks

Pessimistic locking (e.g., SELECT ... FOR UPDATE) blocks other transactions from modifying the selected rows until commit.

Optimistic locking assumes conflicts are rare; it uses version numbers or timestamps and CAS to detect changes, retrying if a conflict is detected.

SELECT FOR UPDATE Locking Rules

Behavior varies by isolation level and index condition:

RC level: unique index → IX table lock + X row lock(s); primary key → IX + X on primary key; ordinary index → IX + X on matching rows, otherwise only IX.

RR level: unique index → IX + X on primary key + X on unique index; primary key → IX + X on primary key; ordinary index → IX + X on row + Gap lock; no index → IX + X on each row plus a pseudo‑record lock.

RR Isolation Level Lock Rules

Principles:

All locks are next‑key locks (record + gap).

Only objects accessed during the search are locked.

Optimizations:

Unique index equality searches downgrade next‑key to record lock.

When scanning rightward and the last value doesn’t satisfy the condition, next‑key degrades to gap lock.

Bug: Range queries on unique indexes may stop at the first non‑matching value.

InnoDB Row‑Lock Algorithms

Record Lock: locks a single index record.

Gap Lock: locks a range without the record.

Next‑Key Lock: combines record and gap lock.

If the query uses a unique index or primary key, next‑key lock becomes a record lock; otherwise a gap lock is added.

Debugging Deadlocks

Typical steps:

Run SHOW ENGINE INNODB STATUS to view the deadlock log.

Identify the conflicting SQL statements.

Analyze the lock types each statement acquires.

Reproduce the deadlock scenario.

Examine the log details and outcomes.

Images illustrating lock compatibility matrices and examples are retained for visual reference.