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

This article will explain in detail what is the isolation level and concept of database transactions. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The concept of transaction isolation

Any database that supports transactions must have four features, namely:

Atomicity (Atomicity)

Consistency (Consistency)

Isolation (Isolation)

Persistence (Durability)

The above four points are often referred to as transaction ACID, and the database has the above characteristics to ensure the correctness of the data in the transaction (Transaction). Among them, the isolation of transactions means that transactions are independent of each other and will not affect each other, that is, the operations and data used within a transaction are isolated from other concurrent transactions.

What is the problem without transaction isolation?

If there is no transaction isolation, there may be dirty reads, unrepeatable reads, phantom reads, and so on.

In order to help and deepen our understanding, we use the familiar "cucurbit" as an example. At first, there were seven gourds on the vine. Whenever a cucurbit was born, the old man would enter his information into the database table, as follows:

One day, the fourth child was born. Transaction A (Grandpa) accesses the database in order to add information about the newly born cucurbit to the database, but the transaction has not yet been committed.

Insert into T values (4, 'four babies', 'spray water')

At this time, came another transaction B (snake essence), she carried out the query operation, want to query all the information of the birth of the cucurbit.

Select * from T; / / the information of four children appears in the result.

At this time, if there is no effective isolation between transactions, then the four children's information will appear in the results returned by the snake query database, which is dirty reading.

(1) dirty reading: reading data that has not been submitted by others in one's own affairs

The next day, transaction A (snake essence) queried the information about Siwa in the database early in the morning.

Select * from T where ID = 4; / / name: four children's ability: spray water

At this time, transaction B (Grandpa) came, because Grandpa found that four children can actually breathe fire instead of water, so an update will change the four children's ability to breathe fire, and then submitted the transaction.

Update T set Ability= 'Spitfire' where ID = 4

Then, the snake essence (transaction A) wants to view the information used to confirm the four children again, so he executes it again.

Select * from T where ID = 4umbram / name: Siwa ability: fire breathing

At this time, she was surprised to find that she had different abilities when she read out the information of the four children twice. This is unrepeatable.

(2) non-repeatable reading: it is read twice in one's own transaction, and the data before and after it is different.

The third day

Transaction A (snake essence) accesses the database, and she wants to see which cucurbits have been born, so she executes

Select * from T; / / found a total of four cucurbit messages

At this time, because the five babies were born, transaction B (grandpa) opened the database and entered the information of the five children.

Insert into T values (5, 'five', 'spray')

At this time, transaction A (snake essence) wanted to query all the born cucurbit information again for confirmation, so he executed the query again.

Select * from T; / / found a total of five pieces of information about cucurbits.

At this time, transaction A (snake spirit) may be fooled and think that he is hallucinating. This situation is called phantom reading.

(3) Phantom reading: a concurrent modification transaction between two consecutive lookups in one's own transaction modifies the data set of the query, causing the two queries to return different results (Note: unrepeatable reading is very similar to phantom reading. The focus of non-repeatable reading is modification, while the focus of phantom reading is on adding or deleting)

Isolation level of the database

To avoid the above situation, we can set the isolation level of the database (choose the most appropriate level combined with the actual scenario). General databases include the following four isolation levels:

Read unsubmitted (Read Uncommitted)

Read submit (Read Committed)

Repeatable read (Repeated Read)

Serialization (Serializable)

Here, taking MySQL as an example, the transaction isolation level in MySQL is divided into the following four levels:

Level 0: anything can happen to TRANSACTION_READ_UNCOMMITTED

Level 1: dirty reading cannot occur in TRANSACTION_READ_COMMITTED--, non-repeatable reading and phantom reading can occur.

Level 2: dirty reading and repeated reading can not occur in TRANSACTION_REPEATABLE_READ--, but phantom reading can occur.

Level 3: TRANSACTION_SERIALIZABLE-none of this can happen

(1) read unsubmitted (TRANSACTION_READ_UNCOMMITTED)

Read unsubmitted means that you can read unsubmitted content. Because queries are unlocked under this isolation level, "dirty reads", "non-repeatable reads" and "phantom reads" may occur. This isolation level will not be used in actual development unless there are special circumstances.

(2) read and submit (TRANSACTION_READ_COMMITTED)

Read submission, which means that you can only read what has been submitted. This is the most commonly used isolation level and the default level for Oracle and SQLServer, which effectively avoids dirty reads. (note: queries are unlocked by default unless locks such as shared and exclusive locks are displayed. Different from "read unsubmitted", "read submit" can avoid dirty reading because of the use of "snapshot read")

(3) repeatable (TRANSACTION_REPEATABLE_READ)

Repeatable, this level can effectively avoid "non-repeatable read", which is also the default level of MySQL database innodb. At this level, ordinary queries also use "snapshot reads", but unlike "read commit", Update operations are not allowed when the transaction starts, while "non-repeatable reads" are caused by data modifications between reads. Therefore, "repeatable reading" can effectively avoid "unrepeatable reading", but can not avoid "phantom reading", because phantom reading is caused by "insert or delete operation".

(4) serialization (TRANSACTION_SERIALIZABLE)

Serialization is the highest isolation level of the database, under which transaction serialization is executed in a queued order, which can avoid dirty reading, unrepeatable reading and phantom reading. With high security, the execution efficiency is low, and the performance overhead is the biggest, so it is seldom used in actual development.

Snapshot read

Database read is divided into: consistent unlocked read, locked read, the above mentioned "snapshot read" that is, unlocked read. It simply means that a snapshot is generated when the SELECT statement is executed.

Note: snapshot reads are different at different transaction isolation levels:

Under the READ COMMITTED isolation level, a snapshot is regenerated for each read in the transaction, so each snapshot is up to date. Therefore, every time you execute SELECT in a transaction, you can also see other changes that have been made by the commit transaction, because the snapshot is read, so dirty reading is effectively avoided. If no snapshot read (consistent unlocked read) is used, then when one updated transaction is not committed, another transaction querying the updated data will be blocked because it cannot be queried. In this case, the concurrency ability and efficiency are relatively poor.

Under the REPEATED READ isolation level, a snapshot is generated when the first SELECT statement is executed in a transaction, and Update updates the snapshot only if changes are made to the data in this transaction, so only changes made by other committed transactions before the first SELECT can be seen.

The isolation of transactions means that transactions are independent of each other and will not affect each other, that is, the operations and data used within a transaction are isolated from other concurrent transactions. When there is no transaction isolation, dirty reading, phantom reading, non-repeatable reading and so on may occur. The above problems can be effectively avoided by setting a reasonable isolation level according to the actual situation.

There are four common isolation levels in the database: read uncommitted, read commit, repeatable read, and serialization, among which read commit is more commonly used in actual development. In which leads to a concept of "read snapshot", it should be noted that there are differences in "read snapshot" under different isolation levels, through the use of "read snapshot" to improve the efficiency when concurrent operations occur.

About the database transaction isolation level and concept is shared here, I hope 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.