Master MySQL Locking: From Row Locks to Deadlocks and How to Prevent Them

Why Locking Is Needed

In MySQL each transaction must isolate its changes; without locks concurrent updates could corrupt data or return inconsistent results, so rows are locked during updates to preserve consistency.

InnoDB Lock Types

Shared and Exclusive Locks

InnoDB implements row‑level shared (S) and exclusive (X) locks. A transaction holding an S lock on a row can be joined by other S locks, while an X lock blocks all other access.

If transaction T1 holds an S lock on row R, another transaction T2 can also obtain an S lock immediately.

If T1 holds an X lock on R, T2 must wait until T1 releases the lock.

Intention Locks

Intention locks are table‑level locks that indicate a transaction’s future row‑level lock intent, preventing the need to scan every row for existing locks.

Intention shared (IS) is set before acquiring row‑level S locks.

Intention exclusive (IX) is set before acquiring row‑level X locks.

Record Lock

A record lock protects a single index entry. Example: SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; If c1 is a primary key or unique index, this statement acquires a record lock on that index entry.

Gap Lock

Gap locks protect a range between index entries to prevent phantom reads. They lock the interval, not the rows inside it.

Next‑Key Lock

Next‑key lock = record lock + gap lock, locking an index record and the preceding gap (e.g., (5,10]).

Insert‑Intention Lock

Before inserting a row, InnoDB sets an insert‑intention lock on the gap where the new record will go, allowing concurrent inserts into different gaps.

Auto‑Increment Lock

When inserting into an AUTO_INCREMENT column, InnoDB acquires a special table‑level lock to guarantee consecutive primary‑key values. The lock mode depends on the innodb_autoinc_lock_mode setting (0 = traditional, 1 = consecutive, 2 = interleaved).

Deadlocks and Prevention

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

Access tables in a consistent order.

Keep transactions short; split large ones.

Use lower isolation levels (e.g., RC) when appropriate.

Design proper indexes to reduce lock contention.

Consider optimistic locking or distributed lock services for high‑contention scenarios.

Pessimistic vs. Optimistic Locks

Pessimistic locking (e.g., SELECT ... FOR UPDATE) blocks other sessions from modifying the selected rows until the transaction ends.

Optimistic locking relies on a version or timestamp column; updates succeed only if the version matches, otherwise the transaction retries.

What Does SELECT FOR UPDATE Lock?

Lock behavior varies by isolation level and index usage:

RC Isolation

Unique index hit: IX table lock + two X row locks (primary key and unique index).

Primary key hit: IX + one X row lock.

Non‑unique index hit: IX + two X row locks (index row and primary key row). If no row matches, only IX is taken.

No index: IX + X row lock on each scanned row (MySQL may release locks on rows that don’t satisfy the predicate).

RR Isolation

Unique index hit: IX + X on primary key + X on unique index.

Primary key hit: IX + X on primary key.

Non‑unique index hit: IX + X on the row + Gap lock.

No index: IX + X on each row + a pseudo‑record lock that effectively locks the whole table.

Distributed Lock Using the Database

Using SELECT ... FOR UPDATE as a simple distributed lock is easy but unsuitable for high‑concurrency workloads because it ties lock duration to the transaction and can degrade performance.

Table vs. Row vs. Page Locks

MyISAM supports only table locks.

InnoDB defaults to row locks but also supports table locks.

BDB supports table and page locks.

Table locks are cheap but coarse; row locks are fine‑grained but can deadlock; page locks sit in between.

How MySQL Adds Locks for a Single SQL

Lock acquisition depends on the index used and isolation level, resulting in nine typical scenarios (e.g., primary key with RC, unique index with RR, no index with Serializable, etc.).

Safe Concurrent Row Updates

Use pessimistic locking ( SELECT ... FOR UPDATE) or optimistic locking (version/timestamp check) to ensure only one transaction modifies a row at a time.

RR Isolation Lock Rules

Basic unit is the next‑key lock.

Only objects accessed during the search are locked.

Optimizations: unique‑index equality queries downgrade next‑key to record lock; right‑most index traversal may downgrade to gap lock.

Bug: range queries on unique indexes may lock the first non‑matching value.

InnoDB Row‑Lock Algorithms

Record Lock – locks a single index record.

Gap Lock – locks the interval between index records.

Next‑Key Lock – combination of record and gap lock.

Unique or primary‑key lookups use record locks; non‑unique indexes use next‑key (gap + record) locks.

Deadlock Troubleshooting Steps

Run SHOW ENGINE INNODB STATUS to view the deadlock log.

Identify the conflicting SQL statements.

Analyze the lock types each statement acquired.

Reproduce the deadlock in a test environment.

Examine the log details to pinpoint the cycle.

Apply fixes such as ordering accesses, reducing transaction size, or adding appropriate indexes.