Shulou(Shulou.com)05/31 Report--

This article introduces how to understand the transaction isolation level in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Many friends have doubts about the isolation level of MySQL, in fact, this problem is not difficult at all, the key depends on how to say it! Simply looking at the theory will definitely make you confused, but if we demonstrate some through a few actual SQL, we will find that this thing is so simple!

1. theory

There are four isolation levels for transactions in MySQL, which are as follows:

Serialization (SERIALIZABLE)

Repeatable read (REPEATABLE READ)

Submit read (READ COMMITTED)

Uncommitted read (READ UNCOMMITTED)

The four different isolation levels have the following meanings:

SERIALIZABLE

If the isolation level is serialized, the current transaction is executed one after another between users, which provides maximum isolation between transactions.

REPEATABLE READ

At this isolation level of repeatable readability, transactions are not seen as a sequence. However, changes to the currently executing transaction are still not visible externally, that is, if the user executes the same SELECT statement several times in another transaction, the result is always the same. (because the data changes generated by the transaction being executed cannot be seen externally).

READ COMMITTED

The security of READ COMMITTED isolation level is worse than that of REPEATABLE READ isolation level. Transactions at the READ COMMITTED level can see data modifications made by other transactions. That is, if another transaction modifies the corresponding table during a transaction, multiple SELECT statements of the same transaction may return different results.

READ UNCOMMITTED

READ UNCOMMITTED provides minimal isolation between transactions. In addition to illusory read operations and unrepeatable read operations, transactions at this isolation level can read data that other transactions have not yet committed, if this transaction uses changes that other transactions do not commit as the basis for calculation, then those uncommitted changes are undone by their parent transaction, which leads to a large number of data changes.

In MySQL databases, the default transaction isolation level is REPEATABLE READ

2. SQL practice

Next, verify the above theory to the reader through a few simple SQL.

2.1 View isolation level

You can view the default global isolation level of the database instance and the isolation level of the current session through the following SQL:

MySQL8 previously used the following command to view the MySQL isolation level:

SELECT @ @ GLOBAL.tx_isolation, @ @ tx_isolation

The query results are as follows:

As you can see, the default isolation level is REPEATABLE-READ, both the global isolation level and the current session isolation level.

Starting with MySQL8, view the default isolation level of MySQL with the following command:

SELECT @ @ GLOBAL.transaction_isolation, @ @ transaction_isolation

It's just that the keywords have changed, everything else is the same.

You can modify the isolation level with the following command (it is recommended that developers modify the current session isolation level when making changes, instead of modifying the global isolation level):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The above SQL indicates that the database isolation level of the current session is set to READ UNCOMMITTED. After the setting is successful, query the isolation level again and find that the isolation level of the current session has changed, as shown in figure 1-2:

Note that if you only modify the isolation level of the current session, after changing the session, the isolation level will return to the default isolation level, so when we test, we can change the isolation level of the current session.

2.2 READ UNCOMMITTED2.2.1 prepares test data

READ UNCOMMITTED is the lowest isolation level, and there are dirty reading, unrepeatable reading and phantom reading problems in this isolation level, so let's take a look at this isolation level first so that you can understand what these three problems are all about.

The following are introduced respectively.

First, create a simple table with two pieces of data preset, as follows:

The data in the table is very simple. There are two users, javaboy and itboyhub, each with an account of 1000 yuan. Now simulate a transfer operation between the two users.

Note that if the reader uses Navicat, different query windows correspond to different session, and if the reader uses SQLyog, different query windows correspond to the same session, so if SQLyog is used, the reader needs to open a new connection and query in the new connection.

2.2.2 dirty reading

One transaction reads data that has not been committed by another transaction, which is called a dirty read. The specific operations are as follows:

First, open two SQL operation windows, assuming An and B respectively, and enter the following SQL in A window (no need to execute after the input is completed):

START TRANSACTION;UPDATE account set balance=balance+100 where name='javaboy';UPDATE account set balance=balance-100 where name='itboyhub';COMMIT

Execute the following SQL in the B window, and modify the default transaction isolation level to READ UNCOMMITTED, as follows:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Next, enter the following SQL in the B window. After the input is complete, first execute the first line to open the transaction (note that you only need to execute one line):

START TRANSACTION;SELECT * from account;COMMIT

Next, execute the first two SQL in the A window, that is, open the transaction, and add 100 yuan to the javaboy account.

Go to the B window and execute the second query SQL (SELECT * from user;) of the B window. The result is as follows:

As you can see, the transaction in window A has not yet been committed, but the relevant changes in the data can be queried in window B.

This is the problem of dirty reading.

2.2.3 non-repeatable

Unrepeatable reading means that a transaction reads the same record one after another, but the data read twice is different, which is called non-repeatable reading. The specific steps are as follows (restore the money of both accounts to 1000 before operation):

First open two query windows An and B, and set the database transaction isolation level of B to READ UNCOMMITTED. Specific SQL refer to the above, I will not repeat it here.

Enter the following SQL in the B window, then execute only the first two SQL to open the transaction and query the javaboy account:

START TRANSACTION;SELECT * from account where name='javaboy';COMMIT

The execution results of the first two SQL are as follows:

Execute the following SQL in the A window to add 100RMB to the javaboy account, as follows:

START TRANSACTION;UPDATE account set balance=balance+100 where name='javaboy';COMMIT

4. Go back to window B again and execute the second SQL of window B to view javaboy's account. The result is as follows:

Javaboy's account has changed, that is, two times before and after checking the javaboy account, the results are inconsistent, which is non-repeatable.

The difference between dirty reading and dirty reading is that dirty reading sees data that has not been committed by other transactions, while unrepeatable reading is seeing data that has been committed by other transactions (since the current SQL is also in a transaction, you may not want to see data that has been committed by other transactions).

2.2.4 Phantom reading

Illusionary reading is very similar to unrepeatable reading. Looking at the name is a hallucination.

Let me give you a simple example.

Enter the following SQL in the A window:

START TRANSACTION;insert into account (name,balance) values ('zhangsan',1000); COMMIT

Then enter the following SQL in the B window:

START TRANSACTION;SELECT * from account;delete from account where name='zhangsan';COMMIT

Our steps are as follows:

First, execute the first two lines of the B window, open a transaction, and query the data in the database at the same time. At this time, the only data you can query is javaboy and itboyhub.

Execute the first two lines of the A window to add a user named zhangsan to the database, and note that you do not have to commit the transaction.

Execute the second line of the B window, because of the dirty reading problem, you can query the user zhangsan at this time.

Execute the third line of the B window to delete the record whose name is zhangsan. At this time, there will be a problem. Although the zhangsan can be queried in the B window, the record has not been submitted yet. It is because of dirty reading that the record cannot be deleted. At this time, there was an illusion that there was a zhangsan, but it could not be deleted.

This is phantom reading.

After reading the above case, you should understand the meaning of dirty reading, unrepeatable reading and illusory reading.

2.3 READ COMMITTED

Compared with READ UNCOMMITTED, READ COMMITTED mainly solves the problem of dirty reading, but not for unrepeatable reading and phantom reading.

After changing the isolation level of the transaction to READ COMMITTED, repeat the above test on dirty reading cases, and find that there is no dirty reading problem; repeat the above test on unrepeatable cases, and find that the unrepeatable reading problem still exists.

The above case does not apply to the phantom reading test, let's change to another phantom reading test case.

Or two windows An and B, change the isolation level of window B to READ COMMITTED

Then enter the following test SQL in the A window:

START TRANSACTION;insert into account (name,balance) values ('zhangsan',1000); COMMIT

Enter the following test SQL in the B window:

START TRANSACTION;SELECT * from account;insert into account (name,balance) values ('zhangsan',1000); COMMIT

The test method is as follows:

First, execute the first two lines of SQL in the B window, open the transaction and query the data. At this time, only javaboy and itboyhub users are found.

Execute the first two lines of SQL in the A window, insert a record, but do not commit the transaction.

Execute the second line of SQL in window B, and since there is no dirty reading problem now, the data added in window A cannot be found at this time.

Execute the third line of SQL in the B window, where the name field is unique, so it cannot be inserted here. At this time, there is an illusion that there is no zhangsan user, but can not insert zhangsan.

2.4 REPEATABLE READ

Compared with READ COMMITTED, REPEATABLE READ further solves the problem of unrepeatable reading, but phantom reading is not solved.

The test for phantom reading in REPEATABLE READ is basically the same as in the previous section, except that in the second step, remember to commit the transaction after inserting the SQL.

Because REPEATABLE READ has solved the problem of non-repeatable reading, even if the transaction is committed in the second step, the committed data will not be found in the third step, and there will be an error if the fourth step continues to insert.

Note that REPEATABLE READ is also the default database transaction isolation level for the InnoDB engine

2.5 SERIALIZABLE

SERIALIZABLE provides maximum isolation between transactions. In this isolation level, transactions are executed one after another without dirty reading, non-repeatable reading, and phantom reading problems.

If you set the current transaction isolation level to SERIALIZABLE, it will block when other transactions are opened, and must wait for the current transaction to commit before other transactions can be opened successfully, so the previous dirty read, non-repeatable read and phantom read problems will not occur here.

3. Summary

In general, the corresponding relationships between isolation levels and dirty, unrepeatable, and phantom reads are as follows:

Isolation level dirty read non-repeatable read phantom read READ UNCOMMITTED allows READ COMMITED does not allow REPEATABLE READ does not allow SERIALIZABLE does not allow

The performance relationship is shown in the figure:

So much for sharing about how to understand the transaction isolation level in MySQL. I hope the above content can help you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.