Dirty read, Non-repeatable read, and Phantom read

Dirty read

Uncommitted data is read.

Transaction B is rolled back at this time, then the second transaction A reads dirty data which age is 18

Phantom read

When the user reads records, another transaction inserts or deletes rows to the records being read. When the user reads the same rows again, a new “phantom” row will be found.

Transaction B inserts a new row where transaction A reads, then transaction A finds the total count of the result changes to 2

Non-repeatable read

Before transaction A is over, another transaction B also accesses the same data. Then, due to the modification caused by transaction B, the data read twice from transaction A may be different.

The difference between Phantom read and Non-repeatable read：

The key to non-repeatable reading is to modify:
In the same conditions, the data you have read, read it again, and find that the value is different.
The key point of the phantom reading is to add or delete:
Under the same conditions, the number of records read out for the first time and the second time is different.

The isolation levels are used to solve various problems in the database :

1. DEFAULT
   Use the isolation level used by the database itself.
   ORACLE (read has been submitted) MySQL (repeatable read)
2. Read uncommitted
   Reading uncommitted, as the name implies, is that one transaction can read the data of another uncommitted transaction.
3. Read committed
   Read commit, as the name implies, is that a transaction cannot read data until another transaction is committed.
   Solve dirty read, but cannot solve non-repeatable read and phantom read.
4. Repeatable read
   Repeated reading, that is, when starting to read data (transaction is opened), modification operations are no longer allowed.
   Solved non-repeatable read.
5. Serializable serialization
   Serializable is the highest transaction isolation level. Under this level, transactions are serialized and executed sequentially, which can avoid dirty read, non-repeatable read, and phantom read. However, this transaction isolation level is inefficient and consumes database performance, so it is rarely used.

Let’s use a table to show the problems they can solve

+----------------+------------+---------------------+--------------+
|isolation level | dirty read | non-repeatable read | phonatm read |
+----------------+------------+---------------------+--------------+
|Read uncommitted| ×(unsolved)|   ×                 |  ×           |
|Read committed  | √(solved)  |   ×                 |  ×           |
|Repeatable read | √          |   √                 |  ×           |
|Serializable    | √          |   √                 |  √           |
+----------------+------------+---------------------+--------------+