Example MySQL transaction isolation level and dirty read, phantom read, non-repeatable read

Recommended (free):mysql video tutorial

Isolation of transactions Property

MySQL is a client/server architecture software. For the same server, there can be several clients connected to it. After each client is connected to the server, it can Call it a 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, the server may process multiple transactions at the same time. When multiple transactions are executed simultaneously on the database, problems such as dirty read, non-repeatable read, and phantom read may occur. In order to solve these problems, there is " The concept of "isolation level".

Theoretically, when a transaction accesses a certain data, other transactions should be queued. Only after the transaction is submitted, other transactions can continue to access the data. But generally speaking, the tighter the isolation, the lower the efficiency. Therefore, many times, we have to find a balance between isolation and efficiency.

Problems encountered in concurrent transaction execution

Dirty Read:Dirty read means that one transaction reads another uncommitted Data modified by the transaction.

For example, if Xiao Wang’s account has a balance of 100, there will be two transactions to access Xiao Wang’s account.

##begin; update xxx set balance = balance 50 where client_no = 'Xiao Wang client number' ; begin; select balance from xxx where client_no = 'Xiao Wang client number' ; rollback; commit;

Session A	Session B
	(If it reads 150, it means a dirty read occurred)

As above, session A and session B each opened a transaction. Session A first added the balance to Xiao Wang’s account. 50. At this time, account B queries Xiao Wang's account balance as 150. Then session A rolls back, and the 150 queried by session B becomes incorrect dirty data.

Non-Repeatable Read:Non-repeatable read refers to reading the same data set multiple times within the same transaction, but the results are different. Non-repeatable reads occur because the queried data was modified by other transactions during multiple searches.

Look at the following two session requests.

Session A Session B ##begin; select balance from xxx where client_no = 'Xiao Wang client number' ; (read the balance is 100) select balance from xxx where client_no = 'Xiao Wang client number' ; (If it reads 150, it means a non-repeatable read has occurred ) commit; in session In the same transaction of A, the results of two identical queries are different, which means that a non-repeatable read has occurred.

begin;
update xxx set balance = balance 50 where client_no = 'Xiao Wang client number' ;
commit;

Phantom Read:

The so-called phantom read means that when a transaction reads records in a certain range, another transaction inserts records in the range. When the previous transaction reads the records in this range again, it will read the data that was not read before.Suppose that currently only Xiao Wang’s balance in the account table is 100, and then look at the following two session requests.

The second query in the session A transaction found the name 'Xiao Zhang' that was not found in the first query, which means that a phantom read occurred.

The four isolation levels established by the SQL standard

ISO and ANIS SQL standards have established four transaction isolation levels, namely: read uncommitted (read uncommitted) ), read committed (read committed), repeatable read (repeatable read) and serializable (serializable).

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

• Read uncommitted:When a transaction has not been submitted, the changes it makes can be seen by other transactions.
• Read commit:After a transaction is committed, the changes it makes will be seen by other transactions.
• Repeatable read:The data seen during the execution of a transaction is always consistent with the data seen when the transaction is started. Of course, under the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
• Serialization:As the name implies, for the same row of records, "write" will add a "write lock", and "read" will add a "read lock". When a read-write lock conflict occurs, the transaction accessed later must wait for the completion of the previous transaction before it can continue to execute.

The SQL standard stipulates that for different isolation levels, concurrent transactions can cause problems of different severity. The specific situation is as follows:
(√ means it can happen; × means it cannot happen Occurred)

Session A ##begin; select name from xxx where balance = 100; (Read name is 'Xiao Wang') select name from xxx balance where = 100; (If you read '小王' and '小王' Zhang', it means that phantom reading has occurred) commit;

Session B
begin;
insert into xxx(client_no,name,balance) values('Xiao Zhang customer number','Xiao Zhang',100);
commit;

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read )	×	×	√
Serializable ）	×	×	×

MySQL’s support for four isolation levels

Although ISO and The ANIS SQL standard stipulates four transaction isolation levels, but not all database vendors follow these standards. For example, Oracle database does not support read uncommitted (read uncommitted) and repeatable read (repeatable read) transaction isolation levels.

MySQL InnoDB storage engine supports 4 isolation levels, but different from what is defined in the SQL standard,InnoDB storage engine uses Next under the default repeatable read (repeatable read) transaction isolation level -Key Lock lock algorithm avoids the occurrence of phantom reads. In other words, the InnoDB storage engine can fully guarantee the isolation requirements of transactions under the transaction isolation level of repeatable read, that is, it has reached the serializable isolation level requirements in the SQL standard.

How to set the transaction isolation level

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

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 set the default isolation level of the transaction when the MySQL database is started, you need to modify the transaction- in the configuration file. The value of isolation, for example, if we specifytransaction-isolation = READ COMMITTEDbefore startup, then the default isolation level of the transaction will change from the original REPEATABLE READ to READ COMMITTED.

To view the transaction isolation level of the current session, you can use the following statement:

SELECT @@transaction_isolation;

To view the global transaction isolation level, you can use the following statement:

SELECT @@global.transaction_isolation;

Note: transaction_isolation was introduced in MySQL 5.7.20 to replace tx_isolation. If you are using a previous version of MySQL, please replace the above transaction_isolation with tx_isolation.

For more programming related knowledge, please visit:Programming Video! !

The above is the detailed content of Example MySQL transaction isolation level and dirty read, phantom read, non-repeatable read. For more information, please follow other related articles on the PHP Chinese website!