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

This article will explain in detail how to understand the transaction isolation level and its principle commonly used in the database. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

What is transaction isolation?

Any database that supports transactions must have four characteristics, namely: Atomicity, Consistency, Isolation, Durability, that is, transaction ACID, so as to ensure the correctness of the data in the transaction ((Transaction).

Transaction isolation means that when multiple concurrent transactions access a database at the same time, one transaction should not be disturbed by another transaction, and each concurrent transaction should be isolated from each other.

What happens if there is no transaction isolation?

Suppose we now have such a table (T), which records a lot of awesome names, we do not conduct transaction isolation to see what happens?

* days ago, transaction An accessed the database and did one thing, adding the name of the newcomer to the database, but did not commit the transaction.

Insert into T values (4, 'cow D')

At this time, there is another transaction B, he wants to look up the names of all the awesome people.

Select Name from T

At this point, if there is no effective isolation between transactions, the name "cow D" will appear in the result returned by transaction B. This is called "dirty read".

The next day, transaction An accesses the database. He wants to see the name of the awesome person whose ID is 1, so he executes

Select Name from T where ID = 1

At this point, transaction B came, because the name of ID was changed, so it had to be updated and the transaction was committed.

Update T set Name = 'not awesome' where ID = 1

Then, transaction A wants to see the name of the awesome person whose ID is 1, so it executes again.

Select Name from T where ID = 1

As a result, the name of the awesome person whose ID is 1 is not the same two times, which is unrepeatable read.

On the third day, transaction An accessed the database. He wanted to see who the database was, so he executed

Select * from T

At this time, transaction B came and added a new awesome person to the database.

Insert into T values (4, 'cow D')

At this time, transaction A forgot what the awesome people had just now, so it was executed again.

Select * from T

As a result, there were three awesome people every time and four awesome people the second time.

I believe that transaction A will be confused at this time. What just happened? This situation is called "phantom problem".

In order to prevent dirty reading, unrepeatable reading, phantom reading and so on, we need to set the isolation level of the database according to our actual needs.

What are the isolation levels of the database?

General databases include the following four isolation levels:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Read unsubmitted (Read Uncommitted)

Read submit (Read Committed)

Repeatable read (Repeated Read)

Serialization (Serializable)

How to use these isolation levels needs to be judged according to the actual situation of the business.

Next, let's take a look at the specific conditions of these four isolation levels.

Read unsubmitted (Read Uncommitted)

Read unsubmitted, as the name implies, is to read unsubmitted content.

Therefore, under this isolation level, the query will not be locked, and because the query is unlocked, the consistency of this isolation level is the worst, which may lead to "dirty reading", "non-repeatable reading" and "phantom reading".

If there are no special circumstances, this isolation level will not be used.

Read submit (Read Committed)

Read submission, as the name implies, means that you can only read what has been submitted.

This is the most commonly used isolation level in various systems and is the default isolation level for SQL Server and Oracle. This isolation level can effectively avoid dirty reads, but unless locked as shown in the query, such as:

Select * from T where ID=2 lock in share mode; select * from T where ID=2 for update

Otherwise, ordinary queries will not be locked.

So why is it that "read submit", like "read uncommitted", has no query lock, but can avoid dirty reading?

This means that another mechanism, snapshot (snapshot), is also called snapshot read (Snapshot Read), which ensures consistency without locking.

Assuming that there is no "snapshot read", when an updated transaction is not committed, another transaction querying the updated data will be blocked because it cannot be queried, in which case the concurrency is quite poor.

"snapshot reading" can complete highly concurrent queries, but "read submission" can only avoid "dirty reading", not "unrepeatable reading" and "phantom reading".

Repeatable read (Repeated Read)

Repeatable, as the name implies, is an isolation level designed specifically for the situation of "non-repeatable reading". Naturally, it can effectively avoid "non-repeatable reading". It is also the default isolation level for MySql.

At this level, ordinary queries also use "snapshot reads", but unlike "read commit", "Update" is not allowed when the transaction starts, and "non-repeatable reading" is precisely because the data is modified between the two reads, so "repeatable reading" can effectively avoid "non-repeatable reading", but can not avoid "phantom reading". Because phantom reading is caused by an insert or delete operation (Insert or Delete).

Serialization (Serializable)

This is the isolation level of the database, at which transactions are "serialized sequentially", that is, queued execution.

At this level, "dirty reading", "non-repeatable reading" and "phantom reading" can all be avoided, but the execution efficiency is very poor and the performance overhead is also *, so almost no one will use it.

To sum up.

Why is there "dirty reading"? Because there is no "select" operation, no rules.

Why is there "unrepeatable"? Because there are no rules for "update" operation.

Why is there "phantom reading"? Because "insert" and "delete" operations have no rules.

What can "Read Uncommitted" prevent? Nothing can be prevented.

What can "Read Committed" prevent? Use "Snapshot Read" to avoid "dirty reading", but "unrepeatable" and "phantom reading" may occur.

What can "Repeated Red" prevent? Use "Snapshot Read" to lock the read record to avoid "dirty reading" and "non-repeatable reading", but there may be "phantom reading".

What can "Serializable" prevent? Sitting in rows and eating fruit can effectively avoid "dirty reading", "non-repeatable reading" and "illusory reading", but who knows the effect.

On how to understand the database commonly used transaction isolation level and what is the principle of sharing here, I hope that the above content can be of some help to you, can learn more knowledge. 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.