Chanler

Chanler

HomeArchives

"Dark Horse MySQL" 1. Lock

2
AI Translation
This post is translated from Chinese into English through AI.View Original
AI-generated summary
MySQL locks can be categorized into three types based on granularity: global locks, table-level locks, and row-level locks. **Global Locks**: These lock the entire database instance, making it read-only and blocking all write operations. They are typically used for full database backups to ensure data consistency. However, they can significantly hinder performance, especially during updates. **Table-Level Locks**: These lock entire tables, leading to a higher probability of lock conflicts and lower concurrency. They include: - **Table Locks**: Shared read locks allow multiple reads but prevent writes, while exclusive write locks allow only the locking transaction to read or write. - **Metadata Locks (MDL)**: Automatically managed by the system to maintain metadata consistency, preventing modifications to table structure during active transactions. - **Intent Locks**: Introduced in InnoDB to reduce conflicts between row and table locks. **Row-Level Locks**: These lock specific rows, allowing for the highest concurrency and the lowest chance of conflicts. Types include: - **Record Locks**: Lock individual rows to prevent updates or deletes by other transactions. - **Gap Locks**: Lock the gaps between index records to prevent inserts that could lead to phantom reads. - **Next-Key Locks**: Combine row and gap locks to secure both the data and the preceding gap. In summary, MySQL provides various locking mechanisms to manage data consistency and concurrency during transactions, each with its own advantages and trade-offs.

Introduction to Locks#

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

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

Global Lock#

A global lock locks the entire database instance, putting the entire instance into a read-only state. 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 image below, there may be inconsistencies where orders are generated but inventory is not deducted.

image.png|500

With the lock, only reading is allowed, ensuring global consistency at the time of backup.

image.png|500

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 a 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 Lock#

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

Table locks are mainly divided into 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 Lock allows reading by multiple transactions but prevents writing.

Exclusive Write Lock allows only the locking transaction to read and write, while others cannot operate.

image.png|500

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 the metadata are not allowed; this is to avoid conflicts between DML and DDL and ensure the correctness of reads and writes.

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

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 an MDL shared read lock (SHARE_READ/SHARE_WRITE), while DDL that modifies the table structure will request an MDL exclusive lock (EXCLUSIVE).

MDL mainly 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 restrict the consistency of DML operations.

image.png|500

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, thereby reducing the checks for table locks.

DML execution adds a row lock to a certain row, adding a table lock requires checking each row for locks|500

image.png|500

image.png|500

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 Lock#

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

Record Lock: Locks a single row record, preventing other transactions from performing updates and deletes on that row. It is 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. It is supported under the RepeatableRead isolation level.

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

image.png|500

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 the data, preventing other transactions from obtaining either a shared or exclusive lock on the same dataset.

Similar to read/write.

image.png|500

Related SQL row lock types.

image.png|500

By default, InnoDB operates at 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 gaps. 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.

image.png|500

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

image.png|500

Summary#

  1. Overview
    In concurrent access, it addresses the consistency and validity of data access.
    Global locks, table locks, row locks.
  2. Global Lock
    Locks the entire database instance, putting the entire instance into a read-only state.
    Poor performance, used for logical data backups.
  3. Table 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 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 to xLog by Mix Space. The original link is https://blog.0xling.cyou/posts/mysql/mysql-1

Loading...
Ownership of this post data is guaranteed by blockchain and smart contracts to the creator alone.