Transaction nature and isolation level, transaction nature isolation level

Transaction nature and isolation level, transaction nature isolation level
Transaction concept Transaction is the basic unit of concurrency control. A transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable unit of work. Transactions are proposed mainly to solve the problem of maintaining data consistency in the case of concurrency. Basic Features of transactions

• Atomic (Atomicity): the operations contained in a transaction are considered as a logical unit. The operations in this logical unit either succeed or fail.
• Consistentcy (consistency): only valid data can be written to the database, otherwise the transaction should roll back to the initial state.
• Isolation: Transactions allow multiple users to concurrently access the same data without compromising data correctness and integrity. At the same time, modifications to parallel transactions must be independent from those of other parallel transactions.
• Durability (Durability): after the transaction ends, the transaction processing result must be solidified.

The four basic features of ACID can also be called Four attributes: ① Atomicity: All elements in a transaction are committed or rolled back as a whole, the transaction elements are inseparable, and the transaction is a complete operation.
② Consistemcy: when a transaction is completed, the data must be consistent, that is, the data stored in the data is in the same state before the transaction starts. Ensure data loss.
③ Isolation: multiple transactions that modify data are isolated from each other. This indicates that the transaction must be independent and should not affect other transactions in any way.
④ Durability: after the transaction is completed, its impact on the system is permanent. This modification will be retained even if a system failure occurs, and the database is actually modified.
There are four isolation levels for the transaction isolation level database: from low to high is 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, so I was very happy. 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 fee deduction failed. singo wondered why the card had money ......

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 ...), after submitting the transaction, 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 surprised and thought that there was an illusion, phantom read is generated in this way.

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.