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

This article introduces MySQL's locking mechanisms and explains why understanding locks is essential for ensuring data safety and avoiding performance problems in database‑driven applications.

The discussion is organized into three parts: when locks are added, how they are added, and when they should or should not be added.

Explicit locks are directly visible in SQL statements. The most common forms are: SELECT ... FOR UPDATE – acquires an exclusive (write) lock. SELECT ... IN SHARE MODE – acquires a shared (read) lock.

Exclusive locks block both reads and writes on the selected rows, while shared locks block only writes.

Implicit locks are added by the storage engine during normal DML operations. Typical examples include:

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

Table lock: LOCK TABLES … READ/WRITE.

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

MDL locks can cause blocking when schema changes are attempted while other sessions hold read locks.

The article then examines how transaction isolation levels affect locking. In InnoDB, the default level is REPEATABLE READ , which uses next‑key locks to prevent phantom reads. READ COMMITTED uses row locks for updates but does not protect against phantoms, while READ UNCOMMITTED provides no lock protection.

Key locking principles are introduced:

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

Principle 2: Only the rows (or index entries) accessed by the query are locked.

Optimization 1: Equality queries on a unique index downgrade next‑key locks to simple row locks.

Optimization 2: Equality queries on a non‑unique index may downgrade the right‑hand side of a next‑key lock to a gap lock.

To illustrate these concepts, the article provides a series of session‑based examples. First, a table is created:

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);

Several scenarios are then demonstrated using three concurrent sessions (A, B, C):

Primary‑key equality exists – Session A updates the primary key, causing row locks on the old and new values; Session B’s insert of the same key blocks, while Session C’s insert of a different key succeeds.

Non‑unique index equality exists – Session A updates rows via the secondary index a, which locks the index range (0, 10] and the corresponding primary‑key rows; subsequent inserts that fall inside the locked range are blocked.

Primary‑key range query – SELECT * FROM t_db_lock WHERE id >= 0 AND id <= 5 FOR UPDATE locks rows 0 and 5; inserts into the locked range are blocked, demonstrating phantom‑read protection.

Range query on a non‑unique index – Locks the index interval (0, 10] without degrading to a row lock, causing broader blocking.

Lock on a column without an index – A FOR UPDATE on b = 6 acquires a lock on the entire table, illustrating the danger of locking on non‑indexed columns.

From these examples the article derives practical recommendations:

Prefer optimistic concurrency control whenever possible.

If pessimistic locking is required, always add an index on the locked columns.

Understand the database’s isolation level and the lock scope of each SQL statement to avoid long‑running blocking or deadlocks.

In summary, MySQL locks are powerful tools for ensuring data consistency, but they must be used with a clear understanding of their scope, type (explicit vs. implicit), and interaction with transaction isolation levels to prevent performance degradation and service outages.