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

This article mainly introduces what the isolation level of database transactions is, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

Database transaction isolation level: 1, Read Uncommitted (read uncommitted); 2, Read Committed (read committed); 3, Repeatable Read (repeatable read); 4, Serializable (serializable).

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In database operations, a Transaction is an inseparable unit of work composed of one or more SQL statements that operate the database, all of which are either completed or cancelled.

Database transaction isolation level

In practical application, the data in the database is accessed by multiple users. When multiple users operate the same data at the same time, there may be some transaction concurrency problems, as follows.

1) dirty reading

Refers to a transaction that reads uncommitted data from another transaction.

2) non-repeatable

It means that a transaction repeatedly reads the same row of data twice, but gets different results.

3) false reading / illusory reading

A transaction executes a query twice, but the result of the second query contains data that does not appear in the first query.

4) missing updates

Two transactions update a row of data at the same time, and a transaction that commits (or undoes) later overwrites the data committed by the previous transaction.

Lost updates can be divided into two categories, namely, the first type of lost updates and the second type of lost updates.

The first kind of missing update means that when two transactions operate on the same data at the same time, when the first transaction is undone, the updated data of the committed second transaction is overwritten, and the second transaction results in data loss.

The second kind of missing update means that when two transactions operate the same data at the same time, after the first transaction successfully commits the modification result, it overwrites the modification result that has been committed by the second transaction, resulting in data loss to the second transaction.

In order to avoid the above transaction concurrency problems, four transaction isolation levels are defined in the standard SQL specification, and different isolation levels deal with transactions differently. The isolation levels for these four transactions are as follows.

1) Read Uncommitted (read not submitted)

During the execution of a transaction, you can access not only newly inserted data that has not been committed by other transactions, but also uncommitted modified data. If one transaction has already started writing data, the other transaction does not allow simultaneous write operations, but allows other transactions to read this row of data. This isolation level prevents the loss of updates.

2) Read Committed (read submitted)

During the execution of a transaction, you can access not only the newly inserted data successfully committed by other transactions, but also the successfully modified data. Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row. This isolation level can effectively prevent dirty reading.

3) Repeatable Read (repeatable read)

During the execution of a transaction, you can access the newly inserted data successfully committed by other transactions, but you cannot access the successfully modified data. Transactions that read data will prohibit writing transactions (but read transactions are allowed), and write transactions will prohibit any other transactions. This isolation level effectively prevents unrepeatable and dirty reads.

4) Serializable (serializable)

Provide strict transaction isolation. It requires transaction serialization execution, and transactions can only be executed one after another, not concurrently. This isolation level effectively prevents dirty, unrepeatable, and phantom reading. However, this level may lead to a large number of timeouts and lock competition, which is rarely used in practical applications.

In general, the higher the isolation level of the transaction, the more you can ensure the integrity and consistency of the database, but relatively speaking, the higher the isolation level, the greater the impact on concurrency performance. Therefore, the isolation level of the database is usually set to Read Committed, that is, read committed data, which can not only prevent dirty reading, but also have better concurrency performance. Although this isolation level can lead to concurrency problems such as unrepeatable reads, phantom reads, and type II lost updates, it can be controlled by using pessimistic and optimistic locks in your application.

Thank you for reading this article carefully. I hope the article "what is the isolation level of database transactions" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.