Transaction Isolation Level in SQL Server

Transaction Isolation Level: -

Basically, a Transaction is a logical unit of work or t-sql statement, in which either all should be executed successfully or none of them.

With the help of setting the isolation level of a transaction, we can define, up to which extent a transaction can be inaccessible (or isolated) from the other transaction. Or we can say, Transaction Isolation Level controls the locking and row versioning behavior of a transaction.

There are 5 level of Transaction Isolation Level: -

1)    Read Uncommitted

2)    Read Committed

3)    Repeatable Read

4)    Snapshot

5)    Serializable

Read Uncommitted: -

·         Doesn’t maintain any isolation.

·         Result dirty reads, phantom reads and non-repeatable reads.

Read Committed: -

·         Allow transaction to read only committed data.

·         It protects dirty reads.

·         Not prevent against phantom reads and non-repeatable reads.

Repeatable Read: -

·         Cannot read uncommitted data of other transaction.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads.

·         Protect against both dirty reads and non-repeatable reads.

·         Allows phantom reads.

Snapshot: -

·         It prevents dirty reads, phantom reads and non-repeatable reads.

·         It provides each transaction with a "snapshot" of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.

·         The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Serializable: -

·         It prevents dirty reads, phantom reads and non-repeatable reads.

·         Statements cannot read data that has been modified but not yet committed by other transactions.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

If you need to change the isolation model in use by SQL Server, simply issue the command:

SET TRANSACTION ISOLATION LEVEL <level>