Database transaction isolation level

In database operations, the transaction isolation level is proposed to effectively ensure the correctness of Concurrent Data Reading. When the database is used, an error occurs. Update: both transactions are lost, and one row of data is updated at the same time. However, the second transaction fails to exit, causing both changes to the data to fail. This is because the system has not performed any lock operation, so

In database operations, the transaction isolation level is proposed to effectively ensure the correctness of Concurrent Data Reading. When the database is used, an error occurs. Update: both transactions are lost, and one row of data is updated at the same time. However, the second transaction fails to exit, causing both changes to the data to fail. This is because the system has not performed any lock operation, so

Concept

In database operations, the transaction isolation level is proposed to effectively ensure the correctness of Concurrent Data Reading.

Problems encountered during database usage are lost during update

Both transactions update a row of data at the same time, but the second transaction fails to exit, resulting in invalid modification to both data. This is because the system does not execute any lock operations, so concurrent transactions are not isolated.

Dirty read

One Transaction reads the uncommitted data operation results of another transaction. This is quite dangerous, because it is very likely that all operations are rolled back.

Non-repeatable Reads: A transaction Reads data from the same row twice, but different results are obtained. This includes the following situations: (1) After transaction T1 reads a data, transaction T2 modifies it. When transaction T1 reads the data again, it gets a different value from the previous one. (2) Phantom Reads: The transaction performs two queries during the operation, the results of the second query contain data not found in the first query or missing data in the first query (the SQL statements for the two queries are not required to be the same here ). This is because another transaction inserts data during the two queries. Solution: Unauthorized Read is also called Read Uncommitted (Read Uncommitted). Dirty Read is allowed, but update loss is not allowed. If a transaction has started to write data, the write operation of another transaction is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock. For example, the company sent a salary, and the leader sent 5000 yuan to James's account, but the transaction was not submitted, while James went to check the account and found that the salary had been received, which was 5000 yuan, very happy. Unfortunately, the leader found that the amount of salary sent to James was incorrect. It was 2000 yuan, so he quickly rolled back the transaction. After modifying the amount, he committed the transaction, at last, James had a real salary of only 2000 yuan, and James was very happy. The data James first read is dirty data. Authorized Read is also known as Read Committed (Read Committed). It allows repeated Read, but does not allow dirty Read. This can be achieved through "instant shared read lock" and "exclusive write lock. Transactions that read data allow other transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row. Example:

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: Prohibit repeated reading and dirty reading, but sometimes phantom data may appear. This can be achieved through the "shared read lock" and "exclusive write lock. The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions. Example:

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: provides strict transaction isolation. It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently. If transaction serialization is not possible only through the "Row-Level Lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performs the query operation. The higher the isolation level, the more data integrity and consistency can be guaranteed, but the greater the impact on concurrency performance. For most applications, you can set the database system isolation level Read Committed. It can avoid dirty reading and has good concurrency performance. Although it may cause non-repeated read, virtual read, and second-type update loss concurrency problems, in some scenarios where such problems may occur, it can be controlled by applications using pessimistic or optimistic locks.

Below is a summary of the table:

√: Possible occurrence ×: No

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