MySQL Lock Mechanisms: When, How, and When Not to Use

This article explains MySQL's lock mechanisms, emphasizing why understanding locks is crucial for data safety and system performance.

It addresses three core questions: when to acquire locks, how to acquire them, and when not to use them.

Explicit locks are directly visible in SQL statements, such as SELECT ... FOR UPDATE (exclusive lock) and SELECT ... IN SHARE MODE (shared lock). The exclusive lock blocks both reads and writes on the selected rows, while the shared lock only blocks writes.

Other explicit lock commands include global locks like FLUSH TABLES WITH READ LOCK (used during logical backups) and table locks via LOCK TABLES ... READ/WRITE.

Implicit locks are added automatically by InnoDB. They include metadata locks (MDL) for DML (read lock) and DDL (write lock), row locks, gap locks, and next‑key locks. These locks protect rows during updates and prevent phenomena such as phantom reads.

Transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) influence which locks are taken. For example, Repeatable Read adds gap locks to avoid phantom reads, while Read Committed does not.

Practical examples using three sessions (A, B, C) illustrate lock behavior in various scenarios:

Primary‑key equality queries lock the exact row; concurrent inserts on the same key are blocked.

Non‑unique index equality queries acquire next‑key locks, which may block a broader range of rows.

Range queries (e.g., WHERE id >= 0 AND id <= 5 FOR UPDATE) lock all rows in the range and can cause phantom reads if not handled properly.

When the target row does not exist, InnoDB creates a gap lock on the surrounding interval, preventing other sessions from inserting rows that would satisfy the condition.

Key principles derived from the examples:

The basic lock unit is a next‑key lock (open‑closed interval).

Only objects accessed during the search are locked.

Unique‑index equality can downgrade to a simple row lock.

Range scans on non‑unique indexes often become gap locks.

Practical guidelines for developers:

Prefer optimistic locking whenever possible.

If pessimistic locking is required, ensure the locked columns are indexed.

Be aware of the database’s isolation level and its impact on lock granularity.

Avoid long‑running transactions that hold locks, as they can cause deadlocks and degrade performance.

Understanding MySQL's locking behavior helps prevent common pitfalls such as phantom reads, deadlocks, and excessive DB load.

CREATE TABLE `t_db_lock` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;
INSERT INTO t_db_lock VALUES
  (0,0,0),
  (5,5,5),
  (10,10,10);