Understanding dirty Read + non-repeatable READ + Phantom read in database

Read behavior is a condition that can be encountered when multiple transactions are executing concurrently, while reading data. Understanding them first helps you understand the meaning of each isolation level. These include dirty reads, non-repeatable reads, and Phantom reads.

Dirty read:

Dirty read also known as invalid data readout, refers to in the database access, transaction T1 a value modification, and then the transaction T2 read the value, and then T1 for some reason to revoke the modification of the value, which causes the T2 read the data is invalid.

Dirty reading means that when a transaction is accessing the data and the data has been modified and the modification has not been committed to the database, another transaction accesses the data and then uses that data. Because this data is data that has not yet been submitted, the data that is read by another transaction is dirty, and the operation based on dirty data may not be correct.

Non-repeatable READ:

Non-repeatable reading refers to the fact that in database access, two identical queries within a transaction range return different data. This is caused by the commit of the other transaction modifications in the system during the query. For example, transaction T1 reads a certain data, transaction T2 reads and modifies the data, and T1 reads the data again in order to verify the read value, it gets different results.

A more understandable argument is that the same data is read multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data of the first transaction. Because of the modification of the second transaction, the data that the first transaction reads may be different, so that the data that is read two times within a transaction is not the same, so called non-repeatable read, that is, the original read is not repeatable.

Phantom reading

Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, such as "All rows of data" in the table. At the same time, the second transaction modifies the data in the table by inserting a "new row of data" into the table. Then, the user who operates the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred. The general solution to Phantom reading is to increase the range lock ranges, locking the lock range is read-only, which avoids phantom reading.

Solution Solutions

To resolve read phenomena such as dirty reads, non-repeatable reads, and Phantom reads, you need to increase the isolation level of the transaction. At the same time, however, the higher the isolation level of the transaction, the lower the concurrency capability. Therefore, the reader is also required to weigh the business needs.

Understanding dirty Read + non-repeatable READ + Phantom read in database