Detailed explanation of transaction isolation level

Transaction isolation level

In database operations, in order to effectively ensure the correctness of concurrently read data, The proposed transaction isolation level.

Recommendation: "mysql tutorial"

Question raising

The database should be shared and accessed by customers. Then the following uncertain situations are likely to occur during database operations.

Update lost

Both transactions update a row of data at the same time, and the update of the data by one transaction overwrites the update of the data by the other transaction. This is because the system does not perform any lock operations, so concurrent transactions are not isolated.

Dirty Read

A transaction reads the uncommitted data operation results of another transaction. This is quite dangerous because it is possible that all operations will be rolled back.

Non-repeatable Reads

Non-repeatable Reads: A transaction reads the same row of data twice, but obtains different results.

Includes the following situations:

(1) Virtual read: After transaction T1 reads certain data, transaction T2 modifies it, and when transaction T1 reads it again This data gets a different value than the previous time.

(2) Phantom Reads: The transaction performs two queries during the operation. The result of the second query contains data that did not appear in the first query or is missing from the first query. The data appearing in (the SQL statements of the two queries are not required to be the same). This is caused by another transaction inserting data during the two queries.

Solution

In order to avoid the above situations, in the standard SQL specification, 4 transaction isolation levels are defined. Different isolation levels have different effects on transactions. Handling is different.

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. This can be achieved through "transient shared read locks" and "exclusive write locks". 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.

Repeatable Read

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

Serialization (Serializable)

Serialization (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.

For more programming related content, please pay attention to the Programming Introduction column on the php Chinese website!

The above is the detailed content of Detailed explanation of transaction isolation level. For more information, please follow other related articles on the PHP Chinese website!