Mysql case analysis of transaction isolation level

Many friends have always been confused about the isolation level of MySQL. In fact, this question is not difficult at all. The key is how to explain it! Just looking at the theory will definitely make you dizzy, but if we demonstrate it through some actual SQL, you will find that it is so simple!

Today, I would like to demonstrate the transaction isolation level problem in MySQL through a few simple cases.

1. Theory

There are four types of transaction isolation levels in MySQL, as follows:

• SERIALIZABLE
• Repeatable Read
• READ COMMITTED
• READ UNCOMMITTED

The meanings of the four different isolation levels are as follows:

SERIALIZABLE

If the isolation level is serialized, users execute the current transaction sequentially one after another. This isolation level provides maximum isolation between transactions.

REPEATABLE READ

At the repeatable read isolation level, transactions are not considered to be a sequence. However, the changes in the currently executing transaction are still not visible to the outside world. 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 READ COMMITTED isolation level is less secure than the REPEATABLE READ isolation level. Transactions at the READ COMMITTED level can see changes made to data by other transactions. That is, during transaction processing, if other transactions modify the corresponding tables, multiple SELECT statements in the same transaction may return different results.

READ UNCOMMITTED

READ UNCOMMITTED provides minimal isolation between transactions. In addition to being prone to phantom read operations and non-repeatable read operations, transactions at this isolation level can read data that other transactions have not yet committed. If this transaction uses uncommitted changes from other transactions as the basis for calculations, and those uncommitted changes are undone by their parent transactions, this will result in a large amount of data changes.

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

2. SQL Practice

Next, we will verify the above theory to the readers through a few simple SQL statements.

2.1 Check the 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:

Before MySQL 8, use 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 for the global isolation level and the current session isolation level.

Starting from MySQL 8, use the following command to view the MySQL default isolation level:

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

Only the keywords have changed, everything else remains the same.

The isolation level can be modified by the following command (it is recommended that developers modify the current session isolation level instead of the global isolation level):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The above SQL statement indicates that the database isolation level of the current session is set to READ UNCOMMITTED. After the setting is successful, the isolation level is queried again and it is found 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, the isolation level will be restored to the default isolation level after changing a session, so when we test, we only need to modify the isolation level of the current session.

2.2 READ UNCOMMITTED

2.2.1 Preparing test data

READ UNCOMMITTED is the lowest isolation level. This isolation level has dirty read, non-repeatable read and phantom read problems, so let's take a look at this isolation level first, so that you can understand what these three problems are.

They are introduced below one by one.

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

The data in the table is very simple. There are two users, javaboy and itboyhub, and each of their accounts has 1,000 RMB. Now let’s simulate a transfer operation between these two users.

Note that if you are using Navicat, different query windows correspond to different sessions. If you are using SQLyog, different query windows correspond to the same session. Therefore, if you are using SQLyog, you need to open a new connection and perform query operations in the new connection.

2.2.2 Dirty Read

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

First, open two SQL operation windows, assuming they are A and B. Enter the following SQL in window A (do not execute after entering):

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 window B to change the default transaction isolation level to READ UNCOMMITTED, as follows:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Next, enter the following SQL in window B. After entering, execute the first line to start the transaction (note that only one line needs to be executed):

START TRANSACTION;
SELECT * from account;
COMMIT;

Next, execute the first two SQL statements in window A to start a transaction and add 100 yuan to the javaboy account.

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

It can be seen that although the transaction in window A has not been committed, the relevant data changes can be queried in window B.

This is the dirty read problem.

2.2.3 Non-repeatable read

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

1. First, open two query windows A and B, and set the database transaction isolation level of B to READ UNCOMMITTED. Please refer to the above for the specific SQL, which will not be repeated here.
2. Enter the following SQL in window B, and then execute only the first two SQLs to start the transaction and query the javaboy account:

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

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

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

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

4. Return to window B again and execute the second SQL statement in window B to view the account of javaboy. The result is as follows:

The javaboy account has changed, that is, the results of checking the javaboy account twice are inconsistent, which is a non-repeatable read.

The difference between dirty read and non-repeatable read is that dirty read is to see the data that other transactions have not committed, while non-repeatable read is to see the data that other transactions have committed (because the current SQL is also in a transaction, it may not want to see the data that other transactions have committed).

2.2.4 Phantom Read

Phantom read is very similar to non-repeatable read, and just looking at the name means that it produces an illusion.

Let me give you a simple example.

Enter the following SQL in window A:

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

Then enter the following SQL in window B:

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

We perform the following steps:

• First, execute the first two lines of window B to start a transaction and query the data in the database at the same time. At this time, the only data queried are javaboy and itboyhub.
• Execute the first two lines in window A to add a user named zhangsan to the database. Note that you do not need to commit the transaction.
• Execute the second line of window B. Due to the dirty read problem, the user zhangsan can be found at this time.
• Execute the third line of window B to delete the record with name zhangsan. There will be a problem with deletion at this time. Although zhangsan can be found in window B, this record has not been committed. It is seen because of dirty read, so it cannot be deleted. At this moment, an illusion occurred. There was clearly a zhangsan, but it could not be deleted.

This is phantom reading.

After reading the above cases, everyone should understand what dirty reads, non-repeatable reads, and phantom reads mean.

2.3 READ COMMITTED

Compared with READ UNCOMMITTED, READ COMMITTED mainly solves the problem of dirty reads, but does not solve the problem of non-repeatable reads and phantom reads.

After changing the transaction isolation level READ COMMITTED , repeating the above test on the dirty read case, it is found that the dirty read problem no longer exists; repeating the above test on the non-repeatable read case, it is found that the non-repeatable read problem still exists.

The above case is not suitable for phantom reading test. Let's change to a phantom reading test case.

Still two windows A and B, change the isolation level of window B READ COMMITTED ,

Then enter the following test SQL in window A:

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

Enter the following test SQL in window B:

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 window B, start a transaction and query data. At this time, only two users, javaboy and itboyhub, are found.
• Execute the first two lines of SQL in window A to insert a record, but do not commit the transaction.
• Execute the second line of SQL in window B. Since there is no dirty read problem now, the data added in window A cannot be found at this time.
• Execute the third line of SQL in window B. Since the name field is unique, insertion cannot be performed here. At this point, an illusion occurs. Even though there is no user called zhangsan, you cannot insert zhangsan.

2.4 REPEATABLE READ

Compared with READ COMMITTED, REPEATABLE READ further solves the problem of non-repeatable reads, but phantom reads are not solved.

The phantom read test in REPEATABLE READ is basically the same as that in the previous section. The difference is that you must commit the transaction after executing the insert SQL in the second step.

Since REPEATABLE READ has solved the problem of non-repeatable read, even if the transaction is committed in the second step, the committed data cannot be found in the third step, and an error will occur if the data is inserted in the fourth step.

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 sequentially one after another, and dirty reads, non-repeatable reads, and phantom reads will not occur. It is the safest.

If the current transaction isolation level is set to SERIALIZABLE, then when you start other transactions at this time, they will be blocked and must wait until the current transaction is committed before other transactions can be started successfully. Therefore, the previous dirty read, non-repeatable read, and phantom read problems will not occur here.

3. Conclusion

In general, the correspondence between isolation levels and dirty reads, non-repeatable reads, and phantom reads is as follows:

The performance relationship is shown in the figure:

Okay, that’s all for this article. You may want to give it a try by writing a few lines of SQL.

This is the end of this article about MySQL case analysis of transaction isolation level. For more relevant MySQL transaction isolation level content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!