Database transaction isolation level and dirty read, phantom read, non-repeated read, transaction level

Database transaction isolation level and dirty read, phantom read, non-repeated read, transaction level

I. database transaction isolation level

 

There are four database transaction isolation levels, from low to high: Read uncommitted, Read committed, Repeatable read, Serializable, these four levels can solve dirty reads, non-repeated reads, and Phantom reads one by one.

√: Possible occurrence ×: No

	Dirty read	Non-repeated read	Phantom read
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

 

Note: we will discuss isolation-level scenarios, mainly in the case of multiple transaction concurrency. Therefore, the next sections will focus on transaction concurrency.

Read uncommitted

When the company paid the salary, the leader sent 5000 yuan to the singo account, but the transaction was not submitted, while singo went to view the account and found that the salary had been paid, which was 5000 yuan, very prosperous. Unfortunately, the lead found that the amount of salary sent to singo was incorrect. It was 2000 yuan, so he quickly rolled back the transaction. After modifying the amount, he committed the transaction, the actual salary of singo is only 2000 yuan, so singo is happy.

 

In the above situation, we are talking about dirty reads, two concurrent transactions, "transaction A: Lead pays for singo", and "transaction B: singo queries the wage account ", transaction B reads data not committed by transaction.

When the isolation level is set to Read uncommitted, dirty reads may occur. For how to avoid dirty reads, see the next isolation level.

Read committed Read submission

Singo uses a payroll card for consumption. The system reads 2000 yuan from the card, and her wife transfers the 2000 yuan from the singo payroll card to another account, A transaction was submitted before singo. When singo deducts money, the system Checked that singo's payroll card had no money and the deduction failed. singo was very puzzled and clearly had money in the card, why ......

The above situation occurs, that is, what we call non-repeated reads, two concurrent transactions, "transaction A: singo consumption", and "transaction B: singo's wife online transfer ", transaction A reads the data in advance, and transaction B updates the data immediately and commits the transaction. When transaction A reads the data again, the data has changed.

When the isolation level is set to Read committed, dirty reads are avoided, but non-repeated reads may occur.

The default level of most databases is Read committed, such as SQL Server and Oracle. For how to solve the problem of non-repeated reading, see the next isolation level.

Repeatable read

When the isolation level is set to Repeatable read, repeated read can be avoided. When singo uses a payroll card for consumption, once the system starts to read the payroll 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.

Although Repeatable read avoids repeated reads, Phantom reads may occur.

Singo's wife works in the Banking Department. She often checks singo's credit card purchase records through the internal banking system. One day, she was checking that singo's total credit card consumption amount for the month (select sum (amount) from transaction where month = this month) was 80 yuan, while singo pays the bill at the cashier right after eating haicai outside, consuming 1000 yuan, that is, adding a 1000 yuan purchase record (insert transaction ...), and submitted the transaction, then singo's wife printed the details of singo's credit card consumption for the current month on A4 paper, but found that the total consumption was 1080 yuan, singo's wife was very surprised, the phantom read is like this.

Note: The default isolation level of MySQL is Repeatable read.

Serializable serialization

Serializable is the highest level of transaction isolation, with the highest cost and low performance. It is rarely used. At this level, transaction execution can avoid dirty reads and non-repeated reads, it also avoids phantom reading.

 

II,Dirty read, phantom read, and non-repeated read

1. Dirty read:
Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used.

2. Non-repeated read:
A transaction reads the same data multiple times. When the transaction is not completed, another transaction also accesses the same data. Therefore, the data read twice in the first transaction may be different because of the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called non-repeated read. (That is, you cannot read the same data content)
For example, an editor reads the same document twice, but the author overwrites the document between the two reads. The document has been changed when the editor reads the document for the second time. The original reads cannot be repeated. This issue can be avoided if the editor can read the document only after the author has completed writing.

3. Phantom read:
It refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows not modified in the table, just like
Like an illusion.
For example, an editor changes the document submitted by the author, but when the production department merges the changes into the primary copy of the document, the author has added unedited new materials to this document. This issue can be avoided if no one can add new materials to the document before the editors and production departments process the original document.