SQL Server Interview Question #159

What is SNAPSHOT isolation?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

SNAPSHOT isolation uses row versioning so a transaction can read a transactionally consistent snapshot of committed data as of the time the transaction began, rather than taking traditional shared locks for those reads.

It can reduce reader-writer blocking, but it requires the database option ALLOW_SNAPSHOT_ISOLATION and uses the row-versioning infrastructure. Update conflicts can occur when concurrent transactions modify the same data.

SNAPSHOT should be selected based on consistency and concurrency requirements rather than viewed simply as a faster isolation level.

Code Example

ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT *
FROM dbo.Products;

COMMIT;