MySQL Lock Mechanisms: Row‑Level, Table‑Level, Page‑Level Locks and Optimistic vs Pessimistic Concurrency Control

Row‑level Lock

Row‑level lock is the finest granularity lock in MySQL, locking only the rows involved in the current operation. It greatly reduces lock conflicts but incurs the highest locking overhead. Row‑level locks include shared (read) locks and exclusive (write) locks.

Characteristics

High overhead, slower lock acquisition, possible deadlocks; smallest granularity, lowest conflict probability, highest concurrency.

Usage

Shared lock (S lock / read lock) – see illustration below:

Exclusive lock (X lock / write lock) – see illustration below:

Table‑level Lock

Table‑level lock is the coarsest granularity lock, locking the entire table. It is simple to implement, consumes fewer resources, and is supported by most MySQL engines (MyISAM, InnoDB). Table‑level locks also have shared and exclusive variants.

Characteristics

Low overhead, fast lock acquisition, no deadlocks; large granularity, high conflict probability, low concurrency.

Usage

Shared lock (S lock / read lock) – illustration:

Exclusive lock (X lock / write lock) – illustration:

Page‑level Lock

Page‑level lock sits between row‑level and table‑level locks, locking a group of adjacent records (a page). It balances lock granularity and performance; BDB uses page‑level locks by default.

Characteristics

Locking cost and time are between table and row locks; possible deadlocks; moderate concurrency.

Optimistic vs Pessimistic Concurrency Control

Both are conceptual approaches to concurrency. Pessimistic control assumes high contention and acquires locks before data access, while optimistic control assumes low contention and validates data at commit time.

Pessimistic Lock

Pessimistic concurrency control (PCC) prevents other transactions from modifying data while a transaction holds an exclusive lock. In MySQL, this usually means using the database's lock mechanisms (row, table, or page locks). The typical workflow is: attempt to acquire an exclusive lock on the target record; if acquisition fails, wait or raise an error; upon success, perform the update and release the lock when the transaction ends.

MySQL InnoDB Pessimistic Lock

To use pessimistic locks in InnoDB, disable autocommit (set autocommit=0) so that a transaction can hold locks across multiple statements.

Optimistic Lock

Optimistic concurrency control (OCC) assumes conflicts are rare. Each transaction reads data with a version identifier (e.g., a timestamp or version number). At commit, the transaction checks whether the version has changed; if it has, the transaction rolls back and informs the user.

Implementation often uses a version column that increments on each update, or a timestamp. The compare‑and‑swap (CAS) operation can enforce consistency.

MySQL Storage Engine Lock Mechanisms

MyISAM and MEMORY use table‑level locking.

BDB uses page‑level or table‑level locking (default page‑level).

InnoDB supports row‑level and table‑level locking; default is row‑level.

InnoDB Row vs Table Lock

InnoDB row locks are applied to index entries, not directly to rows. If a query does not use an index, InnoDB falls back to a table lock. Therefore, proper indexing is crucial to avoid unnecessary lock contention.

Row‑level Locks and Deadlocks

MyISAM never deadlocks because it acquires all required locks at once. InnoDB acquires locks incrementally, which can lead to deadlocks. InnoDB detects deadlocks and rolls back one of the involved transactions.

Deadlock Prevention (Three Common Strategies)

Access multiple tables in a consistent order across all programs.

Lock all required resources in a single transaction whenever possible.

For highly contended sections, consider escalating to a coarser lock granularity (e.g., table‑level lock).

-END-