Introduction to MySQL Locks and Their Types

1 MySQL Lock Introduction

1.1 What Is a Lock

A lock is a mechanism used by computers to coordinate concurrent access to a shared resource by multiple processes; MySQL implements locks at the server or storage‑engine level to ensure data consistency and integrity.

1.2 Problems Solved by Locks

Locks address concurrency issues such as dirty reads, non‑repeatable reads, and phantom reads that arise when one transaction reads data while another modifies it.

Two common solutions are:

Solution 1: MVCC for reads, locking for writes – offers good performance but may read older versions.

Solution 2: Lock both reads and writes – slightly slower but guarantees the latest data, suitable for high‑security scenarios like banking.

2 Lock Classification

MySQL provides many lock types that can be categorized by mode, granularity, etc.

3 Optimistic and Pessimistic Locks

3.1 Optimistic Lock

1. Concept

An optimistic lock assumes conflicts are rare; it checks for conflicts only when the transaction attempts to commit.

2. Implementation

Implemented via a version column: each row stores a version number that increments on every update. When committing, the current version is compared with the version read earlier; if they match, the update succeeds.

3. Use Cases

Best for workloads with many reads and few writes.

3.2 Pessimistic Lock

1. Concept

A pessimistic lock assumes conflicts are likely, so it acquires a lock on each data operation.

2. Implementation

Implemented using shared (read) and exclusive (write) locks.

3. Use Cases

Suitable for scenarios with low concurrency but many writes.

4 Shared and Exclusive Locks

4.1 Shared Lock

1. Concept

A shared (read) lock allows other transactions to also acquire shared locks but prevents exclusive locks.

2. Implementation

Acquired with:

select … lock in share mode

4.2 Exclusive Lock

1. Concept

An exclusive (write) lock blocks all other transactions from reading or writing the locked rows.

InnoDB automatically applies exclusive locks for UPDATE, DELETE, INSERT; SELECT does not lock by default.

2. Implementation

Acquired with:

select … for update

5 Granularity Locks

5.1 Global Lock

1. Concept

A global lock blocks all DML operations on the entire MySQL instance.

2. Implementation

Issued with:

FLUSH TABLES WITH READ LOCK (FTWRL)

3. Use Cases

Typically used for full‑database backups.

5.2 Table‑Level Lock

1. Concept

Locks an entire table; both MyISAM and InnoDB support table locks, including metadata locks (MDL).

2. Implementation

Acquire with:

LOCK TABLES ... READ/WRITE

To release a table lock:

Step 1: Identify locked tables with SHOW PROCESSLIST

Step 2: Kill the locking sessions, e.g.:

kill 21;
kill 22;

5.3 Page‑Level Lock

Locks a group of adjacent rows (a page) – a compromise between row‑level and table‑level locking; supported by the BDB engine.

5.4 Row‑Level Lock

1. Concept

The finest granularity lock, with the lowest conflict probability but higher overhead; only InnoDB supports row locks.

2. Implementation

Row locks are applied to index records automatically during UPDATE/DELETE; they lock the index, not the physical row.

6 Intention Locks

1. Concept

Intention locks are table‑level locks that coordinate with row‑level locks, allowing both to coexist.

2. Function

When a transaction holds row locks, MySQL automatically places an intention lock on the table; another transaction can check the intention lock instead of scanning every row when requesting a table‑wide lock.

7 Gap, Record, and Next‑Key Locks

1. Concept

Record locks, gap locks, and next‑key locks are all exclusive locks; record locks target a specific row, gap locks protect a range, and next‑key locks combine both to prevent phantom reads.

7.1 Record Lock

Locks a specific row (e.g., primary key id=1):

SELECT * FROM user WHERE id = 1 FOR UPDATE;

The lock prevents other transactions from inserting, updating, or deleting that row.

7.2 Gap Lock

Based on a non‑unique index, it locks a range of index values, e.g., [1,10):

SELECT * FROM user WHERE id < 10 FOR UPDATE;

All rows with id 1‑9 are locked, blocking inserts in that interval.

7.3 Next‑Key Lock

Combines a record lock with a gap lock (left‑open, right‑closed interval) to prevent phantom reads; it applies only to non‑unique indexes.

Source: https://blog.csdn.net/wgzblog

Backend Technical Community Invitation

Join our high‑quality technical exchange group for developers, recruiters, and industry discussion. Please keep conversations civil and focused on technology, job referrals, or industry topics.

Advertisement and scams are prohibited.