Understanding MySQL Locks, Isolation Levels, and Concurrency Control

In MySQL, many operations acquire locks implicitly; for example, UPDATE, DELETE, and INSERT statements obtain exclusive (X) locks, while MyISAM automatically adds read locks for SELECT and write locks for data‑modifying statements.

Locks can be classified by usage (optimistic vs. pessimistic) and granularity (table‑level, row‑level, page‑level). Table locks are cheap, fast, and never deadlock but have low concurrency, whereas row locks provide finer granularity, higher concurrency, but may cause deadlocks.

InnoDB supports both table and row locks, while MyISAM only supports table locks. InnoDB uses row locks only when the query uses an index; otherwise it falls back to a table lock.

Table locks have two modes: read lock (shared) and write lock (exclusive). In a read‑lock scenario, multiple readers do not block each other, but any writer is blocked; in a write‑lock scenario, both readers and other writers are blocked.

Row locks in InnoDB come in two types: shared (S) lock, which allows multiple transactions to read a row but prevents modifications, and exclusive (X) lock, which allows a transaction to modify a row while blocking all other reads and writes.

InnoDB also uses intention locks (IS and IX) at the table level to indicate that a transaction intends to acquire shared or exclusive row locks, respectively.

MVCC (Multi‑Version Concurrency Control) is an advanced form of row‑level locking that enables non‑blocking reads by keeping multiple versions of a row; reads see a snapshot of data without acquiring locks.

Transaction isolation levels are implemented via locks:

Read Uncommitted – allows dirty reads.

Read Committed – prevents dirty reads but allows non‑repeatable reads.

Repeatable Read – prevents non‑repeatable reads; MySQL adds gap locks to also prevent phantom reads.

Serializable – forces full serial execution, eliminating all concurrency anomalies.

Examples of anomalies:

Dirty read: Transaction A reads uncommitted data from Transaction B.

Non‑repeatable read: Transaction A reads a row, Transaction B updates it, and A reads a different value later.

Phantom read: Transaction A re‑executes a range query and sees rows inserted by Transaction B.

To avoid update‑lost problems, three approaches are common:

Use the Serializable isolation level.

Apply optimistic locking by adding a version column and updating with a condition on the version.

Apply pessimistic locking with SELECT ... FOR UPDATE to acquire an exclusive row lock before updating.

Optimistic lock example:

update A set Name='lisi', version=version+1 where ID=#{id} and version=#{version}

Pessimistic lock example: SELECT * FROM table_name WHERE ... FOR UPDATE Gap (GAP) locks are used by InnoDB when a range condition is used; the engine locks the gaps between index entries to prevent phantom inserts.

Example of a gap lock: Select * from emp where empid > 100 for update; Deadlocks occur when multiple sessions acquire locks in different orders; they are avoided by ensuring a consistent lock acquisition order, typically by sorting primary keys before locking.

Deadlock‑free lock acquisition example:

Select * from xxx where id in (xx,xx,xx) for update;

In summary, MySQL’s locking hierarchy consists of shared (S) and exclusive (X) locks, intention locks, MVCC for non‑blocking reads, and various isolation levels that dictate how locks are applied to guarantee data consistency while balancing concurrency.