编程知识 cdmana.com

MySQL transaction isolation level and examples of dirty read, magic read and non repeatable read

Isolation of transactions

MySQL It's a client / Software server architecture , For the same server , There can be several clients connected to it , After each client connects to the server , It can be called a conversation (Session). Each client can issue a request statement to the server in its own session , A request statement may be part of a transaction , That is to say, the server may handle multiple transactions at the same time . When there are multiple transactions in the database executing at the same time , There may be dirty reading (Dirty Read)、 It can't be read repeatedly (Non-Repeatable Read)、 Fantasy reading (Phantom Read) The problem of , To solve these problems , And then there is “ Isolation level ” The concept of .

In theory, when a transaction accesses a data , Other transactions should be queued , When the transaction is committed , Other transactions can continue to access this data . But in general, the tighter the isolation , The less efficient . So many times , We all need to find a balance between isolation and efficiency .

Problems with concurrent execution of transactions

Dirty reading (Dirty Read): Dirty read means that a transaction reads data modified by another uncommitted transaction .

For example, there is... In Xiao Wang's account 100 Balance of , Next, there are two transactions to access Xiao Wang's account .

conversation A conversation B
begin;
update xxx set balance = balance+50 where client_no = ‘ Xiao Wang customer number ’ ; begin;
select balance from xxx where client_no = ‘ Xiao Wang customer number ’ ;
( If you read 150, It means dirty reading )
rollback; commit;

Above , conversation A And the session B Each opens a transaction , conversation A First, I added... To Xiao Wang's account balance 50, At this point, the account B The balance of Xiao Wang's account is 150, Next conversation A Rolled back , That conversation B Query to the 150 It's an incorrect dirty data .

It can't be read repeatedly (Non-Repeatable Read): Non repeatable reading refers to reading the same data set multiple times in the same transaction , But the results are different . The reason for non repeatable reading is that the data queried during multiple searches has been modified by other transactions .

Look at the following two session requests .

conversation A conversation B
begin;
select balance from xxx where client_no = ‘ Xiao Wang customer number ’ ;
( Read that the balance is 100)
begin;
update xxx set balance = balance+50 where client_no = ‘ Xiao Wang customer number ’ ;
commit;
select balance from xxx where client_no = ‘ Xiao Wang customer number ’ ;
( If you read 150, It means that non repeatable reading has happened )
commit;

In conversation A In the same transaction of , The results of two same queries are different , It means there's a non repeatable read .

Fantasy reading (Phantom Read): The so-called unreal reading , When a transaction is reading a range of records , Another transaction inserts a new record in the scope , When the previous transaction reads the records of this range again , It will read data that has not been read before .

If only Xiao Wang's balance is 100, Let's look at the following two session requests .

conversation A conversation B
begin;
select name from xxx where balance = 100 ;
( Read name by ‘ Xiao Wang ’)
begin;
insert into xxx(client_no,name,balance) values(‘ Xiaozhang customer number ’,‘ Xiao Zhang ’,100);
commit;
select name from xxx where balance = 100 ;
( If I read ‘ Xiao Wang ’ and ‘ Xiao Zhang ’, It means that there has been a magic reading )
commit;

conversation A The second query in the transaction , I found what I didn't find in the first query name ‘ Xiao Zhang ’, This means that there's unreal reading .

SQL The four levels of isolation set by the standard

ISO and ANIS SQL The standard sets four levels of transaction isolation , Respectively : Read uncommitted (read uncommitted)、 Read the submission (read committed)、 Repeatable (repeatable read) And serialization (serializable ).

Let's take a look at the meaning of these four isolation levels .

  • Read uncommitted : When a transaction has not yet been committed , The changes it makes can be seen by other things .
  • Read the submission : After a transaction is committed , The changes it makes will be seen by other things .
  • Repeatable : Data seen during the execution of a transaction , It is always consistent with the data seen when the transaction is started . Of course, at the level of repeatable read isolation , Uncommitted changes are also invisible to other transactions .
  • Serialization : As the name suggests, for the same line of records ,“ Write ” Will add “ Write lock ”,“ read ” Will add “ Read the lock ”. When there is a read/write lock conflict , Subsequent transactions must wait for the previous transaction to complete , In order to proceed .

SQL Specified in the standard , For different isolation levels , Concurrent transactions can have different severity problems , The details are as follows :
( √ It can happen ;× It can't happen )

Isolation level Dirty reading It can't be read repeatedly Fantasy reading
Read uncommitted (read uncommitted)
Read the submission (read committed) ×
Repeatable (repeatable read) × ×
Serialization (serializable ) × × ×

MySQL Support for four isolation levels

although ISO and ANIS SQL The standard sets four levels of transaction isolation , But not all database vendors follow these standards , such as Oracle Database does not support read uncommitted (read uncommitted) And repeatable reading (repeatable read) Transaction isolation level for .

MySQL InnoDB Storage engine support 4 Kind of isolation level , But with SQL The difference defined in the standard is ,InnoDB The storage engine is in the default repeatable read (repeatable read) Under transaction isolation level , Use Next-Key Lock Lock algorithm , Avoid the generation of unreal reading . in other words InnoDB The storage engine is repeatable (repeatable read) Under transaction isolation level of , The transaction isolation requirements can be fully guaranteed , That is to say SQL Serialization in standards (serializable ) Isolation level requirements .

How to set the isolation level of transactions

stay InnoDB In the storage engine , You can use the following command to set the transaction isolation level for the global or current session :

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
{
	READ UNCOMMITTED
	| READ COMMITTED
	| REPEATABLE READ
	| SERIALIZABLE
}

If you want to set the isolation level of the current session to read commit , You can use the following statement :

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

If you want to MySQL Set the default isolation level for transactions when the database starts , You need to modify the configuration file transaction-isolation Value , For example , We specified... Before starting transaction-isolation = READ COMMITTED, Then the default isolation level of the transaction changes from the original REPEATABLE READ Turned into READ COMMITTED.

View transaction isolation levels for the current session , You can use the following statement :

SELECT @@transaction_isolation;

View the global transaction isolation level , You can use the following statement :

SELECT @@global.transaction_isolation;

Be careful :transaction_isolation Is in MySQL 5.7.20 To replace tx_isolation Of , If you're using a previous version of MySQL, Please use the above transaction_isolation Replace the place with tx_isolation .

版权声明
本文为[osc_ rjwgyutp]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/01/20210104130448669t.html

Scroll to Top