Overview of MySQL Locks: Types, Characteristics, and Usage Scenarios

When operating a database, concurrency issues can cause data inconsistency; ensuring consistent and effective concurrent access is a fundamental problem that all databases must solve, and lock conflicts significantly affect performance.

MySQL Lock Overview

Compared with other databases, MySQL's lock mechanism is relatively simple; its most notable feature is that different storage engines support different lock mechanisms.

MyISAM and MEMORY storage engines use table‑level locking.

InnoDB supports both row‑level and table‑level locking, defaulting to row‑level.

Main Characteristics of MySQL Locks

Table‑level lock: low overhead, fast acquisition, no deadlocks (MyISAM acquires all needed locks at once), large lock granularity, highest lock‑conflict probability, lowest concurrency.

Row‑level lock: higher overhead, slower acquisition, can cause deadlocks, smallest granularity, lowest conflict probability, highest concurrency.

Page lock: overhead and acquisition speed between table and row locks, can cause deadlocks, granularity between table and row, moderate concurrency.

Row Locks and Table Locks

Lock granularity is generally divided into row lock, table lock, and database lock.

Row lock: locks an entire row of data during access to prevent concurrent errors.

Table lock: locks the whole table during access to prevent concurrent errors.

Table lock: low overhead, fast acquisition, no deadlocks, large lock scope, high conflict probability, low concurrency.

Row lock: high overhead, slower acquisition, can cause deadlocks, small lock scope, low conflict probability, high concurrency.

Pessimistic Lock and Optimistic Lock

Pessimistic lock: assumes data will be modified by others, so it acquires a lock every time data is read, blocking other transactions until the lock is released.

Traditional relational databases use many such mechanisms (row lock, table lock, read lock, write lock) that lock before operation.

Optimistic lock: assumes data will not be modified by others, so it does not lock on read; during an update it checks whether the data has been changed by another transaction, often using a version number.

Optimistic locks are suitable for read‑heavy applications, improving throughput; many databases provide a write_condition -like mechanism as an optimistic lock.

The two locks have trade‑offs: optimistic locks work well when conflicts are rare, reducing lock overhead and increasing throughput, but frequent conflicts cause retries and degrade performance, making pessimistic locks more appropriate.

Shared Lock

A shared lock allows multiple transactions to hold the same lock on a resource simultaneously, similar to multiple keys opening the same door. In MySQL, a shared lock is obtained by appending LOCK IN SHARE MODE to a query.

When to Use Table Locks

For InnoDB tables, row‑level locks are preferred in most cases, but special situations may warrant table locks:

If a transaction updates a large portion or all rows of a large table, row‑level locking can be inefficient and cause long wait times; a table lock can improve performance.

If a transaction involves many tables and is complex, it may cause deadlocks; locking all involved tables at once can avoid deadlocks and reduce rollback overhead.

Excessive use of such transactions should prompt consideration of MyISAM tables.

Table Lock and Row Lock Application Scenarios

Table‑level lock: low concurrency, query‑heavy, few updates (e.g., small web applications).

Row‑level lock: high‑concurrency environments with strict transaction integrity requirements (e.g., online transaction processing systems).

More Architecture Advanced Series

Reply "Architecture" to learn more.

-END-