If someone asks you what the isolation level of MySql is, send this article to him!

△Hollis, a person with a unique pursuit of Coding△

This is Hollis's  356th original sharing

Author l zyz1992

Source: l Hollis (ID: hollischuang)

First of all, we must understand what is a transaction?

A transaction is a series of strict operations in a program, all operations must be completed, otherwise all changes made in all operations will be undone. That is the atomicity of the transaction, a series of operations in a transaction either all succeed or fail.

There are two types of transaction endings. When all steps in the transaction are successfully executed, the transaction commits. If one of the steps fails, a rollback operation will occur, undoing all operations before the start of the transaction.


ACID of the transaction

Transaction has four characteristics

  1. Atomic transaction is the logical unit of work of the database. The transaction contains multiple operations, either all of them are done or none of them are done
  2. Isolation (Isolation is also the focus of this article) Transactions cannot interfere with each other, that is, the operation of a transaction is isolated from other transaction operations of the database, and the time of each transaction executed concurrently is complementary to interfere with each other.
  3. Once a persistent transaction is committed, its changes are permanent
  4. The result of consistent transaction execution must satisfy the change from one state to another. Therefore, when the database only contains the results of successful transaction submission, the database is said to be in a consistent state. If a system failure occurs when the database system is running, some unfinished transactions are forced to abort, and some modifications have been written to the database, the database is in an incorrect state at this time.

In fact, the above three conditions (atomicity, isolation, durability) are ultimately to maintain the consistent service of database data

Four isolation levels of MySQL

The SQL standard defines four isolation levels to limit which changes inside and outside the transaction are visible and which are invisible.

  1. Read uncommitted data [Read Uncommitted] At this isolation level, all transactions can see the execution results of other transactions that have not been committed. (It is impossible to use this isolation level in actual production)
  2. Read the submitted content [Read Committed] This isolation level is the default isolation level of most databases (not the MySQL default). It satisfies the simple definition of isolation: a transaction can only see the changes made by other committed transactions. This isolation level also supports non-repeatable reading, that is, the same select may get different results
  3. Repeatable Read [Repeatable Read] This is MySQL's default isolation level, which ensures that the same transaction will see the same data row when reading data concurrently. But in theory, it will lead to another problem, [phantom reading]. Phantom reading: query some data under the same condition, and then other transactions [add] or [delete] the data of the condition, which will lead to different reading results. The InnoDB storage engine solves this problem through the multi-version control (MVCC) mechanism
  4. Serializable [serializable] This is the highest isolation level of transactions. It solves the problem of phantom reading by forcing transaction ordering to make it impossible to conflict with each other. It adds a shared lock to each read data row. But it may cause timeout and lock competition (this isolation level is too extreme, and it is basically not used in actual production)

These four isolation levels are implemented using different lock types

  1. Dirty read reads the uncommitted or rolled back data of the previous transaction
  2. Non-repeatability of the same select query, but the results are different, there are transactions in the process that update the original data
  3. The number of results of the two queries of the phantom read is not the same, and there is transaction addition or deletion of data in the process

The following is a summary of the different problems caused by different isolation levels

Details of the isolation levels measured test

View the isolation level of the database

show variables like '%isolation%'

Set the isolation level of the database

set session transaction isolation level Read Uncommitted;

Set the isolation level of the database as:Read Uncommitted

Experiment 1: Read Uncommitted

Read Uncommitted That is: read uncommitted

Precondition: Set the isolation level of the database to read uncomitted;

set session transaction isolation level Read Uncommitted;


img

img

The first step: A starts the transaction:start tracsaction;

img

The second step: A query data:select * from test;

img

The third step: B opens the transaction:start transaction;

img

The fourth step: B query data:select * from test;

img

The fifth step: B updates the data update test set num =10 where id = 1:; B did not commit the transaction

img

The sixth step: A reads the data ---- A reads the uncommitted data of B (the isolation level of the current database is: Read Uncommitted )

img

The seventh step: B rollback data:rollback;

img

The eighth step: B query data:select * from test;

img

The ninth step: A query number:select * from test;

img

Conclusion: Transaction B updated the data, but did not commit. Transaction A reads B's uncommitted record. Because of dirty reads. Read Uncommitted is the lowest isolation level

Experiment 2: Read Committed-Read Committed

Precondition: Set the isolation level of the database to:Read Committed;

set session transaction isolaction level Read Committed;


img

img

The first step: A starts the transaction:start transaction;

img

The second step: A query data:select *from test;

img

The third step: B opens the transaction:start transaction;

img

The fourth step: B query data:select * from test;

img

The fifth step: B update data:update test set num =10 where id=1

View Results:

img

The sixth step: A query data:select * from test;

img

Step 7: B submit data:commit;

img

The eighth step: A query data:select * from test;

img

Conclusion:Read Committed Read the committed isolation level solves the problem of dirty reads, but the problem of non-repeatable reads occurs, that is, the results of two queries of transaction A are inconsistent, because transaction B updates a piece of data between the two queries.

Read submitted records are only allowed to be read, but repeatable reading is not required

Experiment 3: Repeatable Read

Precondition: set the level of the database to repeatability

set session transaction isolation level repeatable read;

img

The first step: A starts the transaction:start transaction;

img

The second step: A query data:select * from test;

img

The third step: B opens the transaction:start transaction;

img

The fourth step: B query data:select * from test;

img

The fifth step: B update data:update test set num=10 where id=1;

img

B did not commit the transaction at this time

The sixth step: B query data:select * from test;

img

The seventh step: A query data

img

The result is still the previous result (because transaction B has not yet been committed)

The eighth step: B commits the transaction:commit;

img

Step 9: A query data: select * from test;At this time, the record of A query is still the same as before

img

The tenth step: B inserts a piece of data and commits the transaction:inset into test(num) value(4);

img

The eleventh step: A query the data, and found that the result is still the same as before:select * from test;

img

The twelfth step: A submit the transaction and query data

img

At this time, it is found that the data of A query is consistent with the result of B query;

Conclusion: The Repeatable Readisolation level only allows to read the records of committed transactions,

Experiment 4: Serialization-Serializable

Precondition: set the isolation level of the database to be serializable

img

The first step: A starts the transaction and queries data

img

The second step: B opens the transaction and inserts the data, and finds that it can only wait and cannot execute

img

The third step: A commits the transaction

img

The fourth step: B insert data

img

Conclusion: The serializablefield is completely locked. If a transaction is to operate the same data, it must wait until the previous transaction is completed and the lock is released. It is a complete isolation level, which will lock the corresponding data table, because it will cause efficiency problems.

Summary of this article

This article does not explain the principle in depth, but allows everyone to understand the isolation level from the surface of the isolation level more intuitively, because I found that many of my colleagues are vague and ambiguous about this, but this is No, because the technology itself does not allow such ambiguity. To understand is to understand, it can be used reasonably. If it is ambiguous, then it must be full of loopholes in actual application, so this is the reason why this article was born.

We can put aside the principle and the concrete realization of the bottom layer first, and first be able to clarify the meaning of various professional terms clearly and clearly. This is not an improvement.

Finally, with one sentence, if you don't accumulate steps, you can't reach a thousand miles. If you don't accumulate small streams, you can't make a river .

 往期推荐

Mosaic passwords are no longer safe! This open source "mosaic" tool restores in one second

Net worth breaks 100 million! The 86 version of Journey to the West "Red Boy" refuses to make his debut as an academic master. He is already a doctor of the Chinese Academy of Sciences and has 52 companies under his name

Take my parents to the Shanghai Oriental Pearl Revolving Restaurant to experience it

Facing Java Issue 360: How to Use Optimistic Locking to Improve High Concurrency Throughput and Not Oversold

In-depth Concurrency Issue No. 015: How to debug multi-threaded code?

If you like this article,

Please press and hold the QR code to follow Hollis.

Forwarding to the circle of friends is the greatest support for me.

Click to see

Like is a feeling

Watching is a kind of support

↘↘↘