Read Phenomena
Concurrent transactions accessing the same resource at the same time can lead to read issues. To understand database isolation, we need to understand the different read phenomena which may occur during the execution of concurrent transactions.
Let's consider an example of the database of a clothing store. A sample table from the database with customer purchases is illustrated below.
Dirty Read
A dirty read is a reading issue that occurs when uncommitted data is read from the database.
Suppose transaction 1 wrote the last row in the database but did not commit it yet. At the same time, transaction 2 accesses the same resource and reads data from the table.
Since transaction 1 did not commit the changes it made yet, it will try to read uncommitted data. This is a problem, as if transaction 1 fails, the data may be rolled back, changing the values. This would lead to transaction 2 reading wrong values.
Non-repeatable Read
Non-repeatable read occurs when the same row is read twice from the database.
In our example, suppose transaction 1 updated a value in the database. As transaction 2 is running concurrently, it updates the same value in the database to some other value. When transaction 1 tries to access that value again, this value has changed from what this transaction wrote earlier.
Phantom Read
Phantom read refers to the problem where similar queries fetch different results from the same location in the database. The result may have changed during some time between the two queries.
Suppose transaction 1 ran a query on the database. Sometime later, concurrently running transaction 2 accessed the same resource and updated the table by adding new entries. When transaction 1 runs the same query again, it finds different output as compared to what it received earlier.
