Transaction Isolation Levels IN SQL Server 2012

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.

·         No Phantom reads

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.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.(hence you can repeatedly read the same values)

·         Possibility for Phantom read

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 (not in any other transaction), resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

No other transactions can modify data that is being modified by the current transaction until the current transaction completes. but if it transaction just read the record  in its scan not modify, it open for action by other transactions. hence values of few records may change or new record inserted by other transactions when it is just being read by read commited transaction.
·         Possibility for Phantom reads

READ UNCOMMITTED

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

·         Allow dirty read

SNAPSHOT

·         The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

·         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.