Understand the 4 isolation levels of a database

This article transferred from: http://m.blog.csdn.net/article/details?id=51924963

There are 4 isolation levels for database transactions, from low to high to read uncommitted, Read committed, Repeatable read, and Serializable. Also, dirty reads, non-repeatable reads, and Phantom reads can occur in concurrent operations of a transaction. The concepts and linkages are described below through case one by one.

Read UNCOMMITTED

READ UNCOMMITTED, as the name implies, is a transaction can read another uncommitted transaction data.

Case: The boss will pay the programmer, the programmer's salary is 36,000/month. But pay when the boss accidentally pressed the wrong number, according to 39,000/month, the money has been hit the programmer's account, but the transaction has not been submitted, just then, the programmer to check their salary this month, found that more than usual 3,000 yuan, thought the pay rise is very happy. But the boss in time to find out the wrong, immediately roll back almost on the submission of the transaction, the number is changed to 36,000 before submission.

Analysis: The actual programmer this month's salary is still 36,000, but the programmer sees is 39,000. What he sees is the data that the boss has not yet committed to the transaction. This is dirty reading .

How to solve the dirty reading? Read committed! Read commit, can solve dirty read problem.

Read committed

Read Commit, as the name implies, is a transaction to wait for another transaction to be committed before the data can be read.

Example: A programmer with a credit card to enjoy life (Cary is only 36,000), when he paid the bill (Programmer Affairs Open), the charge system in advance detected his card has 36,000, at this time!! The programmer's wife is going to turn all the money out to act as a home and submit. When the charging system is ready to deduct, then check the amount of the card, found that there is no money (the second Test amount of course to wait for the wife to transfer the amount of the transaction submitted ). Programmers will be very depressed, obviously Cary is rich ...

Analysis: This is the read commit, if there is a transaction to update the data (update) operation, read operations to wait for the update operation transaction committed before the data can be read to resolve the dirty read problem. But in this case, there is a transaction within the scope of two identical queries but return different data , which is not repeatable read .

How to solve the possible non-repeatable reading problem? REPEATABLE READ!

REPEATABLE READ

Repeat read, which is no longer allowed to modify operations when starting to read data (transaction on)

Example: A programmer with a credit card to enjoy life (Cary is only 36,000), when he paid the bill ( transaction Open, do not allow other transactions update modification operation ), the system has detected in advance that his card has 36,000. His wife can't turn out the money at this time. Then the charging system will be able to deduct money.

Analysis: Repeated reading can solve non-repeatable reading problems. To write here, it should be understood that the non-repeatable reading corresponds to the modification, that is, the update operation. But there may also be Phantom reading problems. Because the Phantom read problem corresponds to inserting an insert operation, not an update operation .

When will the Phantom read occur?

Example: One day the programmer spent 2000 yuan, then his wife went to see his today's consumption record (full table scan FTS, wife affairs Open), see really spent 2000 yuan, at this time, the programmer spent 10,000 to buy a computer, that is, added insert a consumer record , and submit. When the wife prints the programmer's List of consumption records (the wife's business submission), it was found that it took 12,000 yuan to appear to be hallucinating, which is the Phantom reading.

How to solve the problem of phantom reading? serializable!

Serialization of Serializable

Serializable is the highest transaction isolation level at which transaction serialization is performed in order to avoid dirty reads, non-repeatable reads, and Phantom reads. However, this kind of transaction isolation level is inefficient and consumes database performance, which is generally not used.

It is worth mentioning that the default transaction isolation level for most databases is read committed, such as SQL Server, Oracle. The default isolation level for MySQL is repeatable read.

Understand the 4 isolation levels of a database