What are the transaction isolation levels of the database?

In database operations, in order to effectively ensure the correctness of concurrently read data, the transaction isolation level is proposed. In the standard SQL specification, four transaction isolation levels are defined, and different isolation levels handle transactions differently. The following article will introduce you to the transaction isolation level. I hope it will be helpful to you.

General databases include four isolation levels. Different isolation levels handle transactions differently.

Unauthorized reading

Also known as Read Uncommitted: Dirty reads are allowed, but lost updates are not allowed. If a transaction has started writing data, another transaction is not allowed to write at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through an "exclusive write lock".

Authorized Read

Also known as Read Committed: Non-repeatable reads are allowed, but dirty reads are not allowed. 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 can be achieved through "instant shared read lock" and "exclusive write lock".

Repeatable Read

Non-repeatable reads and dirty reads are prohibited, but phantom read data may sometimes occur. Transactions that read data will disable write transactions (but allow read transactions), and write transactions will disable any other transactions. This can be achieved through "shared read lock" and "exclusive write lock".

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. Transaction serialization cannot be achieved only through "row-level locks". Other mechanisms must be used to ensure that newly inserted data will not be accessed by the transaction that just executed the query operation.

The higher the isolation level, the more complete and consistent the data can be guaranteed, but the greater the impact on concurrency performance. For most applications, you can give priority to setting the isolation level of the database system to Read Committed. It can avoid dirty reads and has better concurrency performance. Although it will lead to concurrency problems such as non-repeatable reads, phantom reads, and type II lost updates, in individual situations where such problems may occur, they can be controlled by the application using pessimistic locking or optimistic locking.

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