Database transaction ISOLATION LEVEL-dirty read, Phantom Read, non-repeatable read (Clear explanation)

I. Database transaction ISOLATION LEVEL

There are 4 isolation levels for database transactions, from low to high to read uncommitted, Read committed, Repeatable read, and Serializable, which can solve the problems of dirty reading, non-repeatable reading, and Phantom reading one by one.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

Note: We discuss the isolation level scenario, mainly in the case of multiple transactions concurrency, so the next explanation is around the transaction concurrency. READ UNCOMMITTED not submitted

The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to read UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level. Read Committed reading commit

Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...

The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.

When the isolation level is set to read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level. Repeatable READ Repeat

You can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is Singo wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have phantom reads.

Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ), and submitted a transaction, then Singo's wife will singo the current month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of such a generation.

Note: The default isolation level for MySQL is repeatable read. Serialization of Serializable

Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.

Second, Dirty reading, phantom reading, non-repeatable reading

1. Dirty reads:
Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data.

2. Non-repeatable reading:
is to read the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read. (That is, you cannot read the same data content)
For example, an editor reads the same document two times, but between two reads, the author rewrites the document. When the editor reads the document for the second time, the document has changed. The original read is not repeatable. You can avoid this problem if the editor can read the document only after the author has finished writing it all.

3. Phantom reading:
A phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then, the user who operates the first transaction in the future will see that there are no modified rows of data in the table, as if
There was an illusion.
For example, an editor changes the document submitted by the author, but when the production department merges its changes into the primary copy of the document, it finds that the author has added the unedited new material to the document. This problem can be avoided if no one is able to add new material to the document until the editor and production department have finished processing the original document.