Chanler

Introduction to Locks#

In MySQL, locks are categorized into three types based on granularity:

1. Global Lock: Locks all tables in the database
2. Table-Level Lock: Locks the entire table for each operation
3. Row-Level Lock: Locks the corresponding row data for each operation

Global Lock#

A global lock locks the entire database instance, putting the whole instance into read-only mode. Subsequent DML write statements, DDL statements, and transaction commit statements for updates will be blocked.

A typical scenario is locking all tables for a full database backup. Without locking, as shown in the figure below, there may be inconsistencies where orders are generated but inventory is not deducted.

If locked, only reading is allowed, ensuring global consistency at the time of backup.

flush tables with read lock; # Locking
mysqldump -uroot -p123456 database > database.sql # Backup
unlock tables; # Unlocking

Applying a global lock is a relatively heavy operation and has the following drawbacks:

1. If backing up on the master database, no updates can be executed during the backup period, effectively halting business operations.
2. If backing up on the slave database, the slave cannot execute the binary logs (binlog) synchronized from the master during the backup period, leading to master-slave lag.

In the InnoDB engine, adding the parameter --single-transaction allows for consistent data backup without locking, using snapshot reads for the backup operation.

mysqldump --single-transaction -uroot -p123456 database > database.sql

Table-Level Lock#

Table-level locks lock the entire table for each operation, with a larger locking granularity, the highest probability of lock conflicts, and the lowest concurrency, applied in storage engines like MyISAM, InnoDB, and BDB.

Table-level locks can be divided into the following three categories:

1. Table Lock
2. Metadata Lock (MDL)
3. Intent Lock

Table Lock#

Table locks are divided into two types:

1. Shared Read Lock
2. Exclusive Write Lock

Shared read locks allow reading by all but prevent writing.

Exclusive write locks allow only the locking party to read and write, while others cannot operate.

Metadata Lock#

The metadata lock (MDL) process is automatically controlled by the program and does not require explicit use. It is automatically applied when accessing a table. The main purpose of the MDL lock is to maintain the consistency of table metadata. When there are active transactions on a table, write operations on metadata are not allowed; this is to avoid conflicts between DML and DDL, ensuring the correctness of reads and writes.

MDL was introduced in MySQL v5.5:
When performing DML operations (insert, delete, update, query) on a table, a shared MDL read lock is applied.
When performing DDL operations that change the table structure, an exclusive MDL write lock is applied.

The essence of the metadata lock is that while a transaction is ongoing, the table structure cannot be modified; DML during the transaction will request a shared MDL read lock (SHARE_READ/SHARE_WRITE), while DDL that modifies the table structure will request an exclusive MDL lock (EXCLUSIVE).

MDL primarily targets the table structure's metadata, ensuring that the shared read lock (SHARE) for reading the table structure and the exclusive write lock (EXCLUSIVE) for changing the table structure can maintain consistency during DML operations.

Intent Lock#

To avoid conflicts between row locks and table locks during DML execution, InnoDB introduces intent locks, allowing table locks to avoid checking each row of data for locks, thus reducing the checks for table locks.

When other transactions hold an intent shared lock (IS) on the table, it is possible to add a read table lock, but not a write table lock; the transaction must wait for the intent shared lock to be released.

When other transactions hold an intent exclusive lock (IX) on the table, it is not possible to add either a read or write table lock; the transaction must wait for the intent exclusive lock to be released.

Row-Level Lock#

Row-level locks lock the corresponding row data for each operation, with the smallest locking granularity, the lowest probability of lock conflicts, and the highest concurrency, applied in the InnoDB storage engine.

Row Lock (Record Lock): Locks a single row record, preventing other transactions from performing updates and deletes on that row, supported under ReadCommitted and RepeatableRead isolation levels.

Gap Lock: Locks the gaps between index records, excluding the records themselves, ensuring that the gaps between index records remain unchanged and preventing other transactions from inserting into these gaps, thus avoiding phantom reads, supported under RepeatableRead isolation level.

Next-Key Lock: A combination of row locks and gap locks, locking both the data and the gap before the data, supported under RepeatableRead isolation level.

Row Lock (Record Lock)#

There are two types of row locks:

1. Shared Lock (S): Allows one transaction to read a row, preventing other transactions from obtaining an exclusive lock on the same dataset.
2. Exclusive Lock (X): Allows the transaction holding the exclusive lock to update data, preventing other transactions from obtaining either a shared lock or an exclusive lock on the same dataset.

Similar to read/write.

Related SQL row lock types.

By default, InnoDB operates under the RepeatableRead transaction isolation level. InnoDB uses next-key locks for searching and index scanning to prevent phantom reads:

1. When retrieving based on a unique index, if performing an equality match on existing records, it will automatically optimize to a row lock.
2. InnoDB's row locks are applied to indexes; if data is retrieved without using index conditions, InnoDB will lock all records in the table, which will escalate to a table lock.

Gap Lock / Next-Key Lock#

The sole purpose of the gap lock is to prevent other transactions from inserting into the gap. Gap locks can coexist; a gap lock used by one transaction will not prevent another transaction from using a gap lock on the same gap.

It can be a bit difficult to understand, so let's refer to the notes.

Summary#

1. Overview
   In concurrent access, addressing data access consistency and validity issues.
   Global locks, table-level locks, row-level locks.
2. Global Lock
   Locks the entire database instance, putting the whole instance into read-only mode.
   Poor performance, used during logical data backups.
3. Table-Level Lock
   Locks the entire table for operations, with a large locking granularity and a high probability of lock conflicts.
   Table locks, metadata locks, intent locks.
4. Row-Level Lock
   Locks the corresponding row data for operations, with the smallest locking granularity and the lowest probability of lock conflicts.
   Row locks, gap locks, next-key locks.

This article is synchronized and updated by Mix Space to xLog. The original link is https://blog.0xling.cyou/posts/mysql/mysql-1