Isolation levels in SQL Server control the way locking works between transactions. A transaction isolation level is defined by the following phenomena:-
NON-REPEATABLE READ:
- Non-Repeatable read occurs when a transaction reads the same row twice and gets a different value each time.
- For example, suppose transaction 1 reads data. Due to concurrency, another transaction 2 updates the same data and commit, Now if transaction 1 rereads the same data, it will retrieve a different value.
- In this result, Name1 is old data and Non-Repeatable Read is a new data.
DIRTY READ:
- This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.
- For Example, Transaction 2 will update data and rollback the changes, Transaction 1 fetches the data while execution Transaction 1 will read uncommitted data.
- In this result, Dirty Read is uncommitted data.
PHANTOM READ:
- During the transaction, new rows are inserted by another transaction to the records being read.
- In this result, Phantom Reads is a newly inserted record.
The following table shows the concurrency side effects allowed by the different isolation levels.
READ COMMITTED:
- In the Read Committed isolation level transaction read only committed data.
- The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating or deleting it.
- It does not allow dirty read. Here Name 2 is a committed record.
READ UNCOMMITTED:
- In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads. In this level, transactions are not isolated from each other.
- In the Read Uncommitted, Transaction read the uncommitted data. It allows dirty read.
SERIALIZABLE:
- Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.
- Serializable helps to avoid dirty read, Non-repeatable read, and phantom read also it minimizes the concurrency.
If you are a newbie to database learning- SQL Server recommended is the following must-watch video: -
