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

MySQL provides three lock granularities—row‑level, page‑level and table‑level—each of which can be used in shared (read) or exclusive (write) modes.

Row‑Level Lock

Row‑level locks are the finest granularity, locking only the rows involved in a transaction. They greatly reduce lock conflicts but incur the highest overhead. Row‑level locks include shared (S) locks for reads and exclusive (X) locks for writes.

Table‑Level Lock

Table‑level locks lock the entire table, offering low overhead and fast acquisition but causing the highest probability of lock conflicts and the lowest concurrency. Both MyISAM and InnoDB support table‑level locks, which also have shared and exclusive variants.

Page‑Level Lock

Page‑level locks sit between row‑ and table‑level locks, locking a group of adjacent records (a page). They balance lock overhead and conflict frequency; BDB supports page‑level locks.

Optimistic vs Pessimistic Concurrency Control

Both are conceptual strategies for handling concurrent transactions. Pessimistic control acquires locks before accessing data, preventing conflicts at the cost of higher overhead and possible deadlocks. Optimistic control assumes conflicts are rare, checking for conflicts only at commit time, often using version numbers or timestamps.

Pessimistic Lock

In relational databases, a pessimistic lock (PCC) blocks other transactions from modifying a row until the lock is released. The typical workflow is: attempt to acquire an exclusive lock on a record; if acquisition fails, wait or raise an error; upon success, modify the record and release the lock after the transaction commits.

MySQL InnoDB Pessimistic Lock Usage

To use pessimistic locks in InnoDB, disable autocommit (set autocommit=0) so that updates are not committed immediately, allowing explicit lock control.

Optimistic Lock

Optimistic concurrency control (OCC) assumes data conflicts are infrequent. It typically uses a version number or timestamp stored with each row; the version is incremented on each update. At commit, the current version is compared with the original; if they differ, the transaction rolls back.

Lock Mechanisms of Common MySQL Storage Engines

MyISAM and MEMORY use table‑level locking. BDB defaults to page‑level locking (or table‑level). InnoDB supports row‑level and table‑level locks, defaulting to row‑level.

InnoDB Row vs Table Lock

InnoDB implements row locks by locking index entries, not the physical rows. Consequently, only queries that use an index trigger row‑level locks; otherwise, InnoDB falls back to table‑level locks.

Key points:

When a query does not use an index, InnoDB uses a table lock.

Locks are applied to index entries, so different transactions using the same index key can conflict even if they access different rows.

Multiple indexes allow different transactions to lock different rows via different indexes.

The optimizer may choose a full table scan over an index scan; in that case, a table lock is used.

Row‑Level Locks and Deadlocks

MyISAM never deadlocks because it acquires all required locks at once. InnoDB acquires locks incrementally, making deadlocks possible. InnoDB detects deadlocks and rolls back one transaction.

Deadlock avoidance tips (three common methods):

Access multiple tables in a consistent order across all concurrent programs.

Acquire all needed resources in a single transaction whenever possible.

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

These guidelines help maintain high concurrency while preventing lock‑related performance issues.