Isolation level
The database system has four isolation levels. The isolation level of the database should be carefully analyzed, because
1. Maintaining the highest isolation level may prevent data errors, but it may cause a loss of parallelism and increase the possibility of deadlock.
2. However, lowering the isolation level may cause some bugs that are hard to find.
Serializable (serialization)
Add a range lock (such as a table lock or a page lock) until transaction a ends. To prevent other operations such as insert and update in the range of trasaction B.
Phantom read, dirty read, and non-repeated read won't happen.
Repeatable read (Repeatable read)
For read records, add a shared lock until transaction a ends. Other Transaction B's attempt to modify this record will wait until trasaction A ends.
Possible problems: Phantom reads may occur when a range query is executed.
Read committed)
When reading data in trasaction A, add a shared lock to the record, but release immediately after reading ends. Other Transaction B's attempt to modify this record will wait until the reading process in a ends, without the end of the entire trasaction. Therefore, the reading results for the same record may be different at different stages of trasaction.
Possible problems: repeated reading is not allowed.
Read uncommitted (uncommitted read)
No shared lock is added. Therefore, other trasaction B can modify the same record during the reading process of the record by trasaction A, which may cause the data read by a to be damaged or incomplete or incorrect.
In addition, you can read the data modified in trasaction B (not submitted) in trasaction. For example, trasaction B modified the r record but did not submit it. In this case, the R record is read in trasaction A and the modified data is read.
Possible problems: Dirty reading.
Four isolation levels
