What are the database transaction isolation levels?

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 (Transaction) is an indivisible unit of work composed of one or more SQL statements that operate the database. These operations are either completed or canceled.

Database transaction isolation level

In practical applications, the data in the database is accessed by multiple users. When users operate the same data at the same time, some transaction concurrency issues may occur, as detailed below.

1) Dirty reading

means that one transaction reads uncommitted data from another transaction.

2) Non-repeatable read

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

3) Virtual read/phantom read

means that a transaction executes two queries, but the result of the second query contains data that did not appear in the first query.

4) Lost update

means that two transactions update a row of data at the same time, and the transaction submitted (or revoked) later overwrites the data submitted 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 type of lost update refers to when two transactions operate the same data at the same time. When the first transaction is canceled, the updated data of the second transaction that has been submitted is overwritten. , the second transaction caused data loss.

• The second type of lost update refers to when two transactions operate on the same data at the same time. After the first transaction successfully submits the modification results, the modifications that have been submitted by the second transaction are The result was overwriting, resulting in data loss for the second transaction.

In order to avoid the above transaction concurrency problems, four transaction isolation levels are defined in the standard SQL specification. Different isolation levels handle transactions differently. The isolation levels of these four transactions are as follows.

1) Read Uncommitted (read uncommitted)

During the execution of a transaction, it can access both newly inserted data that has not been committed by other transactions and Uncommitted modified data. If a transaction has started writing data, another transaction is not allowed to write data at the same time, but other transactions are allowed to read this row of data. This isolation level prevents lost updates.

2) Read Committed

During the execution of a transaction, it can access both the newly inserted data successfully submitted by other transactions and the Successfully modified data. The transaction that reads the data allows other transactions to continue to access the row of data, but the uncommitted write transaction will prevent other transactions from accessing the row. This isolation level effectively prevents dirty reads.

3) Repeatable Read

During the execution of a transaction, it can access newly inserted data successfully submitted by other transactions, but it cannot Access successfully modified data. Transactions that read data will disable write transactions (but allow read transactions), and write transactions will disable any other transactions. This isolation level effectively prevents non-repeatable reads and dirty reads.

4) Serializable (serializable)

Provides strict transaction isolation. It requires transactions to be executed serially, and transactions can only be executed one after another and cannot be executed concurrently. This isolation level effectively prevents dirty reads, non-repeatable reads, and phantom reads. However, this level may lead to a large number of timeouts and lock competition, and is rarely used in practical applications.

Generally speaking, the higher the isolation level of a transaction, the better it can ensure the integrity and consistency of the database. However, 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, which means reading committed data, which can prevent dirty reads and provide better concurrency performance. Although this isolation level can lead to concurrency problems such as non-repeatable reads, phantom reads, and type 2 lost updates, they can be controlled by using pessimistic and optimistic locking in the application.

Related free learning recommendations: mysql video tutorial

The above is the detailed content of What are the database transaction isolation levels?. For more information, please follow other related articles on the PHP Chinese website!