Understanding MySQL Locks: When to Use Them, How They Work, and When to Avoid Them

Introduction

MySQL locks are essential for protecting data consistency, but misuse can cause severe performance degradation or even bring a database down. The article breaks down the lock mechanisms, when they are acquired, how they are applied, and guidelines for safe usage.

1. When to Acquire a Lock

Explicit Locks

Explicit locks appear directly in SQL statements and are easy to identify.

SELECT ... FOR UPDATE;      -- exclusive (write) lock
SELECT ... IN SHARE MODE;   -- shared (read) lock

FOR UPDATE blocks both reads and writes on the selected rows, while IN SHARE MODE only blocks writes. These are commonly used in inventory updates or any scenario where a row must not be modified concurrently.

Implicit Locks

Implicit locks are added by the storage engine without being written in the SQL text. They include:

Global lock: FLUSH TABLES WITH READ LOCK (used during logical backups).

Table lock: LOCK TABLES … READ/WRITE.

Metadata lock (MDL): read lock during DML, write lock during DDL.

MDL read locks are taken when a session performs SELECT/INSERT/UPDATE/DELETE; MDL write locks are taken when a session alters table structure, which can block other sessions.

2. How Locks Are Applied

Transaction Isolation Levels

The isolation level determines which lock types are used:

READ UNCOMMITTED : no row‑level locks, dirty reads possible.

READ COMMITTED : prevents dirty reads; uses row locks but not gap locks.

REPEATABLE READ (default) : adds next‑key (row + gap) locks to prevent phantom reads.

SERIALIZABLE : the strictest, converting many reads into locks.

Most production systems run at READ COMMITTED for a balance between consistency and concurrency.

Locking Principles

Principle 1: The basic lock unit is a next‑key lock (a half‑open interval).

Principle 2: Only the objects accessed during the search are locked.

Optimizations

When an equality condition uses a unique index , the next‑key lock degrades to a simple row lock.

When scanning a unique index to the right and the last value does not satisfy the equality, the lock degrades to a gap lock .

3. Practical Session Examples

The following examples use a table t_db_lock (id INT PK, a INT, b INT) to illustrate lock behavior under different scenarios.

CREATE TABLE `t_db_lock` (
  `id` INT NOT NULL,
  `a`  INT DEFAULT NULL,
  `b`  INT 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);

3.1 Primary‑key equality exists (session A locks id=0)

-- Session A
BEGIN;
UPDATE t_db_lock SET id=id+1 WHERE id=0;   -- locks rows id=0 and id=1

-- Session B
INSERT INTO t_db_lock VALUES (1,1,1);      -- blocked (row lock)

-- Session C
UPDATE t_db_lock SET id=id+1 WHERE id=5;   -- succeeds (different row)

3.2 Non‑unique index equality (WHERE a=0)

-- Session A
BEGIN;
UPDATE t_db_lock SET b=b+1 WHERE a=0;       -- locks index range (0,5] and the primary‑key row

-- Session B
INSERT INTO t_db_lock VALUES (1,1,1);      -- blocked (gap/row lock on primary key)

-- Session C
UPDATE t_db_lock SET b=b+1 WHERE b=5;     -- succeeds (outside lock range)

3.3 Primary‑key range lock (FOR UPDATE on id BETWEEN 0 AND 5)

-- Session A
BEGIN;
SELECT * FROM t_db_lock WHERE id>=0 AND id<=5 FOR UPDATE;  -- locks rows id=0…5

-- Session B
UPDATE t_db_lock SET b=b+1 WHERE a=0;   -- blocked (row in lock range)

-- Session C
INSERT INTO t_db_lock VALUES (1,1,1);  -- succeeds (id=1 already exists, but insert is blocked by primary‑key conflict, not by lock)

3.4 Gap lock preventing phantom reads (REPEATABLE READ)

-- Session A (RR)
BEGIN;
SELECT * FROM t_db_lock WHERE id=3 FOR UPDATE;   -- row does not exist, creates a gap lock (0,5)

-- Session B
INSERT INTO t_db_lock VALUES (2,2,2);           -- blocked (inside gap)

-- Session C
INSERT INTO t_db_lock VALUES (6,6,6);           -- succeeds (outside gap)

3.5 Metadata lock on an unindexed column

-- Session A
BEGIN;
SELECT * FROM t_db_lock WHERE b=6 FOR UPDATE;   -- no index on b → locks the whole table

-- Session B
INSERT INTO t_db_lock VALUES (3,3,3);           -- blocked (table‑level lock)

4. When Not to Use Pessimistic Locks

Because locks can quickly become a bottleneck, the article recommends:

Prefer optimistic locking (e.g., version columns) whenever possible.

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

Understand the current isolation level and evaluate whether the SQL may cause deadlocks or long‑running blockages.

Use range locks only when necessary to prevent phantom reads; otherwise, keep transactions short.

There is no universal “silver bullet” for concurrency control—each use‑case must be analyzed to choose the safest and most efficient strategy.

Conclusion

By mastering explicit and implicit MySQL locks, next‑key and gap lock behavior, and the impact of isolation levels, developers can avoid common pitfalls such as unnecessary blocking, deadlocks, and phantom reads, ultimately keeping their databases performant and reliable.