Understanding MySQL Locks: From MVCC to Deadlock Prevention

Why Locks Are Needed

Concurrent transactions without constraints cause dirty writes, dirty reads, non‑repeatable reads, and phantom reads. MVCC solves snapshot‑read issues, but current reads (e.g., SELECT ... FOR UPDATE, UPDATE, DELETE, INSERT) must use locks to ensure the latest data and prevent simultaneous modifications.

Lock Classification Overview

MySQL locks can be viewed from three intersecting dimensions:

Granularity : global, table, or row level.

Mode : shared (S) vs exclusive (X) locks.

Design : pessimistic vs optimistic.

These dimensions combine; for example, a row‑level exclusive pessimistic lock is implemented by SELECT ... FOR UPDATE.

Global and Table Locks

Global lock ( FLUSH TABLES WITH READ LOCK) makes the whole database read‑only, mainly for full‑database logical backups. It blocks all writes and is costly, so MVCC‑based snapshot backups are preferred for InnoDB.

Table‑level locks include:

S/X table locks via LOCK TABLES ... READ / WRITE (rarely used by InnoDB for ordinary DML).

Metadata locks (MDL) that protect schema changes; a read MDL is taken for SELECT/INSERT/UPDATE/DELETE, and a write MDL for ALTER/DROP TABLE.

Intention locks (IS/IX) that signal a transaction will acquire row locks, allowing quick compatibility checks.

AUTO‑INC locks that guarantee sequential auto‑increment values; their behavior is controlled by innodb_autoinc_lock_mode.

Table‑level locks are fast to acquire but limit concurrency, making them suitable for low‑concurrency scenarios or engines without row locks.

Intention Locks

Intention locks solve the problem of a transaction needing a table‑level X lock while other transactions hold row‑level X locks. By placing an IS or IX lock on the table before acquiring row locks, InnoDB can determine conflict with a single check instead of scanning every row.

Row‑Level Locks (InnoDB’s Core)

Row‑level locking consists of several lock types:

Record lock : locks a specific existing row, e.g., SELECT * FROM users WHERE id=10 FOR UPDATE;.

Gap lock : locks the interval between rows, preventing inserts into that gap. Example: querying a non‑existent id=12 locks the gap (10,15).

Next‑key lock : a combination of a record lock and the preceding gap lock, forming a left‑open, right‑closed interval (e.g., (10,15] for id=15).

Insert intention lock : a special gap lock indicating an intent to insert into a gap; compatible with other insert intention locks but blocked by existing gap locks.

Implicit lock : uses the hidden trx_id column of a newly inserted row as a lightweight lock until the transaction commits.

Gap and next‑key locks are the reason REPEATABLE READ in MySQL can prevent phantom reads: they lock both the matching rows and the surrounding gaps, blocking concurrent inserts that would otherwise create phantom rows.

Pessimistic vs Optimistic Locks

All S/X locks are pessimistic: they assume conflicts will happen and acquire the lock before the operation, incurring lock contention.

Optimistic locking avoids database locks entirely, detecting conflicts at commit time, typically via a version column. Example:

SELECT balance, version FROM account WHERE id=1;  -- version = 7
UPDATE account SET balance=balance-10, version=version+1
WHERE id=1 AND version=7;

If the version has changed, the UPDATE affects zero rows, signalling a conflict.

Guideline: use pessimistic locks for write‑heavy, high‑conflict workloads; use optimistic locks for read‑heavy, low‑conflict scenarios.

Lock Selection Guide

Map lock types to scenarios:

Full‑database backup → global lock (or MVCC snapshot).

Bulk updates on MyISAM → table‑level write lock.

High‑concurrency transfers → row‑level exclusive lock.

Counter updates with many reads → optimistic lock.

Decision flow (simplified):

Need to lock whole database?
  └─Yes → Global lock
  └─No → Lock whole table?
        └─Yes → Table lock
        └─No → Row lock
               └─High write concurrency? → Pessimistic
               └─Low write concurrency? → Optimistic

Principle: finer granularity and shorter hold time yield higher concurrency.

Deadlocks

Row‑level locks increase concurrency but can cause deadlocks—circular wait chains where each transaction holds a lock the other needs.

Four necessary conditions: mutual exclusion, hold‑and‑wait, no preemption, circular wait.

Typical deadlock patterns include inconsistent operation order, missing indexes causing lock escalation, gap‑lock conflicts, and unique‑key collisions.

InnoDB detects deadlocks by building a lock‑wait graph and performing a depth‑first search; it also respects innodb_lock_wait_timeout (default 50 s).

When a deadlock is found, InnoDB automatically rolls back the “cheapest” victim based on data‑changed size, transaction age, undo‑log size, and read‑only status.

Deadlock Diagnosis and Prevention

Post‑mortem analysis uses SHOW ENGINE INNODB STATUS to view the latest deadlock details.

Prevention tips:

Design proper indexes to avoid full‑table scans and lock escalation.

Enforce a consistent access order (e.g., ascending primary‑key order).

Keep transactions short; split large transactions.

Use READ COMMITTED when business permits, eliminating gap locks.

Set a reasonable innodb_lock_wait_timeout.

Prefer optimistic locking for read‑heavy workloads.

Best Practices

Prefer row‑level locks (InnoDB) over table‑level locks.

Avoid long‑running transactions; commit quickly.

Separate reads to replicas to reduce lock contention.

Use MVCC snapshot reads whenever possible; only use FOR UPDATE for true current‑read needs.

Combined, redo logs guarantee durability, undo logs enable rollback, MVCC provides non‑blocking reads, locks ensure write and current‑read correctness, and deadlock handling resolves circular waits, together delivering InnoDB’s ACID guarantees.