How to understand database transaction isolation levels and dirty reads, non-repeatable reads, and phantom reads

The content this article brings to you is about how to understand database transaction isolation levels and dirty reads, non-repeatable reads, and phantom reads. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. help.

1. Four basic elements for correct execution of database transactions

1.1ACID principle.

The ACID principle is the four basic elements for the normal execution of database transactions, which refer to atomicity, consistency, independence and durability.

　#　##Atomicity of transactions(Atomicity) means that a transaction is either fully executed or not executed. That is to say, a transaction cannot only be executed halfway and then stop. For example, if you withdraw money from To withdraw money from the machine, this transaction can be divided into two steps: 1. swipe the card, 2. withdraw money. It is impossible to swipe the card but not have the money come out. These two steps must be completed at the same time. Or they are not completed at all.

　#　##Consistency of transactions

(Consistency) means that the operation of the transaction does not change the consistency of the data in the database. For example, the integrity constraint a b=10, if a transaction changes a, then b should also change accordingly. In other words, if A transfers 300 yuan to B, then A's account must be reduced by 300 yuan, and B's account must be increased by 300 yuan. It cannot be said that it is an increase or decrease, such as 200 yuan, etc. This is consistent with the transaction Atomicity, but inconsistent with transaction consistency. In actual business, it is not that simple. It is often similar to the logic of deducting inventory when purchasing something. There is inventory in the main table, inventory in the inventory table, and inventory in the SKU table. Then, due to design flaws, even if transactions are added, it still appears. In addition to the problems of oversold and SKU inventory not matching the total inventory, this means that the consistency is not satisfied. 　#　## Independence

(Isolation): The independence of transactions is also called isolation. It means that two or more transactions will not be executed in an interleaved state, because this may lead to data inconsistency. .

　Persistence

(Durability): Once a transaction is committed or rolled back, this state must be persisted to the database, regardless of read problems that may arise due to isolation.

1.2 Dirty reading, non-repeatable reading, and phantom reading.

---

　Dirty read

(Dirty read): Reading data in one transaction that has not been committed by another transaction. For example, when a transaction is accessing data and makes modifications to the data, but the modifications have not yet been committed to the database, another transaction also accesses the data and then uses the data.

　Non-repeatable read

(NonRepeatable Read): Neither can read the same data content. It means that the same data is read multiple times within a transaction. Before the transaction ends, another transaction also accesses the same data and modifies it. Then, between the two reads of data in the first transaction, due to the For modifications between two transactions, the data read twice by the first transaction may be different.

　Phantom Read

(Phantom Read): In a transaction, the results of the two queries are inconsistent (for the insert operation). It refers to a phenomenon that occurs when transactions are not executed independently. For example, the first transaction modifies the data in a table, and this modification involves all data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a row of new data into the table. Then, if the user who operates the first transaction finds that there are still unmodified data rows in the table, it will be like an illusion.

 For example, an editor changes a document submitted by an author, but when production merges their changes into the master copy of the document, it is discovered that the author has added new, unedited material to the document. This problem can be avoided if no one can add new material to the document until the editors and production department have finished working on the original document.
2. Database transaction isolation levels

---

There are 4 isolation levels for database transactions, from low to high they are Read uncommitted (read uncommitted), Read committed (read committed) , Repeatable read (repeatable read), Serializable (serialization), these four levels can solve the problems of dirty read, non-repeatable read and phantom read one by one.

2.1 Read uncommitted

The company paid wages, and the leader transferred 5,000 yuan to singo's account, but the transaction was not submitted. Singo happened to check his account and found that his salary was 5,000 yuan, which made him very happy. Unfortunately, the leader discovered that the salary paid to singo should be 2,000 yuan, so he quickly rolled back the transaction (rolling back 5,000 yuan), modified the amount (to 2,000 yuan), and submitted the transaction. Finally, singo actually The salary is only 2,000 yuan, and Singo is happy.

The above situation occurs, which is what we call dirty reading, two concurrent transactions, "Transaction A: The leader pays salary to singo", "Transaction B: singo queries the salary account", transaction B reads Transaction A has not yet committed data.

 When the isolation level is set to Read uncommitted (read uncommitted), dirty reads may occur. If we increase the isolation level to Read committed (read committed) at this time, dirty reads can be avoided.

2.2 Read committed

---

 singo took the salary card to make a purchase, and the system read that there was indeed 2,000 yuan in the card. At this time, her wife happened to be transferring money online, and transferred the 2,000 yuan from the singo salary card to another account, and in singo I submitted a transaction before, but when Singo deducted the money, the system checked that there was no money in Singo's salary card, and the deduction failed. Singo was very confused. There was obviously money in the card, but what happened?

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

 When the isolation level is set to Read committed, dirty reads are avoided, but non-repeatable reads may occur (the same data content cannot be read).

The default level of most databases is Read committed, such as Sql Server and Oracle. At this time, if you upgrade the isolation level to Repeatable read, you can avoid dirty reads and non-repeatable reads. Reading happens.

---

2.3 Repeatable read

- When the isolation level is set to Repeatable read, non-repeatable reads can be avoided. When singo takes the salary card to consume, once the system starts to read the salary card information (that is, the transaction starts), singo's wife cannot modify the record, that is, singo's wife cannot transfer money at this time.

　#　## (The examples given by the two blogs here are different, please indicate the reason) In other words, there are two sessions A and B, and two transactions are opened respectively, and then A sends a message to B transferred 500 yuan, A submitted the transaction, and B checked again and found that it was still the original amount. B can only end the current transaction and start a new transaction to query the data changes, thus avoiding non-repeatable reads. . If we set Seriizable (serialization), it is equivalent to locking the table, and only one transaction is allowed to access the table at a certain time.

Although Repeatable read avoids non-repeatable reads, phantom reads may still occur.

For example, Singo’s wife works in the banking department, and she often checks Singo’s credit card consumption records through the bank’s internal system. One day, she was checking that the total consumption amount of singo's credit card that month (select sum(amount) from transaction where month = this month) was 80 yuan. At this time, singo happened to be eating outside and then paying the bill at the cashier. 1,000 yuan, that is, a new consumption record of 1,000 yuan (insert transaction...) was added and the transaction was submitted. Then singo's wife printed the details of singo's credit card consumption for the month on A4 paper, but found that the total consumption was 1,080 yuan. My wife was very surprised and thought she was hallucinating, so the phantom reading happened.

NOTE: The default isolation level of Mysql is Repeatable read.

---

2.4 Serializable Serializable is the highest transaction isolation level, and it is also the most expensive and has very low performance. It is generally rarely used. , at this level, transactions are executed sequentially, which not only avoids dirty reads, non-repeatable reads, but also avoids phantom reads.

---

3. Summary

3.1 Isolation levels and corresponding possible problem tables

Isolation LevelDirty readNonRepeatable ReadPhantom ReadPossiblyPossiblyPossiblyImpossiblePossiblePossibleImpossibleImpossiblePossible## Serialization

Read uncommitted(Read uncommitted)
Read committed(Read committed)
Repeatable read(Repeatable read)
(Serializable)Impossible	Impossible	Impossible

The above is the detailed content of How to understand database transaction isolation levels and dirty reads, non-repeatable reads, and phantom reads. For more information, please follow other related articles on the PHP Chinese website!