ACID, isolation level, MVCC, lock, etc.

• First: Concurrency problems and isolation levels mainly focus on the problems and ideas to be solved by transactions. First, understand why transactions are needed and the problems faced in transaction concurrency control.
• Bottom: Isolation level implementation – MVCC isolation, the lock is in order to better achieve concurrency control, concurrent performance will have a direct influence on the business transaction, so this article will speak in detail how to implement segregation, mainly about two kinds of mainstream technology solutions – MVCC and lock, understand the MVCC and lock, can extrapolate to see various database concurrency control scheme, Understand the problems each implementation solves and the concurrency issues that require the developer’s own attention to better support business development.

Before the beginning of the article to give a small thought, consider a situation: like the following implementation of User withdrawal 100 yuan, whether there will be no problem?

1. Start Transaction
2. SELECT balance FROM users WHERE user_name=x; (This read is in Transaction)
3. Determine whether balance is greater than or equal to 100 in the code
4. If less than $100, End Transaction and return insufficient balance withdrawal failure
5. UPDATE users SET balance = balance-100 WHERE user_name=x; Then Commit Transaction returns a successful withdrawal

If you already know enough about database transactions to know what goes wrong and why, this article is too introductory for you to continue reading. If you are not clear, I hope you will understand after reading the first and second chapters. Otherwise, I write too badly.

1. Reunderstand ACID

1. Problems in data operations

All solutions in the technology must be to solve specific problems, first understand the problem and then look at the solution, it is easier to learn, understand more in-depth, so start from the problems faced by the database. First of all, to understand why the database will have the demand of the transaction, to understand the database first to solve the fundamental problem is not storage, file system storage problem has been solved, the purpose of a database is how to help developers are more reliable, more quickly, more convenient to use storage, better help developers to complete the business, the business in a high frequency requirements are: There is a sequence of operations that either all succeed, or they don’t happen at all, and don’t result in dirty data because some of them didn’t. If there is no transaction, we need a lot of code to handle all kinds of errors, clean up all kinds of dirty data and avoid possible bugs, such as the successful order but no deduction due to database breakdown. To improve development efficiency and reduce development costs, a database needs to provide a guarantee that a set of operations can be treated as a unit, which can all succeed, and in the case of partial failure, can be rolled back completely as if nothing had happened. This set of operations is called a Transaction. But it is not enough to do that, because it needs a simple, actually introduced another problem, please pay attention to the point – “a set of operations”, maybe there are multiple independent operation in the transaction, they combine into a set of operations, understand multithreaded programming students will think, this would be a classic concurrency issues, Without concurrency control across multiple transactions, unexpected results can occur that consumers do not want.

To summarize, the problems faced in data manipulation:

1. How to view a set of operations as a whole and either all succeed or all roll back.
2. How can it be concurrency controlled in a way that satisfies the previous requirement without unexpected results?

2. What do we need: ACID

ACID refers to the four characteristics that a transaction must have to ensure that it is correct and reliable.

Atomicity in transactions is a property that is often misunderstood because it doesn’t mean Atomicity in the usual context. Most of the time it means an instruction that is indivisible and impervious to interruption. Such as reading the value of a memory address, writing the value back to the memory address, redis SETNX (Set if not exists), these operations conform to what we often call atomicity. Atomicity in a transaction, however, does not mean that a transaction is impervious to interrupts. It simply means that all operations in a transaction should be treated as an indivisible set of instructions, none of which can stand on its own and either all execute successfully or none (i.e., rollback). There are also many misunderstandings about successful execution. Successful execution refers to the database level, not the business level. For example, when the customer buys product A, the merchant just takes the product off the shelves. Update products set price=100 where product_id=A and status= 0 update products set price=100 where product_id=A and status= 0 Calculate! If the number of rows is 0, the database returns a normal result, which is a failure on the service level, but a success on the database level. In this case, the database will not be rolled back. The programmer needs to determine the number of rows to be updated. If the database is down or fails due to hardware or system problems, the command is considered to have failed. In this case, the database will retry or simply roll back, and the error will be returned to the developer. Atomicity not only ensures transaction reliability for developers (no dirty data due to database errors), but also provides business convenience by allowing developers to manually roll back.

2. The term Consistency is also quite confusing. Unlike the “Consistency” used in master-slave database replication, which refers to whether multiple copies are synchronized and have the same data, Consistency refers to whether transactions produce unexpected intermediate states or results. For example, dirty reads and unrepeatable reads produce unexpected intermediate states, while dirty writes and lost changes produce unexpected results. Consistency is actually further guaranteed by the following isolation. If isolation meets the requirements, consistency can be satisfied. That is, inadequate isolation can lead to transactions that do not meet consistency requirements, so it is important to understand the isolation levels so that fewer bugs can be written.

In simple terms, Isolation means that multiple transactions do not affect each other and the existence of each other is not felt. This feature is used for concurrency control. In multithreaded programming, if everyone is reading and writing the same piece of data, then it is possible to end up with inconsistent data over time, meaning that each thread may be affected by another thread. In theory, the strictest implementation of isolation is to be completely unaware of the existence of other concurrent transactions, and multiple concurrent transactions will result in the same result as serial execution no matter how scheduled. In order to achieve serial effect, the current method is generally Two Phase Locking, but the efficiency of both reading and writing is very low, and the reading and writing can only be executed in line. Sometimes, the principle can be compromised for efficiency, so the isolation is not strict, which is divided into various levels from high to low:

• ⬇️ Serializable
• ⬇️ Read Repeatable
• ⬇️ Read Committed
• ⬇️ Read Uncommitted

Each level is just a guideline, and each database implements it differently. Some databases implement Read Repeatable at Read Committed level, while others do not provide Read Uncommitted level at all. When the isolation level is Serializable, the transaction feels like a completely atomic operation, unaffected by any interruptions or concurrency. Most database isolation levels are not Serializable. The default isolation level is Read Repeatable or Read Committed. It is easy to write code that causes data inconsistency at the business level if you program with serializable thinking and use isolation levels lower than serializable, so it is important for developers to understand the isolation levels and their principles to better support business development. Isolation levels and their implementation are described in more detail below.

If a transaction is committed successfully, the modification to the data is permanent and will not be lost even if the system fails. The failure here is only a general error such as downtime, system Bug, power outage. If the hard disk is damaged, there is no way to prevent data loss.

Concurrency issues and isolation levels

Before discussing the implementation of the individual isolation levels, let’s look at the problems that can result from insufficient isolation when transactions are executed concurrently.

Dirty Write

The uncommitted transaction writes to data written by another uncommitted transaction, which is called dirty write. For example, two concurrent transactions A and B, A writes X, B writes X before A commits, and THEN A commits, even though B has not committed, A also finds that its written X is missing.

Many parts written in “cover” to describe dirty, but I didn’t feel very fit, because coverage suggested that a chain, wrote a transaction data, submitted in yesterday, today have affairs to write the same data, you can call it covered, yesterday’s data become history, but it is not dirty, so more suitable for the description may be “erase”, The transaction finds that its commit has been erased as if it did not exist. Dirty writes are something that a transaction must not allow, so they must not be allowed regardless of the isolation level.

Dirty Read

Because of the rollback nature of the transaction, any read or write before the commit is likely to be undone. If something reads a write from an uncommitted transaction and then rolls back, it will read dirty data because it no longer exists.

Non-repeatable Read

Transaction A reads A value, but does not modify it. Another concurrent transaction B changes the value and commits it. Transaction A reads the value again and finds that it is not the first value read by transaction B, but the value changed by transaction B. The first time I read it, I didn’t do anything, and the next time I read it, it might change.

Phantom

Much like unrepeatable reads, transaction A queries the value of A range, another concurrent transaction B inserts data into that range and commits it, and then transaction A queries the same range and finds an extra record, or A record is deleted by another transaction, and transaction A finds that A record is missing.

Phantom reads are easily confused with non-repeatable reads. The only difference is to remember that non-repeatable reads are “the same record” and phantom reads are “the same range.” Although MVCC uses snapshots to prevent unrepeatable reads, it still does not avoid phantom reads. Phantom reads need to be solved by scope locking. You may wonder why snapshot reads cannot avoid phantom reads, which will be covered in the next article.

The SQL standard specifies what problems are allowed for each isolation level:

Read bias (Read Skew)

Skew can be understood as inconsistent, so read deviation can be understood as the result consistency in violation of the business, such as X, Y, both account balance of 50, adding up to 100, their transaction A reading of 50 X balance, then the transaction from 50 X transfer to B Y and then submit, transaction A balance found in B submitted to read Y to 100, then they are combined into A 150, Service consistency is violated.

Write Skew

Written deviation can be understood as a transaction commit before writing the premise are destroyed, cause in the violation of the consistency of business data, have a good online abbreviation for a premise, that is to read some data, as the premise condition of other written, but before the submission, read the data has been modified and submitted by other affairs, the affairs didn’t know it, It then commits some of its own writes, whose write premises are modified before the commit, causing the write results to violate business consistency. Writing bias occurs when the writing premise is different from the writing target. This is one of the most error-prone areas in business development, and if the developer does not understand the isolation level well and does not know which isolation level is currently being used, it is very likely that the code will be written incorrectly, resulting in business inconsistencies. For example, the credit card system has bonus points for members of different levels, but for members of level 3, the bonus points will be tripled every time. Meanwhile, there will be a scheduled task to check that when the points do not meet the requirements, they will be downgraded. Member first, has carried on the brush calorie of consumption, at this time to compute the integral, open the transaction A, read member of grade 3, at the same time timing task began, read loyalty points for 2800, not content 3000 points should be downgraded to A level 2, then the member level to 2 and commit, due to issues A read of grade 3, It still adds points to the member at 3x points, and the member earns, thanks to the programmer not understanding the transaction isolation level he is using, there is a business inconsistency.

Lost Updates

Because uncommitted transactions cannot see each other’s changes, they update the same data with the same old premise, resulting in the final commit with the wrong value. Suppose there is an Alipay account X with A balance of 100 yuan, and transaction A and B recharge 10 yuan and 20 yuan respectively to X at the same time. The final result should be 130 yuan, but due to the loss of update, the final result is 110 yuan.

The lost update is similar to the write bias, because the write premise is changed. The difference is that the lost update is the final inconsistency in the same data, while the conflict of the write bias is the final inconsistency in different data

All of the issues covered in this article will be addressed in the next article on isolation level implementation. Understanding isolation level implementation can help you choose the appropriate isolation level or consciously avoid the problems caused by inadequate isolation level at the code level.

The resources

• How MySQL Works: Understanding MySQL from the Root
• The Art of Database Transaction Processing: Transaction Management and Concurrency Control
• Database transactions, Isolation levels, and locks (blog)
• SQL Isolation Levels (blog)
• Database Isolation Levels All Developers should Know (blog)
• A Beginner’s Guide to Read and write Skew Phenomena