In this article, we will cover violations around database transactions and the isolation levels in Database Management Systems with proper examples.
Table of contents
What is isolation?
Isolation means the process or fact of isolating or being isolated.
Isolation describes how changes applied by concurrent transactions are visible to each other. Or we can say how concurrent transactions are isolated from each other.
Say there are 2 concurrent transactions, Transaction T1 and Transaction T2. If Transaction T1 makes some changes to a database then how that changes will be visible to other Transaction T2 this is what an Isolation level decides.
There are 4 types of isolation levels: READ_UNCOMMITTED, READ_COMMITED, REPETABLE_READ, and SERIALIZABLE.
Each isolation level prevents zero or more side effects on a transaction. Firstly we will understand what are those side effects.
Side effects or violations of the transaction
Dirty read
Reads the uncommitted change of a concurrent transaction. Whenever a transaction reads something which has not been committed yet is known as a dirty read.
Ex: Transaction T1 makes some database update and did not commit the change still the updated changes will be visible to Transaction T2.
Consider we have a Student table with 4 columns: id, name, subject, marks
- Transaction T1 enters a row in the Student table and commits
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 80 |
- Transaction T1 updates the previous entry. Updates the marks from 80 to 85 but is not committed yet
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 85 |
- Transaction T2 reads the entry from the Student table whose id is ‘101’
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 85 |
- Because of some issue or some exception T1 has to be rolled back. So the marks will be rolled back to 80 for Transaction T1.
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 80 |
- Now Transaction T1 is having marks of 80 whereas Transaction T2 is having marks of 85. Thus Transaction T2 has done a dirty read because this value was never committed.
Nonrepeatable read
Getting different values on re-read of a row if a concurrent transaction updates the same row and commits.
Ex: Transaction T1 reads a piece of information and Transaction T2 updates the same information and commits. Now if Transaction T1 again tries to read the same data it will find the updated information. Here we are repeating the read operation which is causing a violation.
Consider we have a Student table with 4 columns: id, name, subject, and marks with some database entries.
- Transaction T1 reads an entry from the Student table whose id is ‘101’
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 80 |
- Transaction T2 updates the marks of the student whose id is ‘101’ and commits
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 85 |
- Transaction T1 again reads an entry in the Student table whose id is ‘101’
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 85 |
- Here Transaction T1 is getting different values for the same read operation and this is a violation. We should not get different values for the same query within the same transaction.
Phantom read
Getting different rows after re-execution of a range query if another transaction adds or removes some rows in the range and commits.
Ex: Transaction T1 reads some entries with a specific condition and it fetches some results. Now Transaction T2 adds or deletes a new row with the same condition. Now if Transaction T1 tries to read the data with the same condition then the result varies from the previous result.
Consider we have a Student table with 4 columns: id, name, subject, and marks with some database entries.
- Transaction T1 read entries from the Student table where the mark is ’80’ and the subject is ‘Maths’
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 80 |
| 105 | Iqbal | Maths | 80 |
- Transaction T2 triggers an insert query on the Student table with marks as ’80’ and subject as ‘Maths’
| ID | NAME | SUBJECT | MARKS |
| 107 | Suchit | Maths | 80 |
- Transaction T1 read entries from the Student table where the mark is ’80’ and the subject is ‘Maths’
| ID | NAME | SUBJECT | MARKS |
| 101 | Suraj | Maths | 80 |
| 105 | Iqbal | Maths | 80 |
| 107 | Suchit | Maths | 80 |
- Transaction T1 is executing the same query but the result varies in both cases. A new row pops up and this is known as a phantom row.
Different Isolation levels in DBMS
There are 4 types of isolation levels: READ_UNCOMMITTED, READ_COMMITED, REPETABLE_READ, and SERIALIZABLE.
READ_UNCOMMITTED
Say we have a table with 3 records A | B | C.
Transaction T1 changes the value from B to Z but has not committed yet.
Note* When we change some record we have to exclusively lock that row and Transaction T1 got the lock for that row.
Now Transaction T2 reads the data with Isolation level as READ_UNCOMMITTED. READ_UNCOMMITTED means we do not need to acquire a shared lock hence there is no blocking and we can even read the uncommitted data.
So the select query will read A | Z | C. The value of Z is a dirty read.
Imagine Transaction T1 rolls back, so the data in the Transaction T1 will be A | B | C whereas the data in Transaction T2 will be A | Z | C.
Z is a dirty read because it was never a part of our database still it is shown as part of Transaction T2.
READ_COMMITTED
Say we have a table with 3 records A | B | C.
Transaction T1 changes the value from B to Z but has not committed yet.
Here Transaction T1 will hold a mutually exclusive lock on that row till it is committed. Here the new records will be A | Z | C. The update statement also copies the old committed value of A | B | C into some version stores.
Now Transaction T2 reads the data with Isolation level as READ_COMMITTED.
Transaction T2 tries to get the lock for the row but it cannot get the lock as Transaction T1 has already locked that row.
Now the data will be fetched from the version store and Transaction T2 will have the valid data. In the READ_COMMITTED isolation level, we do not have any dirty reads.
REPETABLE_READ
Say we have a table with 3 records A | B | C . Transaction T1 reads this table and fetches the results as A | B | C. While reading the data Transaction T1 will get a shared lock on those rows and once the read is done it will be released.
Transaction T2 updates the value from B to Z. While updating data, Transaction T2 gets the mutual exclusive lock and updates the data.
Now Transaction T1 again tries to fetch data from the same table this time it gets the result as A | Z | C. This is the problem statement we are getting different results for the same query for the same Transaction T1.
Solution: We will apply the isolation level as REPETABLE_READ. Now when Transaction T1 reads this table for the first time it fetches the results as A | B | C. While reading the data Transaction T1 will get a shared lock on those rows and will not release the lock till the commit happens.
Transaction T2 tries to update a record. For updating a record Transaction T2 needs to get the exclusive lock but since Transaction T1 has already acquired a lock on those rows so Transaction T2 cannot perform any operation on those rows till Transaction T1 release the lock hence Nonrepeatable read violation is taken care of using REPETABLE_READ isolation level.
SERIALIZABLE
This isolation level will prevent phantom records. Imagine we have a table with 6 records AA | BB | CC | AA | BB | CC. Transaction T1 reads a specific range of data ( i.e ) it reads all AA records hence 2 records will be fetched.
Transaction T2 inserts a new record AA in the above table.
Now Transaction T1 again tries to read all AA records. This time 3 records will be read. This is the problem statement we are getting different results for the same range query for the same Transaction T1.
Solution: We will apply the isolation level as SERIALIZABLE. Now for the first time when Transaction T1 tries to read all AA records, it will fetch 2 records and will apply a Key Range Locking for these 2 records.
Now if Transaction T2 tries to insert a new row with the same key ( AA in this case ) Here Transaction T2 tries to get the exclusive lock for insertion but as a Key Range Locking is already applied for this key hence that insertion cannot be done and Transaction T2 has to wait till Transaction T1 commits and releases the lock. Hence Transaction T1 will always see 2 records for that range query. Thus SERIALIZABLE isolation level prevents the phantom reads.
I hope you found this article interesting and valuable. If you are having any concerns or questions about this article please comment below.