When and How to Use MySQL Locks: A Deep Dive into Explicit and Implicit Locking

01 When to Apply Locks

1.1 Explicit Locks

MySQL locking can be divided into explicit and implicit locks. Explicit locks are easy to identify because they appear directly in SQL statements, such as:

select ... for update;</code>
<code>select ... in share mode;

The former acquires an exclusive lock, the latter a shared lock. Exclusive locks block both reads and writes on the locked range, while shared locks block only writes. This is often used in e‑commerce inventory updates to ensure consistency.

Other less common explicit locks include global locks (e.g., flush tables with read lock) and table locks ( lock tables … read/write).

1.2 Implicit Locks

Implicit locks are more subtle and can cause hidden problems. In addition to table locks, InnoDB uses metadata locks (MDL): a read lock during DML and a write lock during DDL, which can block normal operations and trigger retry storms.

Row locks and gap locks are also implicit. Depending on the isolation level, they may enlarge the lock range and affect performance, potentially leading to service snowballing.

1.3 Does Locking Affect Reads?

InnoDB’s MVCC (multi‑version concurrency control) records transaction IDs in the undo log and maintains a read view, allowing reads to proceed without being blocked by most locks.

02 How to Apply Locks

Understanding lock types and scope requires knowledge of transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Higher isolation levels enforce stricter locking.

Key principles:

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

Only objects accessed during the search are locked.

Equality queries on a unique index downgrade next‑key locks to row locks.

Equality queries on a non‑unique index may downgrade to gap locks when the rightmost value does not satisfy the condition.

Bug: range queries on a unique index may lock the first non‑matching value.

Locks are placed on indexes; gap locks are shared, not exclusive.

2.1 Read Committed (RC) Example

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

Various sessions (A, B, C) demonstrate how row locks on primary keys and indexes block or allow operations depending on the lock scope.

2.2 Non‑Unique Equality

When updating a non‑unique indexed column, the lock is placed on the index entries and may also lock the corresponding primary‑key rows, causing blocking for other sessions.

2.3 Primary Key Not Existing

If the target row does not exist, InnoDB acquires a next‑key lock on the surrounding range, which can block inserts that would fall into that range, preventing phantom reads.

2.4 Repeatable Read (RR) and Gap Locks

Under RR, gap locks are introduced to prevent phantom reads. The lock range can be larger than the exact key value, especially for non‑unique indexes.

03 When to Use and When to Avoid Locks

Because pessimistic locks can degrade performance, consider the following guidelines:

Prefer optimistic locking whenever possible.

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

Verify the database isolation level and analyze potential deadlocks or long‑running blocks.

There is no universal silver bullet; the appropriate strategy depends on the specific workload and business requirements.