SQL must know and know (isolation level of transaction)

Time:2022-5-25
SQL must know and know (isolation level of transaction)

What are the possible exceptions of concurrent transaction processing?

Before understanding the database isolation level, we need to know what possible problems should be solved when setting the isolation level of transactions, that is, what exceptions will occur during concurrent transaction processing. In fact, three exceptions have been defined in the SQL-92 standard. The levels of these exceptions areDirty read, nonrepeatable read and phantom read

What do dirty reading, unrepeatable reading and unreal reading represent? I’ll explain it to you with an example. For example, we have a hero table, heros_ Temp, as follows:

SQL must know and know (isolation level of transaction)

In this hero table, we will record the names of many heroes. Assuming that we do not isolate transactions, what happens to the database during concurrent transaction processing?

On the first day, Xiao Zhang visited the database, was conducting transaction operations, and wrote a new hero “Lv Bu”:

BEGIN;
INSERT INTO heros_ Temp values (4, 'Lubu');

When Xiao Zhang hasn’t submitted the transaction yet, Xiao Li visited the data table again. He wants to see which heroes are in this hero table:

SELECT * FROM heros_temp;
SQL must know and know (isolation level of transaction)

Did you find anything unusual?At this time, Xiao Zhang has not submitted the transaction, but Xiao Li has read the data that Xiao Zhang has not submitted. This phenomenon is called “dirty reading”

The next day, Xiao Zhang wanted to see who the hero with id = 1 was, so he made an SQL query:

SELECT name FROM heros_temp WHERE id = 1;
SQL must know and know (isolation level of transaction)

However, at this time, Xiao Li started a transaction operation. He modified the hero name with id = 1 and changed the original “Zhang Fei” to “Zhang Yide”:

BEGIN;
UPDATE heros_ Temp set name = 'Zhang Yide' where id = 1;

Then Xiao Zhang queries again to see who the hero with id = 1 is:

SELECT name FROM heros_temp WHERE id = 1;
SQL must know and know (isolation level of transaction)

At this time, you will find that the results of the two queries are not the same. Xiao Zhang will wonder what’s going on?He had just executed a query and immediately conducted another query. The results of the two queries were different. In fact, the situation encountered by Xiao Zhang is called “non repeatable reading”, that is, the results of two readings of the same record are different.

On the third day, Xiao Zhang wanted to see the heroes in the data table. He began to execute the following sentence:

SELECT * FROM heros_temp;
SQL must know and know (isolation level of transaction)

At this time, after Xiao Zhang finished his execution, Xiao Li started another transaction and inserted a new hero “Lv Bu” into the database:

BEGIN;
INSERT INTO heros_ Temp values (4, 'Lubu');

Unfortunately, Xiao Zhang forgot all the heroes at this time and performed the query again:

SELECT * FROM heros_temp;
SQL must know and know (isolation level of transaction)

He found that there was one more hero in this query. Originally there were only three, but now there are four. This abnormal situation is called “unreal reading”.

Let me summarize the characteristics of these three exceptions:

  • 1. Dirty reading:Read data that has not been committed by other transactions.
  • 2. Non repeatable:When reading a certain data, it is found that the results of the two readings are different, that is, the same content is not read. This is because other transactions have modified or deleted this data at the same time.
  • 3. Unreal reading:Transaction a obtains n pieces of data according to the query criteria, but at this time, transaction B changes or adds M pieces of data that meet the query criteria of transaction A. in this way, when transaction a queries again, it will find n + M pieces of data, resulting in unreal reading.

What are the levels of transaction isolation?

The three exceptions of dirty reading, non repeatable reading and phantom reading are defined in the SQL-92 standard. At the same time, the SQL-92 standard also defines four isolation levels to solve these exceptions.

The order of solving the number of exceptions from less to more (for example, there may be three kinds of exceptions for read uncommitted, and these exceptions will not exist for serialization) determines the isolation level,The four isolation levels from low to high are read uncommitted, read committed, repeatable read, and serializable.The exceptions that can be solved by these isolation levels are shown in the following table:

SQL must know and know (isolation level of transaction)

You can see that serialization can avoid all exceptions, while read uncommitted allows exceptions to occur.

  • Read uncommittedIn other words, uncommitted data is allowed to be read. In this case, the query will not use locks, which may lead to dirty reads, unrepeatable reads, phantom reads, etc.

  • Reading submitted means that you can only read the submitted content, it can avoid dirty reading, which belongs to the default isolation level common in RDBMS (such as Oracle and SQL Server). However, if you want to avoid non repeatable reading or unreal reading, you need to write SQL statements with locking during SQL query (I will talk about locking in the advanced chapter).

  • Repeatable reading, ensure that the data results obtained from two queries of a transaction under the same query conditions are consistent, which can avoid non repeatable reading and dirty reading, but can not avoid phantom reading. MySQL’s default isolation level is readability.

  • Serializable, serializing transactions, that is, executing in order in a queue. Serialization is the highest isolation level, which can solve all possible exceptions in transaction reading, but it sacrifices the concurrency of the system.

Use MySQL client to simulate three exceptions

During the simulation, we need to open two MySQL clients, client 1 and client 2.

In client 1, let’s check the isolation level of the current session and use the command:

mysql> SHOW VARIABLES LIKE 'transaction_isolation';

Then you can see that the current isolation level is repeatable-read, that is, repeatable read.

SQL must know and know (isolation level of transaction)

Now let’s minimize the isolation level and set it to read uncommitted.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Then check the isolation level under the current session. The results are as follows:

SQL must know and know (isolation level of transaction)

Since MySQL is automatically committed by default, we also need to set the autocommit parameter to 0. The command is as follows:

mysql> SET autocommit = 0;

Then let’s check the autocommit value in session. The results are as follows:

SQL must know and know (isolation level of transaction)

Then we start client 2 with the same operation, that is, set the isolation level to read uncommitted and autocommit to 0.

Simulate “dirty read”

We start a transaction in client 2, in heros_ Write a new hero “Lubu” in the temp table. Be careful not to submit it at this time.

SQL must know and know (isolation level of transaction)

Then we can view the current hero table in client 1:

SQL must know and know (isolation level of transaction)

You can find that the new hero “Lubu” not submitted by client 2 is read in client 1. In fact, client 2 may roll back immediately, resulting in “dirty reading”.

Simulate “non repeatable read”

We use client 1 to view heroes with id = 1:

SQL must know and know (isolation level of transaction)

Then use client 2 to modify the hero name with id = 1:

SQL must know and know (isolation level of transaction)

At this time, use client 1 to query again:

SQL must know and know (isolation level of transaction)

You can find that for client 1, the same query statement appears “unreadable”.

Simulated “unreal reading”

Let’s use client 1 to query all heroes in the data table:

SQL must know and know (isolation level of transaction)

Then use client 2 to insert a new hero “Lubu”:

SQL must know and know (isolation level of transaction)

At this time, we use client 1 to check again:

SQL must know and know (isolation level of transaction)

You will find one more piece of data in the data table.

If you are a beginner, you can use heros_ Temp data table simply simulates the above process to deepen the understanding of dirty reading, unrepeatable reading and phantom reading. Correspondingly, you will also know more about the abnormal problems solved by different isolation levels.