Location>code7788 >text

MySQL Avoids Phantom Reads with Next-Key Locking (Row Locking + Gap Locking)

Popularity：826 ℃/2024-10-16 23:17:06

In MySQL, the InnoDB engine is used by theNext-Key Lockingtechniques to solve phantom reads. Phantom reads are a transactional concurrency problem that typically occurs in theRepeatable ReadIn a range query operation at the isolation level. Phantom reads occur when a transaction performs the same range query multiple times during a query, but the results are inconsistent due to insertion or deletion operations by other transactions, resulting in "phantom" records.

Next-Key Locking The technology combines theRecord Lock cap (a poem)Gap Lock, which avoids phantom read problems by locking records within ranges and gaps between them, preventing other transactions from inserting or deleting data within these locked areas.

I. What is Next-Key Locking?

Next-Key Locking is aLocked Interval mechanism, which consists of two parts:

  1. Record Lock: Lock an exact row of data to prevent other transactions from modifying that row.
  2. Gap Lock: Lock the "gap" between rows of data, preventing other transactions from inserting new data in that gap.

Next-Key Locking locks the rows of the current query and their "before and after" gaps, which not only prevents modification of existing records, but also prevents the insertion of new data within the query range, avoiding phantom reads.

Principle of Next-Key Locking

existRepeatable ReadAt the isolation level, when executing a range query, InnoDB locks all rows that satisfy the condition and their neighboring gaps in the range through Next-Key Locking. For example, execute the following SQL statement:

SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

This query locks theusersThe table of all those who satisfy theage BETWEEN 20 AND 30The locking mechanism is as follows. The specific locking mechanism is as follows:

  1. Record Lock: Lock all eligible rows and prevent other transactions from modifying them.
  2. Gap Lock: Lock "gaps" between rows in the query range, preventing other transactions from inserting new rows in those gaps.

Assume that the current data is as follows:

id | age
---------
 1 | 18
 2 | 25
 3 | 28
 4 | 35

fulfillmentSELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE; Next-Key Locking does the following:

  • lockage=25 respond in singingage=28 These two lines (Record Lock).
  • lockage > 18 until (a time)age < 35 All gaps (Gap Lock) between theage=19 until (a time)age=34 Inserts a new line between

This ensures that no other transaction can insert, delete, or modify data within the scope of the query until the current transaction commits, thus avoiding phantom reads.

The realization mechanism of Next-Key Locking

  1. Record Lock: A row lock is a precise lock that locks only a specific row, preventing other transactions from modifying that row of data. Row locking ensures that read and write operations in a transaction are consistent for rows of data that already exist.
  2. Gap Lock: A gap lock is a type of scope lock that locks the gaps between rows of data. Instead of locking the actual rows of data, it locks the gaps between rows and prevents other transactions from inserting new rows in those gaps. This type of lock is used to solve phantom read problems.
  3. Next-Key Locking (row locking + gap locking): Next-Key Locking combines row locking and gap locking by locking not only the exact rows of data, but also the gaps between them. In this way, Next-Key Locking prevents other transactions from inserting new data within the scope of the current query, thus avoiding phantom reads.

Next-Key Locking The process of resolving phantom readings

Next-Key Locking is the first step in theRepeatable Readused in the isolation level, the process is roughly as follows:

  1. Service AExecute a range query, such asSELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;. At this point, InnoDB uses Next-Key Locking to lock the rows and gaps of data in the range, preventing other transactions from inserting new rows in the range.
  2. Transaction BAttempts atage=26to insert a new record at the location of the Since transaction A is already locked by Next-Key Locking theage=25until (a time)age=30gap between them, transaction B will be blocked until transaction A commits or rolls back.
  3. Transaction ATransaction B can only successfully insert data after a commit or rollback. If Transaction A does not lock this range, after Transaction B inserts the data, when Transaction A then performs a range query, the results will be different, resulting in phantom reads.

In this way, Next-Key Locking solves the phantom read problem caused by concurrent inserts.

V. Advantages and Limitations of Next-Key Locking

dominance：

  • Addressing phantom reading: Next-Key Locking effectively prevents phantom reads during range queries and ensures transaction consistency.
  • Enhanced Data Security: Lock gaps in the query scope to prevent other transactions from inserting or modifying data without committing, ensuring data consistency in the transaction.

limitation：

  • Reduced concurrency performance: The locking granularity of Next-Key Locking is large and may lock a large number of rows and gaps, resulting in a concurrent performance degradation of the system.
  • Gap Lock Overhead: Gap locking can result in the inability to insert data into gaps within the locking range, which may affect data write efficiency in some scenarios, especially in highly concurrent write scenarios.

Next-Key Locking's Practical Usage Scenarios

  1. Balance inquiries in the banking system：
    • In a banking system, a user may perform a range query (e.g., a query for transaction records for a specific time period) when inquiring about an account balance. With Next-Key Locking, the system prevents other transactions from inserting new transaction records during this time period, ensuring that the user gets consistent results for each query.
  2. Order inquiry in e-commerce system：
    • In e-commerce platforms, when users query orders for a certain time period, they may need to ensure the consistency of order data during the query process. With Next-Key Locking, other users are prevented from inserting new orders during the order query period to ensure the consistency of order data.

VII. Summary

Next-Key Locking By combining row locks and gap locks, it solves the problem of the MySQLRepeatable ReadPhantom read problem at isolation level. It not only locks the specific data rows within the query range, but also locks the gaps between the data rows, preventing the insertion of new data. Although this locking mechanism can effectively solve the data consistency problem in concurrent environments, it will also bring about a concurrent performance degradation, which needs to be weighed in the actual business scenarios to use.
After reading what others have written for so long, organize it yourself and summarize it in a nutshell. Simple and concise

<Previous Next>