Master MySQL Locks: From Basics to Advanced Optimizations

Reference Answer

MySQL's lock mechanism ensures data consistency and integrity during concurrent access.

From the lock granularity perspective, MySQL supports table‑level locks, row‑level locks and page‑level locks; InnoDB mainly uses row‑level locks to improve concurrency.

From the lock type perspective, MySQL provides shared locks (S) and exclusive locks (X). Shared locks allow multiple transactions to read the same resource, while exclusive locks grant exclusive read‑write access.

InnoDB also implements intention locks (IS and IX) at the table level to coordinate with row‑level locks.

1. Basic concepts: why locks are needed?

Locks serialize conflicting operations, such as preventing overselling in a flash‑sale scenario, by ensuring only one transaction can modify a row at a time.

2. Lock granularity

2.1 Table‑level lock

Locks the whole table; simple but low concurrency.

2.2 Row‑level lock

Locks only the rows being modified; higher concurrency but higher overhead.

2.3 Page‑level lock

Locks a data page (≈16KB); intermediate between table and row locks.

3. Lock types

3.1 Shared lock (S)

Multiple transactions can acquire a shared lock on the same row; no transaction can acquire an exclusive lock on that row.

SELECT ... LOCK IN SHARE MODE

3.2 Exclusive lock (X)

Exclusive lock blocks both shared and other exclusive locks.

SELECT ... FOR UPDATE

3.3 Compatibility matrix

S + S: compatible

S + X: not compatible

X + X: not compatible

4. Intention locks

Intention shared (IS) and intention exclusive (IX) locks act as table‑level markers indicating that a transaction intends to acquire row‑level shared or exclusive locks.

5. Record, gap and next‑key locks

Record lock locks the index record itself. Gap lock (used in REPEATABLE READ) locks the gap between index records to prevent phantom inserts. Next‑key lock combines a record lock with a gap lock.

6. Locks and isolation levels

Read‑uncommitted uses almost no locks; read‑committed uses MVCC for reads and record locks for writes; repeatable read (default) uses snapshot reads and next‑key locks; serializable locks all reads and writes.

7. Two‑phase locking

InnoDB acquires locks during the growing phase and releases them all at commit or rollback.

8. Deadlocks

Deadlocks occur when transactions wait for each other’s locks. MySQL detects and resolves them, but avoiding them by ordering resource acquisition, shortening transactions, and proper indexing is recommended.

9. Pessimistic vs optimistic locking

Pessimistic locking assumes frequent conflicts and locks before use; optimistic locking assumes rare conflicts and checks a version or timestamp at commit.

10. Lock optimization

Use proper indexes, primary or unique keys, keep transactions short, batch updates, avoid row‑by‑row operations, and monitor locks via information_schema.innodb_locks and SHOW ENGINE INNODB STATUS.

UPDATE users SET status = 1 WHERE name='张三';

-- Create an index on the name column
CREATE INDEX idx_name ON users(name);

UPDATE users SET status = 1 WHERE name='张三';