When using transactions at database, we may fall into some problems like below.
- Dirty Read – It is a situation which occurs when one transaction T1 updates a record in a database row but doesn’t commit the same. Here another transaction T2 comes and read that database row which is not yet committed. But T1 after that rolls back the transaction reverting the row back to it’s previous version. So the read which T2 did was an incorrect one. This is called as a dirty read.
- Non Repeatable read – It is a situation in which one transaction T1 tries to read a row which is locked by some other transaction T2 so it reads the last version of the row which was there. Some DB implementation may allow this. Then T2 commits row and updates the value of a column. Then T1 again tries to read the same row later as part of it’s own transaction. This it gets a new value This is called Non repeatable read
- Phantom Read – Phantom read means different number of rows are returned on different calls. Suppose a transaction T1 reads some specific amount of rows using a select query.Now another transaction T2 inserts few rows into the same table. Now T1 again fires the same select query later which it fired earler. Now it gets the additional rows. This is called phantom reads.
Hope you understood all such type of reads when dealing with Sql Transactions.
Now let’s look into some isolation levels which are there in relational databases.
- Read Uncommitted – In this isolation level, a transaction reads rows whether it is commited or uncommitted. So the transactions are not isolated here.
So it facilitates dirty read, non repeatable read as well as phantom reads. It is the lowest isolation offered by SQL and generally not preferred in modern systems but it depends on your application logic.
2. Read Committed – In this isolation level, a transaction reads rows only if it is committed.
It facilitates non repeatable read as well as phantom reads. You are thinking that why it facilitates non repeatable read if it reads only committed data. Some database might allow a transaction T1 reading from a rows from a snapshot version if another transaction T2 has held a lock on the same rows and still not commited it.
3. Repeatable Read – The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read. This isolation level is the most restrictive of all and is the default isolation level for many systems. But then, it depends on your system.
4. Serializable – In this highest isolation level, transactions are executed serially one after the another.Actually the transactions are concurrent, but they look like they are executing serially.