Database isolation level, database isolation level

Database isolation level, database 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, 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.

[SQL]View plaincopy

1. For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not adopted, various concurrency problems will occur:
2. • Dirty read: For two things T1, T2, T1 read fields that have been updated by T2 but not submitted. if T2 is rolled back, the content read by T1 is temporary and invalid.
3. • Repeatable reading: a field is read for T1, T2, and T1, and then T2. after T2. T1. then, T1.
4. • Phantom read: For two things T1, T2, T1 read a field from a table, and T2 inserts some new rows in the table. then, if T1 reads the same table again, there will be several more rows.
5. Database transaction isolation: the database system must be able to isolate and concurrently run various transactions so that they do not affect each other and avoid various concurrency problems.
6. The degree to which a transaction is isolated from other transactions is called the isolation level. the database specifies multiple transaction isolation levels. Different isolation levels correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.
7. The database provides the following isolation levels:
8. Isolation level description
9. Read uncommitted (read uncommitted data) allows the transaction to READ changes that have not been committed by other transactions. problems such as dirty reads, non-repeated reads, and Phantom reads may occur.
10. Read commited (READ committed data) only allows the transaction to READ the changes that have been committed by other transactions, which can avoid dirty reads, but the Repeatable READ and phantom READ problems still occur.
11. Repeatable read (repeatable read) ensures that the transaction can READ the same value from a field multiple times. during the duration of the transaction, other transactions are prohibited from updating the field, it can avoid dirty reads and non-repeated reads, but Phantom reads still exist.
13. SERIALIZABLE ensures that the transaction can read the same row from a table. During this transaction, other transactions are prohibited from performing insert, update, and delete operations on the table, all concurrency problems can be avoided, but the performance is very low
14. Two transaction isolation levels supported by Oracle: read commited and SERIALIZABLE. The default transaction isolation level of Oracle is read commited.
15. Mysql supports the transaction isolation level in Mysql 4. The default transaction isolation level of Mysql is repeatable read.

Transaction (TransactionIs the Execution Unit of the database management system. It can be a database operation (such as a Select operation) or a group of operation sequences. The ACID attribute of the transaction, that is, atomicity (Atomicity), Consistency (Consistency), Isolation (Isolation), Persistent (Durability).

Atomicity: ensure that all or all operations in the transaction are not executed. For example, to execute a transfer transaction, either the transfer is successful or the transfer fails. If the transfer is successful, the amount is transferred from the transfer-out account to the destination account, and the amount of the two accounts changes accordingly. If the transfer fails, the amount of the two accounts remains unchanged. No money is deducted from the transfer-out account, but the target account does not receive the money.

Consistency: ensure that the database always maintains data consistency-the transaction operation is consistent before and after the transaction operation, regardless of whether the transaction is successful or not. In the above example, the database maintains data consistency before and after the transfer.

Separation: if multiple transactions are executed concurrently, the result should be the same as the serial execution of multiple transactions. Obviously, the simplest isolation is to execute all the transactions in a serial mode: first, and only after a transaction is executed can the next transaction be executed. However, the database efficiency is low. For example, two different transactions only read the same batch of data, which can be performed concurrently. Different isolation levels are available to control the effect of concurrent execution. The following is a detailed description.

Durability: Durability indicates that the impact on the database is persistent after the transaction operation is completed. Even if the database is damaged due to a fault, the database should be able to recover. Generally, logs are used.

 

Transaction isolation level (Transaction isolation levels): The isolation level is the level of control over transaction concurrency. ANSI/iso SQL classifies it into four levels: SERIALIZABLE, REPEATABLE READ, READ committed, and READ uncommitted. To achieve the isolation level, the database usually uses a Lock ). Generally, you only need to set the isolation level during programming. The database will set the specific lock used. First, we will introduce four levels, and then give an example to explain the concurrency problems that may occur in the next three levels (Repeatable read, read committed, read uncommitted.

SERIALIZABLE: all transactions are executed serially one by one to avoid phantom read (phantom reads ). For databases that implement Concurrency Control Based on locks, serialization requires that when you perform a range query (for example, select a user of the age of 10 to 30, you need to obtain the range lock ). If it is not a database that implements Concurrency Control Based on locks, you need to roll back the transaction when checking that there is a transaction that violates the serial operation.

Repeatable read: All data obtained by the Select statement cannot be modified. This prevents data inconsistency before and after a transaction. However, there is no way to control Phantom reads, because at this time other transactions cannot change the selected data, but data can be added because the previous transaction has no range lock.

READ committed: the READ data can be modified by other transactions. This may cause non-repeated reading. That is to say, the read lock is obtained when the transaction reads data, but is released immediately after reading the data (you do not need to wait until the transaction ends), and the write lock is released after the transaction is committed. After the read lock is released, data may be modified by other things. This level is also the default isolation level of SQL Server.

READ uncommitted: This is the lowest isolation level, allowing other transactions to see uncommitted data. This level causes Dirty Read (Dirty Read ).

 

      Example: The following describes the concurrency problems corresponding to the three isolation levels. Assume there are two transactions. Transaction 1 executes query 1, then transaction 2 executes query 2, then commits, and then the query 1 in transaction 1 executes again. Queries are performed based on the following tables:

Users
Id	Name	Age
1	Joe	20
2	Jill	25

Repeatable read (phantom read, phantom reads)

Each transaction executes the same query, but the returned result set is different. This occurs because the Range Lock is not obtained when the Select Operation is executed, so other transactions can still Insert new data.

Transaction 1	Transaction 2
SELECT * FROM users WHERE age BETWEEN 10 AND 30;
	Insert into users VALUES (3, 'bob', 27 ); COMMIT;
SELECT * FROM users WHERE age BETWEEN 10 AND 30;

Note that transaction 1 executes the same Query statement (Query 1) twice. If a higher isolation level (serialization) is used, the same result set should be returned for the first and second queries. However, the two result sets are different in the Repeatable read isolation level. But why is it called the Repeatable read level? This level solves the following non-repeated read problems.

Read committed (Non-repeatable reads)

In a database system that uses locks to implement concurrency control, read cannot be repeated because no read lock is applied when the Select Operation is executed ).

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = 21 WHERE id = 1; COMMIT;
SELECT * FROM users WHERE id = 1;

In this example, Transaction 2 is submitted successfully, so Transaction 1 will obtain a differentAgeValue. In the SERIALIZABLE and repeatable read isolation levels, the database should return the same value. The database returns the updated value at the read committed and read uncommitted levels. In this way, repeated read is not allowed.

Read not committed (dirty read, dirty reads)

If one transaction 2 reads the modified value of another transaction 1 but rolls back transaction 1, transaction 2 reads a dirty data, which is also called a dirty read. In this case, the transaction is allowed to read uncommitted updates.

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = 21 WHERE id = 1;
SELECT * FROM users WHERE id = 1;
	RollBack

 

To sum up the above, you can wait for the following table:

Isolation level	Dirty read	Non-repeated read	Phantom read
Read not submitted	YES	YES	YES
Read committed	NO	YES	YES
Repeatable read	NO	NO	YES
Serializing	NO	NO	NO

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.