Understanding MySQL Locks: From Global to Row‑Level and Deadlock Prevention

Overview

MySQL uses locks to ensure data consistency when multiple transactions access the same rows concurrently. The article explains why concurrency causes problems, the purpose of locks, and classifies MySQL locks into global, table, and row locks.

1. Global Locks

A global lock (FTWRL – FLUSH TABLES WITH READ LOCK) locks the entire MySQL instance, making the whole database read‑only. It is useful for full‑database logical backups, but while the lock is held no data‑modifying statements (INSERT, UPDATE, DELETE, DDL) can execute, which can stop business operations.

Typical use cases: logical backup of all tables, ensuring a consistent snapshot.

Drawbacks: if performed on the primary server the application is blocked; on a replica it can delay replication.

2. Table Locks

MySQL provides two kinds of table‑level locks: explicit table locks ( LOCK TABLES … READ/WRITE) and metadata locks (MDL) that are acquired automatically when a table is accessed.

Read locks allow many sessions to read the same table concurrently.

Write locks are exclusive and block other sessions from reading or writing the table.

MDL is automatically taken for SELECT, INSERT, UPDATE, DELETE and for DDL operations; read‑only MDL for SELECT, write MDL for DDL.

For InnoDB tables manual LOCK TABLES is discouraged because InnoDB already provides finer‑grained row locks.

3. Row Locks

InnoDB implements row‑level locks (record locks) on index entries. The article lists the main row‑lock types and their typical triggers.

Record lock – locks a single index record; used by SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE.

Gap lock – locks the gap between index records to prevent phantom rows; active in REPEATABLE READ isolation.

Next‑key lock – combination of record and gap lock; the default lock algorithm in REPEATABLE READ.

Effective row locking requires that the WHERE clause uses an index; otherwise InnoDB falls back to a table lock.

4. Lock Implementation Details

Two‑phase locking (2PL) governs InnoDB lock acquisition: a lock‑acquire phase during transaction execution and a release phase at COMMIT or ROLLBACK. Keeping the lock‑holding time short reduces contention.

Intention locks (IS, IX) are table‑level markers that indicate a transaction intends to acquire row‑level shared or exclusive locks, allowing the lock manager to quickly detect conflicts.

5. Deadlocks

Deadlocks occur when two or more transactions wait for each other’s locks, forming a cycle. The article presents three typical scenarios:

Shared‑lock upgrade deadlock (S → X) when two sessions read a row and then try to update it.

Cross‑order deadlock when transactions lock rows in opposite order (e.g., id=1 then id=2 vs. id=2 then id=1).

Gap‑lock deadlock caused by conflicting insert‑intent locks and gap locks.

InnoDB detects deadlocks automatically, rolls back the transaction with the smallest rollback cost, and returns error 1213.

6. Prevention Strategies

Access rows in a fixed order (e.g., ascending primary‑key).

Prefer optimistic locking (version columns) over pessimistic locking when possible.

Keep transactions short and split large ones into smaller units.

Create appropriate indexes so that WHERE clauses use them, avoiding full‑table scans and unintended table locks.

Consider using READ COMMITTED isolation to eliminate gap locks if the application can tolerate it.

7. Example: AUTO_INCREMENT Lock

When a table contains an AUTO_INCREMENT column, MySQL uses an AUTO‑INC lock (a table‑level lock) to generate sequential values. Two implementations exist:

Traditional AUTO‑INC lock holds the lock for the whole INSERT statement.

Lightweight lock acquires the lock only while the values are generated, then releases it, improving concurrency for multi‑row inserts.

Example DDL:

CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT,
    c VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

Conclusion

Understanding MySQL’s lock hierarchy—from global to row‑level—and the conditions that lead to deadlocks enables developers to design high‑concurrency applications, choose appropriate isolation levels, and apply practical safeguards.