If you find the content helpful, give it a thumbs up and encourage you to update 😂.

This article is excerpted from geek Time’s MySQL Practice 45 lecture

LBCC (Single version Control – locking)

Based on the concurrency control of the lock, this solution is relatively simple and crude, that is, when a transaction reads a data, the lock is locked, and no other transactions are allowed to operate (of course, the implementation of the lock is also important, if we only lock the current data still can not solve the illusion problem).

In MySQL transaction, the implementation of lock is related to the isolation level. In the isolation level of RR (Repeatable Read), in order to solve the phantom reading problem, MySQL uses Gap lock to prevent data writing at the cost of sacrificing parallelism. However, this lock has many conflicts due to its insufficient parallelism. Deadlocks are often caused. The current Row mode can avoid many conflicts and even deadlocks, so it is recommended that the isolation level of Row + RC (Read Committed) mode be used by default to greatly improve the Read and write parallelism of the database.

There are three levels of locks in MySQL: page-level, table-level, and row-level. Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low. Occurs in MyISAM, Memory, InnoDB, BDB and other storage engines. Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is minimum, the probability of lock conflict is lowest, and the concurrency is highest. Will happen in the InnoDB storage engine.

Row locks include two types of locks:

  • Shared lock (S) : Multiple transactions can be read together. Shared locks are not mutually exclusive. Shared locks block exclusive locks and can be displayed using the lock in share mode statement.
  • Exclusive lock (X) : Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks of the same data set.

For UPDATE, DELETE, and INSERT statements, InnoDB automatically assigns exclusive locks to the data sets involved, or uses exclusive locks for select for UPDATE displays.

Page-level locking: the overhead and locking time are between table and row locks. Deadlocks occur; The locking granularity is between table and row locks, with moderate concurrency, and occurs in BDB storage engines.

InnoDB row locks

InnoDB row locks are implemented by locking records on index data pages. There are three main implementation algorithms: Record Lock, Gap Lock and next-key Lock.

  • Record Lock: A Lock for a single row Record (data is locked, Gap is not locked).
  • Gap Lock: A Gap Lock that locks a range, excluding the record itself (no data is locked, only the Gap in front of the data is locked).
  • Next-key Lock: Simultaneously locks the data and locks the Gap in front of the data (to solve the phantom read problem).

For example, when you perform select * from t where D =5 for update, you not only add a row lock to the existing database, but also add a gap lock between the records. This ensures that no new records can be inserted. That is to say, in the process of scanning a row, not only will the row lock will be added to the row, but also the gap lock on both sides of the row.

Next – key Lock Lock

A gap lock is a front-open and back-open interval, and a gap lock and a row lock are collectively called next-key locks. Each next-key lock is a front-open and back-closed interval. The operation of “insert a record into this gap” is in conflict with the gap lock, that is, there is no conflict when adding a gap lock to the same interval. The introduction of gap locking and next-key lock helped us solve the illusion problem, but it also brought some “headaches”. Deadlocks occur when each other holds a gap lock that you want to insert records into. And gap locking can cause the same statement to lock a larger range, which actually affects concurrency. The locking rule contains two “rules”, two “optimizations” and a “bug” :

  • Principle 1: The basic unit of locking is next-key lock. I hope you remember that the next-key lock is a front-open and back-closed interval.
  • Principle 2: Only objects accessed during lookup are locked.
  • Optimization 1: equivalent query on index, next-key lock degenerates to row lock when unique index is locked.
  • Optimization 2: the next-key lock degrades to a gap lock when the last value does not meet the equivalence condition.
  • One bug: Range queries on unique indexes access up to the first value that does not meet the criteria.

A deadlock

What are the four conditions for a deadlock to occur?

  • Mutual exclusion: a resource can only be used by one process at a time.
  • Request and hold conditions: when a process is blocked by requesting resources, it holds on to acquired resources.
  • Non-deprivation condition: the process can not forcibly take away the resources it has acquired until they are used up.
  • Circular wait condition: A relationship between multiple processes that circulates wait resources.

How do you avoid deadlocks? Here are some suggestions:

  • Lock sequence is consistent (the most likely to cause lock conflicts, the most likely to affect the concurrency of the lock as far back as possible);
  • Update data based on primary or unique key if possible;
  • The amount of data in a single operation should not be too much, and as few tables are involved.
  • Reduce indexes on tables and reduce resource locking.

Two-pahse Locking — 2PL

The two-phase lock protocol states that all transactions should follow the following rules:

  • Before you can read or write any data, you must first request and obtain a lock on that data
  • After releasing a block, a transaction does not apply for and obtain any other blocks

That is, the execution of a transaction is divided into two phases:

  • The first stage is the capture blockade stage, called the expansion stage
  • The second stage is the release of the blockade, called the contraction stage

First of all, two-phase locking emphasizes the two operations, “locking (growing phase) and unlocking (shrinking phase), and each operation has a phase, which means that no matter how many data items need to be locked in the same transaction, Then all locking operations can only be completed in the same phase, in this phase, it is not allowed to unlock the locked data items, that is, locking and unlocking operations cannot be performed interleaved (within the same transaction). This article refers to things within the same transaction. In InnoDB transactions, row locks are added when they are needed, but are not released immediately when they are not needed, but when the transaction ends.

MVCC (Multi-version Control)

Is there a way to solve the problem of unrepeatable and illusory reads by using optimistic locks instead of locking? In fact, THE MVCC mechanism is designed to solve this problem. It can replace row-level locking in most cases and reduce the overhead of the system.

Concurrency Control MVCC is a multi-version Concurrency Control technology. As the name suggests, MVCC implements database concurrency control through multiple versioning of rows. The idea is simply to save historical versions of data. This allows us to determine whether the data is displayed by comparing the version numbers. With MVCC we can solve the following problems:

  • The problem of blocking between read and write, through MVCC can make read and write do not block each other, that is, read does not block write, write does not block read, so that can improve the transaction concurrent processing ability.
  • Reduced the probability of deadlock. This is because MVCC uses an optimistic locking approach, which does not require a lock to read data and locks only the necessary rows for write operations.
  • Resolve consistent read issues. Consistent read is also called snapshot read. When we query the snapshot of the database at a certain point in time, we can only see the update result of the transaction committed before this point in time, but not the update result of the transaction committed after this point in time.

What is illusory?

At the repeatable read isolation level, normal queries are snapshot reads and do not see data inserted by other transactions. Therefore, illusion only appears under “current read”. In two concurrent sessions, the result of session B’s modification is seen with “current read” by the SELECT statement following session A, and cannot be called phantom read. Phantom only refers to “newly inserted rows”.

Snapshot read and current read

In MVCC concurrency control, Read operations can be divided into two types: Snapshot Read and Current Read. LBCC solves the illusion of the current read, while MVCC solves the illusion of the ordinary read (snapshot read). Snapshot read: Reads the visible (and possibly historical) version of the record without locking. Current read: The latest version of the record is read, and the records returned by the current read are locked to ensure that other transactions will not modify the record concurrently. Note: MVCC only works at Read Commited and Repeatable Read isolation levels.

How do I distinguish snapshot reads from current reads?

The value can be as follows: Snapshot read: A simple select operation is snapshot read and does not need to be locked. Current read: special read operations, such as insert/update/delete operations, belong to current read and need to be locked.