SET TRANSACTION ISOLATION LEVEL is the controls that lock and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. There are 5 levels for SET TRANSACTION ISOLATION LEVEL. There are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE. By default, SQL Server set ‘SET TRANSACTION ISOLATION LEVEL READ COMMITTED’ as default. So what does each of them mean?

READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed.

READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data.

REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

SNAPSHOT
Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. 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
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.

Tags: Transact-SQL