@[toc] MySQL isolation level The theory is pretty confusing, but if we walk through some real SQL, you’ll see how simple it is.

Today Songo would like to demonstrate the transaction isolation level problem in MySQL with a few simple cases.

Theory of 1.

The isolation levels of transactions in MySQL are as follows:

  • SERIALIZABLE
  • REPEATABLE READ
  • READ COMMITTED
  • READ UNCOMMITTED

The meanings of the four isolation levels are as follows:

  1. SERIALIZABLE

If the isolation level is serialized, the current transactions are executed sequentially between users, one after the other, and this isolation level provides maximum isolation between transactions.

  1. REPEATABLE READ

At this isolation level, a transaction is not considered a sequence at repeatable reads. However, changes to the currently executing transaction are still invisible to the outside world, meaning that if the user executes the same SELECT statement several times in another transaction, the result is always the same. (Because changes to the data generated by an ongoing transaction cannot be seen externally).

  1. READ COMMITTED

READ COMMITTED isolation level is less secure than REPEATABLE READ isolation level. Transactions at the READ COMMITTED level can see changes made to data by other transactions. That is, multiple SELECT statements from the same transaction may return different results during a transaction if other transactions modify the corresponding table.

  1. READ UNCOMMITTED

READ UNCOMMITTED provides minimal isolation between transactions. But prone to false read operation and cannot be repeated read operation, in the transaction isolation level can be read other transactions have not submitted data, if this transaction using other affairs not submit changes as the basis of calculation, and then the uncommitted changes by their parent transaction cancellation, this leads to the changes on a large amount of data.

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

2. Practice of SQL

Next, test the above theory for the reader with a few simple SQL statements.

2.1 Viewing the Isolation Level

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

MySQL isolation level = MySQL8

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
Copy the code

The query results are shown as follows:

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

Start MySQL8, run the following command to check the MySQL default isolation level:

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
Copy the code

It’s just the key’s changed. Everything else is the same.

You can run the following command to change the isolation level (you are advised to change the current session isolation level instead of changing the global isolation level) :

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Copy the code

The above SQL query indicates that the database isolation level of the current session is set to READ UNCOMMITTED. When the isolation level is set to READ UNCOMMITTED, the isolation level of the current session is changed, as shown in Figure 1-2.

Note that if only the isolation level of the current session is changed, the isolation level will be restored to the default isolation level after another session. Therefore, we need to change the isolation level of the current session when testing.

2.2 the READ UNCOMMITTED

2.2.1 Preparing Test Data

READ UNCOMMITTED is the minimum level of isolation at which dirty reads, unrepeatable reads, and phantom reads can occur, so let’s look at this level to get a sense of what’s going on.

The following are introduced respectively.

Create a simple table with two default values as follows:

Table data is very simple, there are two users javaboy and itboyHub, each of whom has 1000 RMB account. Now simulate a transfer operation between the two users.

Note that if the reader is using Navicat, the different query Windows correspond to different sessions. If the reader is using SQLyog, the different query Windows correspond to the same session. Therefore, if SQLyog is used, the reader needs to open a new connection. Perform the query operation on the new connection.

2.2.2 dirty read

A transaction that reads data that another transaction has not committed is called a dirty read. Specific operations are as follows:

  1. First, open two SQL operation Windows, for example, A and B, and enter the following SQL in window A (do not execute the SQL after the input is complete) :
START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
UPDATE account set balance=balance- 100. where name='itboyhub';
COMMIT;
Copy the code
  1. Run the following SQL in window B to change the default transaction isolation level to READ UNCOMMITTED:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Copy the code
  1. Enter the following SQL in window B, execute the first line to start the transaction (note that only one line is required) :
START TRANSACTION;
SELECT * from account;
COMMIT;
Copy the code

  1. Next, execute the first two SQL statements in window A to start the transaction and add $100 to the Javaboy account.

  2. SQL > SELECT * from user; SELECT * from user; , the results are as follows:

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

This is the dirty reading problem.

2.2.3 Unrepeatable read

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

  1. First, open two query Windows A and B, and set the database transaction isolation level of B to READ UNCOMMITTED. The specific SQL is referred to above, not repeated here.

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

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

The first two SQL execution results are as follows:

  1. SQL > add $100 to javaboy;
START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
COMMIT;
Copy the code

4. Go to window B and run the second SQL in window B to query the javaboy account.

The javaboy account has changed, that is, the javaboy account has been checked twice, the result is inconsistent, this is the unrepeatable read.

The difference between a dirty read and a dirty read is that a dirty read sees uncommitted data from another transaction, whereas an unrepeatable read sees committed data from another transaction (since the current SQL is also in a transaction, it may not want to see committed data from another transaction).

2.2.4 illusion to read

Illusory reading is very similar to unrepeatable reading, so the name is illusory.

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;
Copy the code

Then enter the following SQL in window B:

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

We perform the following steps:

  1. First, execute the first two lines of the B window, start a transaction, and query the data in the database. Only Javaboy and ITboyHub can be queried.
  2. Execute the first two lines of window A to add A user named Zhangsan to the database without committing A transaction.
  3. Run the second line of the B window. Because of the dirty read problem, zhangsan can be queried.
  4. Execute the third line of window B to delete the record whose name is zhangsan. At this time, the deletion will cause a problem. Although zhangsan can be found in window B, this record has not been committed. At this time, there is an illusion, obviously a Zhangsan, but can not delete.

This is illusory reading.

After reading the above examples, you should understand the meanings of dirty reading, non-repeatable reading and phantom reading respectively.

2.3 the READ COMMITTED

Compared to READ UNCOMMITTED, READ COMMITTED mainly solves the problem of dirty reads and does not solve the problem of unrepeatable and phantom reads.

After changing the isolation level of the transaction to READ COMMITTED, repeat the above test and find that dirty reads no longer exist. Repeat the above test on the unrepeatable read case and find that the unrepeatable read problem still exists.

The above example does not apply to the illusion test, so let’s switch to the illusion test case.

Set the isolation level of Windows A and B to READ COMMITTED.

Then enter the following test SQL in window A:

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

Enter the following test SQL in window B:

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

The test method is as follows:

  1. First, execute the first two SQL lines of window B, start the transaction and query the data. At this time, only javaboy and ITboyHub can be found.
  2. Execute the first two SQL lines of window A, insert A record, but do not commit the transaction.
  3. SQL > execute the second row of window B, because there is no dirty read problem now, so the data added in window A cannot be found at this time.
  4. Execute the third SQL line of window B, which cannot be inserted because the name field is unique. You can’t insert a Zhangsan even though there is no zhangsan.

2.4 REPEATABLE READ

REPEATABLE READ further solves the problem of unrepeatable reading compared to READ COMMITTED, but phantom reading does not.

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

REPEATABLE READ already solved the problem of REPEATABLE READ, so even if the transaction was committed in the second step, the committed data could not be found in the third step, and the fourth step would fail to insert again.

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

2.5 the SERIALIZABLE

SERIALIZABLE provides maximum isolation between transactions, where transactions are executed sequentially one after the other without dirty, unrepeatable, or phantom reads.

If the current transaction isolation level is set to SERIALIZABLE, other transactions will be blocked until the current transaction commits. Therefore, the dirty read, unrepeatable read, and phantom read problems will not occur here.

3. Summary

In general, the isolation levels correspond to dirty reads, unrepeatable reads, and phantom reads as follows:

Isolation level Dirty read Unrepeatable read Read the illusion
READ UNCOMMITTED allow allow allow
READ COMMITED Don’t allow allow allow
REPEATABLE READ Don’t allow Don’t allow allow
SERIALIZABLE Don’t allow Don’t allow Don’t allow

Performance relationship is shown as follows:

Well, this article and partners first said so much, we might as well write a few lines of SQL to try.