This article is shared by Huawei Cloud community “9 locks, 3 reads, and 4 isolation levels in a series, using 15 diagrams to show the concurrency principle behind database transactions” by breakDawn.
Some time ago, DURING the development, I happened to encounter a bug caused by two processes updating a row of records at the same time. Although the problem was finally solved, I still had no idea about the operation logic behind it. After a brief look at various blogs, as well as the book “High Performance mysql”, I found that most of them were a bunch of eight-essay concepts stacked together, and few of them were connected completely.
So I relearned all of these concepts and underlying principles, and connected them all through a transfer problem scenario.
The following database concepts are familiar or memorized by most people when taken separately, but they can be confusing when combined. Here’s an example:
- Exclusive lock, shared lock
- Row lock, table lock, intention lock, gap lock, next-key lock
- Pessimistic lock, optimistic lock
- Two phase lock protocol
- LCBB lock concurrency control protocol, MVCC multi-version control protocol
- Dirty read, unrepeatable read, phantom read
- RU\RC\RR\SE Isolation level
Then ask yourself a question:
- What is the relationship between these locks?
- How exactly do isolation levels use locks +MVCC to solve transaction read problems?
First of all, we ignore the database engine, isolation level Settings, and so on, as if you were storing and updating data with a very rudimentary pediatric level database. Suppose your mall service does exactly two things at the same time
- Zhang SAN transfers 100 yuan to poor Li Si.
- Li Si tries to order clothes worth 100 yuan
Li Si had a balance of 0 yuan at the beginning. Note that since the execution is simultaneous, without any protection, a situation like the following may occur:
It can be seen that Li Si clearly has no money, but deduct the fee, become very strange -100 yuan.
Q: What is the name of the reading process in question?
A: This process is called dirty reading. That is, when the update is rolled back, another transaction reads dirty data and misjudges it, resulting in incorrect processing.
The root cause is that the two transactions are checked first and then deducted, but there is no form of advance protection
Q: How can we manually resolve this dirty read with SQL statements without changing the database isolation level?
A: It is obvious that locking protects the transaction process in advance and prevents B from judging and deducting fees.
SQL > lock (‘ for update ‘, ‘commit’); SQL > lock (‘ for update ‘);
Q: I just saw “lock li Si line”, so this is called row level lock. When is it possible to lock the entire table?
A: Name =’ li si ‘; if name =’ li si ‘, we will add row lock. If it is not an index column, it becomes a table lock. In other words, the essence of a row lock is to lock an index node. If you can’t lock an index node, you can lock the entire table, which can be costly. Table locks can also be manually locked using the lock table syntax.
Q: If transaction A applies for A row lock and another transaction B applies for A table lock, will TRANSACTION B be blocked?
A: B transaction requires A lock, and the description may use each line in A. B The application process can be as follows:
- Determines whether the table has been locked by another transaction
- Determines whether each row in the table is locked by a row lock.
But 2 is too long. Therefore, before applying for row lock, A will first apply for an intention lock and then apply for row lock. Then, when B applies, the second step is to judge the intent lock, and the intent lock will be blocked.
Simply put, an intent lock is a row lock operation used to block a table lock. But row locks and row locks do not block each other unless rows conflict.
The for update we just saw restricts all reads and writes to other parallel transactions, and adds “for update” to both transactions. This lock is called an “exclusive lock” and is a very strong lock that blocks all other read and write operations at once.
Here the use of exclusive lock to solve the dirty read reason is because there is a query balance + deduct balance code, write this code must do advance protection, in order to avoid their own read a may be modified data, resulting in judgment and modification errors.
The opposite of an exclusive lock is a “shared lock,” also known as a read-write lock. Multiple transactions can be read simultaneously, but modification is not allowed. To manually add a shared lock, change for update to lock in share mode.
Q: When is a shared lock better than an exclusive lock?
A: Look at the following examples:
It can be seen that there is no operation of checking and updating itself, but only checking and updating other tables. The tables are not related to each other, and the real-time requirement of balance is not too high.
- If all exclusive locks are added, various select operations will be slow.
- However, if you do not add a shared lock, you may create redundant data when deleting T6, so you still need to add a lock.
Q: When will shared locks (S locks) and exclusive locks (X locks) be released? Release immediately after each update?
A: This involves the “two-stage lock” protocol.
- Lock stage: Locks can be performed in this stage. An S lock is acquired before a read operation is performed on any data (shared locks can continue to be acquired by other transactions, but not exclusive locks) and an X lock is acquired before a write operation (exclusive locks cannot be acquired by other transactions). If the lock fails, the transaction enters the wait state and does not continue until the lock succeeds.
- Unlock phase: After a transaction has released a lock, the transaction enters the unlock phase. During this phase, the transaction can only be unlocked but cannot be locked.
In human terms, a transaction is locked when it needs to be locked until it is unlocked once the commit is complete.
If all concurrently executed transactions comply with the two-lock protocol, then any concurrent scheduling strategy for those transactions is serializable.
Q: Can the two-phase locking protocol avoid deadlocks?
A: It cannot be avoided, but transaction undoing can be done through deadlock detection algorithms.
Let’s go back to the triple Four transfer + order scenario. For update is also a pessimistic lock, which takes time to unlock. By default, contention often occurs. However, if my transfer and order process is very fast, only a few milliseconds each time, then the cost of adding pessimistic lock is too large. At this time, you can manually use optimistic lock. You need to add version column to the balance table, and the increase will be as follows:
In this way, there is no need to specially lock each time. The premise is that the probability of conflict is relatively low and the blocking time is relatively short.
Just a little dirty reading, has solved the following three problems:
- The difference between an exclusive lock and a shared lock is that the former denies all reads and writes while the latter allows concurrent reads and writes.
- The difference between a row lock and a table lock: the former is to lock a single row, the latter is to lock the whole table, the difference is whether or not the index is involved.
- The difference between pessimistic locking and optimistic locking is that the former actively uses the database’s own lock, while the latter adds its own version number and a two-phase locking protocol.
Going back to the dirty read problem, all of the concepts we learned earlier are independent of the isolation level of the database itself and can be avoided by using the locking syntax of the database or the version version number.
But as databases grow so powerful, there is no need to write this kind of complex logic ourselves too often, so isolation level Settings were born.
The isolation level at which dirty reads occur is called RU (read uncommited). At the RU level, I can read data while other transactions are not fully committed.
Q: Quick question, RU level doesn’t have any locks, right?
A: Error, RU level update operation, such as add, delete, or modify operation, still default transaction update operation add exclusive lock, avoid update conflict. Remember that the cause of dirty read is that no lock is added during query, update, and rollback, resulting in errors in other query operations. That is, the query block may read uncommitted data and cause errors rather than update concurrency issues.
Q: Dirty reads can be resolved when our database is set to RC (Read commited), but what is behind the fix?
A: There are two ways in the industry
- LBCC Concurrency Control (Lock-based Concurrency Control)
- Multi-version Concurrency Control for MVCC
LBCC is similar to the pessimistic lock used by the previous hand. The default lock is attempted during a transaction, so it can be blocked by an update exclusive lock, avoiding dirty reads.
But the price is inefficiency. In many cases, the number of select is much higher than that of update.
So InnoDb, based on the concept of optimistic locking, came up with an MVCC and implemented a set of similar optimistic locking mechanism behind the transaction to handle this situation. Ensures that locks are not placed on read operations as much as possible, and exclusive locks only apply to update operations.
Q: How does MVCC work?
TRX_ID and ROLL_BT are added to each row by default. TRX_ID and ROLL_BT are added to each row by default.
In a nutshell
- Only the records before the current transaction are checked or the deleted records whose version is older than the current transaction are rolled back.
- When adding, add a new version of the record
- When deleting, mark the old record as the rollback version
- When you change, you essentially add a new record and mark the old record with a rollback version
Q: What is snapshot read and current read in MVCC?
A:
- Snapshot read: For the select read operation, no lock is added by default and historical version data is used.
- Current read: For INSERT, UPDATE, and DELETE operations, an X lock is still required because data changes are involved and must be modified using the latest data
Q: So back to the dirty read problem, how does MVCC solve the dirty read problem without locking?
A: First of all, each select does not use any lock, each snapshot read, no blocking, so it will look like this:
To summarize the graph, it is:
- On each read, a readView is generated to record the version number of the transaction that has not yet been committed.
- Find records that are smaller than your current version and not in the readView collection, based on your transaction version number, Version.
This ensures that the data read must have been committed. Isn’t that easy?
Q: If transaction B does not make balance judgment and supports direct credit + deduction, will it result in deduction first and then rollback to 0?
A: No. As mentioned above, update operations in MVCC are “currently read”, still require an X lock, and because data changes are involved, they must be modified using the latest data version.
Update (); update (); update ();
Q: What are the pitfalls of this process
A: If the balance of A transaction is read for two consecutive times, there may be the risk of “unrepeatable read”, that is, the data read before and after is inconsistent as shown below:
So the RC isolation level does not solve the “unrepeatable read problem”
Q: How do RR (repeatable, Repeat Read) isolation levels solve this problem?
A: This is essentially the difference when A readView is generated. As you can see in the RC non-repeatable read figure above, the latest readView is fetched each time a read is made. This may result in changes to the readView collection observed by transaction B after transaction A commits.
So RR changes the way the readView is generated by using only the readView that transaction B gets at the beginning of each read, so that only the old data is fetched forever.
Q: What is the illusory reading in the reading question?
A: The “unrepeatable read” is the result of two queries in A transaction, and the values do not match. And “illusion”, is a transaction to query two batches of results, find the two batches of results do not match, as if there is an illusion. As shown below:
Q: Does the MVCC mechanism in RR isolation level solve the above problem?
A: It can be solved. By querying snapshot data, you can ensure that only the same batch of data can be queried.
Q: What if transaction A updates twice in A row, like the following? Can MVCC alone avoid the illusion of the update operation?
A: If you only rely on THE MVCC, you can’t avoid it because the update operation is “current read”. This will cause the read operation in the update operation to be unreal, and the number of records to be updated before and after the update is different.
Q: How does the database handle the illusion of an insert between two updeTes?
A: The update process will still be locked, RR level will enable A “Gap lock” to prevent this. Update XXX where name =’ update XXX ‘; update XXX where name =’ update XXX ‘; update XXX where name =’ update XXX ‘; update XXX where name =’ update XXX ‘;
As you can see, the insert process of order D is blocked by the gap lock of the UPDATE process and cannot be inserted until the end of the transaction. Therefore, phantom reads between updates in a transaction can be avoided and can be.
Q: What is the difference between a row lock, a gap lock, and a next-key lock?
A: A row lock is A single row (single index node) lock. A gap lock is a lock between rows (index nodes). A next-key is a row lock and a gap lock.
Q: If the name field is not an index, but a normal field, how will the gap lock be added?
A: That would lock all the gaps in the table! Because the database cannot confirm which range it is, it simply adds all. This will result in the whole table locking and poor performance.
Q: If name is an index, the entire table will not be gap-locked.
A: If the where condition is written incorrectly, it will cause the index to fail and the entire table to be locked.
Q: RR can resolve illusions between two select and update attempts.
A: I did A lot of research on this problem and finally found A reasonable explanation. Look at the following scene:
Transaction B’s insert occurred before transaction A’s update. Therefore transaction B’s INSERT operation is not blocked by a gap lock.
Update uses the current read, so the number of updates does not match the number of original select.
Mysql official phantom reads the explanation is: as long as in a transaction, the second row even more select phantom read, so this scenario, calculate in reading.
That’s where this graph comes from:
Q: How can serializable isolation level be serialized to avoid phantom reads?
A: At the Se level, MVCC concurrency control degrades to lock-based concurrency control (LCBB). There is no difference between snapshot read and current read. All read operations are current read, read plus read lock (S lock), write plus write lock (X lock). At this isolation level, read and write conflicts occur, so concurrency performance deteriorates dramatically and is not recommended in MySQL/InnoDB.
This is the manual locking process at the beginning of this article.
Click to follow, the first time to learn about Huawei cloud fresh technology ~