Master MySQL Locks: Types, Mechanisms, and How to Avoid Deadlocks

As the hiring season arrives, interviewers often quiz candidates on MySQL locks, prompting a deep dive into lock concepts.

Why Introduce Locks

Locks coordinate concurrent access to shared resources, ensuring data correctness and uniqueness during concurrent transactions.

In MySQL, locks are crucial for managing data access concurrency.

Locks are implemented at the server layer or storage‑engine layer, varying across engines.

MySQL Lock Implementation

Lock types can be viewed from the perspective of lock granularity.

Global Lock

A global lock locks the entire MySQL instance. The command is: flush tables with read lock After execution the whole database becomes read‑only; write statements and DDL are blocked.

Unlock command: unlock tables Typical use case: full‑database logical backup, where data and schema remain unchanged during the lock.

Page Lock

Page‑level locks are specific to the BDB storage engine; most users work with InnoDB, so page locks are rarely discussed.

Table Lock

Both MyISAM and InnoDB support table‑level locks, though InnoDB defaults to row‑level locks.

Table locks include:

Table Lock

Lock the whole table:

// read lock
lock tables table_name read;
// write lock
lock tables table_name write;
// release all table locks
unlock tables

Write locks block any write operations on the table until released. Avoid using table locks with InnoDB because of coarse granularity.

Metadata Lock (MDL)

Since MySQL 5.5, MDL is automatically acquired when accessing a table.

CRUD operations acquire an MDL read lock.

DDL operations acquire an MDL write lock.

MDL is released automatically after the transaction ends.

Intention Lock

Before acquiring row‑level shared (S) or exclusive (X) locks, an intention lock is placed at the table level.

Two types:

Intention Shared (IS) lock – required before a row‑level shared lock.

Intention Exclusive (IX) lock – required before a row‑level exclusive lock.

Why add intention locks first?

They allow the engine to quickly determine whether any rows in a table are locked.

Lock commands:

// intention shared lock then shared row lock
select ... lock in share mode;
// intention exclusive lock then exclusive row lock
select ... for update;

AUTO‑INC Lock

Controls the auto‑increment column during INSERTs to prevent duplicate values.

What does the AUTO‑INC lock do?

It holds the lock until the INSERT finishes, ensuring sequential IDs.

Issues: bulk INSERTs can cause contention.

Optimization mode (innodb_autoinc_lock_mode):

0 – traditional: lock held until statement ends.

1 – consecutive: lightweight mutex for simple INSERT, lock for bulk INSERT.

2 – interleaved: lock released immediately after ID allocation (may reorder IDs).

How does AUTO‑INC lock behave with multiple INSERTs in one transaction?

The lock is independent of the transaction; each INSERT obtains its own lock.

Row Lock

Row locks are applied to individual index entries.

Record Lock

Locks a single row record: SELECT * FROM `demo` WHERE `id`=23 FOR UPDATE; Updates on the same primary key also acquire a record lock.

UPDATE demo SET name='xiaoxu' WHERE id=23;

Gap Lock

Introduced in REPEATABLE READ to prevent phantom reads by locking a range without the rows themselves.

SELECT * FROM demo WHERE id > 23 AND id < 25 FOR UPDATE;

This blocks insertion of id=24.

Next‑Key Lock

Combines record lock and gap lock; locks the index record and the preceding gap (left‑open, right‑closed interval).

Used on non‑unique indexes to prevent phantom reads.

Next‑Key Lock Downgrade

When a record lock or gap lock can satisfy the isolation requirement, the next‑key lock degrades to the simpler lock.

Scenarios:

Unique index equality query: existing row → record lock; non‑existing row → gap lock.

Non‑unique index equality query: existing row → next‑key lock + gap lock; non‑existing row → next‑key lock degrades to gap lock.

Future articles will cover how MySQL acquires row locks.