Microsoft SQL Server: Snapshot isolation

By | March 3, 2016

Snapshot isolation is a guarantee that all reads made in a transaction will see a consistent snapshot of the database.
The transaction itself will successfully commit only if no updates it has made, conflicts with any concurrent updates made since that snapshot.
By using it, deadlock situations when a read operation (intense resource consuming operation) blocks other more vital operations (updates) can be avoided.
To enable snapshot isolation we have to execute the following on all the databases from our Ms SQL instance.
Considering that our database is named myDB

STEP 1
Turn on snapshot isolation on the database:

ALTER DATABASE myDB SET ALLOW_SNAPSHOT_ISOLATION ON;

STEP 2
Set read commit on the snapshot:

ALTER DATABASE myDB SET READ_COMMITTED_SNAPSHOT ON;

Advertisements