Understanding MySQL Locks: Table, Row, and Page Locking Mechanisms

1. Why Does a Database Need Locks?

Locks ensure data consistency; MySQL supports table‑level, row‑level, and page‑level locking across its various storage engines.

2. Table‑Level Locks

Characteristics

• Large granularity, high lock‑conflict probability, low concurrency. • Advantages: no deadlocks, low overhead, fast acquisition/release. • Used by non‑transactional engines such as MyISAM, MEMORY, CSV, suitable for read‑heavy, write‑light workloads.

3. Row‑Level Locks

Advantages & Disadvantages

• Fine granularity, low conflict probability, high concurrency. • Drawbacks: higher overhead, slower acquisition, can cause deadlocks.

InnoDB Row‑Lock Types

• Shared lock (S) : multiple transactions can read the same row but cannot modify it. • Exclusive lock (X) : only the locking transaction can read and modify the row. • Intention locks : automatically added by InnoDB; they do not conflict with row‑level shared or exclusive locks.

Usage Scenarios

• Shared lock: verify a row’s existence while preventing updates/deletes. • Exclusive lock: needed when a transaction intends to update a row.

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

SELECT * FROM table_name WHERE ... FOR UPDATE;

Important Notes

• InnoDB acquires exclusive locks automatically for UPDATE, DELETE, INSERT statements. • SELECT statements without FOR UPDATE/LOCK IN SHARE MODE do not acquire row locks.

4. Page‑Level Locks

Page locks sit between row and table locks, can cause deadlocks, and are used by engines like BDB.

5. Common Lock‑Related Questions

When does InnoDB lock an entire table vs. a single row?

If a query lacks an index, InnoDB falls back to a table lock; otherwise it uses row locks.

When to use row‑level vs. table‑level locks?

Use row‑level locks for indexed queries requiring high concurrency; use table‑level locks for non‑indexed operations.

What does a row lock actually lock?

Row locks are applied on index entries, not directly on the data rows.

Read vs. write locks in MySQL

SELECT triggers a read lock; UPDATE/DELETE/INSERT trigger a write (exclusive) lock, released on COMMIT or ROLLBACK.

Lock algorithms

• Next‑Key lock = Gap lock + Record lock. • Gap lock locks only the gap before a record. • Record lock locks the record itself.

When are locks released?

Locks are released when the transaction commits or rolls back.