Dirty reads, non-repeated reads, and Phantom reads of databases

Database dirty reads, non-repeated reads, and Phantom reads are all related to the isolation of transactions. First, let's take a look at the four features of transactions.

Four major features of transactions (ACID ):

1. Atomicity: a transaction is the logical unit of work of the database. It either executes all the modifications to the database or does not execute all the modifications.
2. Consistemcy: before and after a transaction, the database status meets all integrity constraints.
3. Isolation (Isolation): N transactions that are concurrently executed are isolated. One transaction does not affect one, and one transaction does not have a commit, the modified data cannot be viewed by other transactions (by setting the database isolation level ).
4. Durability: Durability means that when the system or media fails, the updates of committed transactions cannot be lost. Durability mainly lies in the restoration performance of DBMS.

Dirty read:

Dirty reading is also called Invalid Data Reading. A transaction reads data that has not been committed by another transaction, which is called dirty read.

For example, transaction T1 modifies a row of data but has not yet been committed. At this time, transaction T2 reads the data modified by transaction T1, and then transaction T1 Rollback for some reason, the data read by transaction T2 is dirty.

Solution: Adjust the transaction isolation level of the database to READ_COMMITTED.

Repeatable read:

Non-repeated read means that two identical queries in the same transaction return different results.

For example, when transaction T1 reads a data, and transaction T2 reads and modifies the data, T1 reads the data again to verify the read value and obtains different results.

Solution: Adjust the transaction isolation level of the database to REPEATABLE_READ.

Phantom read:

For example, system administrator A changes the score of all students in the database from the specific score to the ABCDE level, but System Administrator B inserts A record of the specific score at this time, after System Administrator A completes the change, he finds that another record has not been changed, just like an illusion. This is Phantom read.

Solution: Adjust the transaction isolation level of the database to SERIALIZABLE_READ.

The level of dirty read, non-repeated read, and phantom read is: Dirty read <Non-repeated read <phantom read. Therefore, if you set the highest level of SERIALIZABLE_READ, you do not need to set REPEATABLE_READ and READ_COMMITTED.