Mastering MySQL Locks: Types, Mechanisms, and Best Practices

In database management systems, locks coordinate access to resources by multiple processes or threads. MySQL uses locks and 锁和MVCC (多版本并发控制) 机制实现了事务隔离级别 to achieve transaction isolation levels, ensuring data reliability under concurrent operations. Below we classify MySQL locks and introduce them in detail.

I. Classification by Performance

From a performance perspective, MySQL locks can be divided into two categories:

Pessimistic Lock : A pessimistic lock assumes that data may be modified by other transactions, so it locks the data for the entire duration of the transaction. Reads and writes both require the lock, preventing other sessions from modifying the locked data until the lock is released.

Optimistic Lock : An optimistic lock assumes that conflicts are rare. It is implemented via a version‑record mechanism. Commonly, a version number column is added to the table. When a row is read, its version is retrieved; on update, the version is incremented and the update succeeds only if the stored version matches the current version, otherwise the update is aborted. 通过数据版本记录机制实现 为数据增加一个版本号字段

II. Classification by Operation Type

Based on the type of operation, MySQL locks fall into two groups:

Read Lock (shared lock or S‑lock): Multiple read locks can be placed on the same data simultaneously without interfering with each other.

Write Lock (exclusive lock or X‑lock): A write lock blocks other write and read locks. If a read lock is held, additional read locks are allowed but a write lock cannot be obtained; once a write lock is held, no other read or write locks can be granted.

III. Classification by Data Granularity

Considering the granularity of the locked data, MySQL provides three main lock types:

Table Lock : Locks the entire table. It has low overhead and fast acquisition but low concurrency. MySQL supports table‑shared locks and table‑exclusive write locks.

Row Lock : Locks individual rows. It offers fine granularity and high concurrency but higher overhead and possible deadlocks. In InnoDB, row locks are applied to index records. Important notes: 行锁主要加在索引上 , 行锁可能会变成表锁 , 行锁是针对索引加锁 , 加锁的索引不能失效，否则行锁可能会变成表锁 . Use lock in share mode for shared locks and for update for exclusive locks.

Page Lock : Locks at the page (or extent) level, with overhead between table and row locks, moderate concurrency, and possible deadlocks.

IV. Finer‑Grained Classification

At an even finer level, MySQL defines two additional lock types:

Gap Lock : When a range query requests a lock, InnoDB locks the index entries that satisfy the condition. If the range contains no actual rows, InnoDB locks the “gap” between index entries. Gap locks are effective only under the REPEATABLE READ isolation level.

Next‑Key Lock : A combination of a row lock and a gap lock. It locks both the index record and the surrounding gap, providing comprehensive protection for ordered data. This prevents insert, delete, or update anomalies in scenarios such as seat allocation in an airline reservation system.

V. Summary

By thoroughly analyzing the various MySQL lock types, we can understand their underlying principles and, based on real‑world business scenarios, choose and apply the appropriate locking mechanisms. This enables us to maximize performance, avoid potential risks, and build efficient, stable database applications that handle concurrent operations gracefully.