MySQL Lock Granularity and Types

In MySQL, locking mechanisms are categorized by their scope into three primary types: global locks, table-level locks, and row-level locks.

Global Locking

Applying a global lock places the entire database instance into a read-only state. This is historically used for logical full-database backups to ensure consistency by preventing data or schema modifications during the dump process.

However, this approach is generally discouraged for large-scale production environments. Since global locks block all write operations, business applications relying on data updates will experience significant downtime during the backup window.

A preferred solution involves using the transactional capabilities of storage engines that support the Repeatable Read isolation level. By initiating a transaction, a Read View is established. While Multi-Version Concurrency Control (MVCC) allows concurrent updates from other transactions, the backup process sees a consistent snapshot of the data.

When using mysqldump, the --single-transaction parameter facilitates this by starting a transaction before the dump. Since the InnoDB engine defaults to Repeatable Read, this method is standard for InnoDB backups.

Table-Level Locking

MySQL employs several varieties of table-level locks:

• Table Locks: These are categorized into Shared Read locks and Exclusive Write locks. Shared locks permit all threads to read but block writes. Exclusive locks grant read/write access only to the holding thread while blocking all others.
• Metadata Locks (MDL): The server automatically applies these during DDL operations to ensure table structure consistency.
  • Read locks (MDL读锁) are applied during CRUD operations.
  • Write locks (MDL写锁) are applied during structural changes (ALTER TABLE, etc.).

  A critical issue arises if a long-running transaction holds an MDL read lock. Subsequent DDL operations requiring a write lock will be blocked. Since the MDL lock acquisition queue prioritizes write locks, this eventually blocks all new CRUD requests for that table, potentially exhausting connection threads. Safe schema changes require checking for and terminating long transactions before proceeding.

• Intention Locks: These allow the server to quickly determine if a table contains locked rows without scanning every row. They act as a high-level indicator; if a "parent" intention lock conflicts with a request, the table lock is blocked.
• AUTO-INC Locks: This special table-level mechanism manages auto-incrementing primary keys. It ensures sequential values by locking the table briefly during the insertion of a statement. To improve performance, newer MySQL versions (post-5.1.22) use a lightweight mutex that releases immediately after assigning the auto-increment value, rather than waiting for the entire statement to complete.

Row-Level Locking

Supported by the InnoDB engine (but not MyISAM), row-level locks offer the finest granularity. If a DML operation operates on an indexed column, only the specific row is locked. If the index is missing or invalid, the lock degrades to a table lock, severely impacting concurrency.

• Record Locks: Locks specific index records. Standard DML statements acquire exclusive locks, while SELECT ... LOCK IN SHARE MODE acquires shared locks.
• Gap Locks: Exclusive to the Repeatable Read isolation level, these lock the gaps between index records to prevent phantom reads. They block insertions into the locked range.
• Next-Key Locks: A combination of a record lock and a gap lock on the preceding space.

Analyzing Row-Level Locking Algorithms

The fundamental unit of locking in InnoDB is the Next-Key lock (a combination of Record Lock and Gap Lock). The locking behavior changes based on the query type and index structure. The Next-Key lock typically covers a (left-open, right-closed] interval, while Gap Locks are (left-open, right-open).

Locking follows optimization rules where Next-Key locks may degrade into Record Locks or Gap Locks if the stricter locking is unnecessary to prevent phantom reads.

Equality Queries on Unique Indexes

The locking behavior differs based on whether the record exists:

• Record Exists: When the record is located in the index tree, the Next-Key lock degrades into a Record Lock.
• Record Does Not Exist: The engine scans until it finds the first record greater than the query value. The Next-Key lock on this greater record degrades into a Gap Lock.

-- Attempting to lock a non-existent SKU
SELECT * FROM inventory WHERE sku_id = 105 FOR UPDATE;

In this scenario, if SKU 105 does not exist, the database locks the gap before the next highest SKU (e.g., 110). This prevents other transactions from inserting SKU 105, ensuring the result set remains empty (avoiding the phantom where a row appears later).

Range Queries on Unique Indexes

Range queries apply Next-Key locks to every scanned index record, with specific degradation rules:

• Greater Than or Equal (>=): If the equality value exists, the lock on that record degrades to a Record Lock. If it does not exist, the scan continues, and the final lock remains a Next-Key lock covering the boundary.
• Less Than or Less Than/Equal (< or <=):
  • If the specific boundary value does not exist, the scan ends at the rightmost record, and the lock degrades to a Gap Lock.
  • If the value exists (e.g., id <= 10), the Next-Key lock on the record 10 is retained (because the record is included in the set). However, for id < 10, the lock on 9 degrades to a Gap Lock since 9 is the last included record.

Equality Queries on Non-Unique Indexes

Since non-unique indexes (secondary indexes) exist alongside the primary key index, locking applies to both.

• Record Exists: The engine scans the secondary index. Next-Key locks are applied to matching records. The scan stops at the first non-matching record, where the Next-Key lock degrades to a Gap Lock. Simultaneously, the corresponding primary key records are locked with Record Locks to prevent data modification.
• Record Does Not Exist: No primary key records are locked. The engine locks the secondary index gap where the record would have been inserted (Gap Lock) to prevent phantom insertions.

Range Queries on Non-Unique Indexes

Unlike unique indexes, range queries on non-unique indexes do not degrade. The engine applies Next-Key locks to all secondary index records encountered during the scan.

-- Range query on a non-unique index
SELECT * FROM orders WHERE total_amount >= 500 FOR UPDATE;

Queries Without Indexes

If a locking query cannot utilize an index (due to missing indexes or inefficient execution plans), MySQL performs a full table scan. In this scenario, the server applies Next-Key locks to every index record in the table. Effectively, this locks the entire table, blocking all other write operations and significantly degrading database performance. It is critical to verify index usage in production environments for UPDATE, DELETE, and SELECT ... FOR UPDATE statements.