Snapshot isolation introduced in SQL Server 2005 to implement optimistic locking. By default SQL Server use pessimistic locking, Read transaction might block write transaction and also write transaction block read transaction.
Blocking is one of the biggest issue for OLTP applications where reporting style queries need to run without out blocking critical write operation. Prior to Sql Server 2005 these kind of report queries uses Nolock hint to avoid blocking.
Status of snapshot isolation status on particular database can be identified using below query,
SELECT snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on
FROM sys.databases
WHERE name='snapshot' -- Database Name
Snapshot isolation can be classified as,
| Types | Snapshot | Read Committed Snapshot |
| Query to switch on Snapshot | ALTER DATABASE <databasename> SET ALLOW_SNAPSHOT_ISOLATION ON | ALTER DATABASE <databasename> SET READ_COMMITTED_SNAPSHOT ON |
Status change on sys.databases
after enabling Snapshot | snapshot_isolation_state will get updated from 0 --> 1
snapshot_isolation_state_desc will get updated from OFF --> ON | is_read_committed_snapshot_on will get updated from 0 --> 1
|
| Default isolation level for new connection | read committed
Use dbcc useroptions to check default isolation level | read committed snapshot |
| Manually enable isolation at Session Level | Required.
set transaction isolation level snapshot
By using above command isolation level for the session has been changed to Snapshot from read committed | Not required.
By default all sessions are enabled with read committed snapshot isolation
read committed snapshot
|
Read Committed Vs Snapshot Vs Read Committed Snapshot:-
Use below table to understand difference between isolation levels,
--Sample Table
CREATE TABLE employee(employee_id BIGINT identity(1,1) PRIMARY KEY ,employee_first_name VARCHAR(100),employee_last_name VARCHAR(100))
--Sample Data
INSERT INTO employee
SELECT 'Bill','Gates'
UNION ALL
SELECT 'Mark','Zuckerberg'
UNION ALL
SELECT 'Thomas','Watson'
| Session 1 | Session 2 (Read Committed) | Session 2 (Snapshot) | Session 2 (Read Committed Snapshot) |
| BEGIN TRAN | BEGIN TRAN | BEGIN TRAN set transaction isolation level snapshot | BEGIN TRAN |
| UPDATE employee
SET employee_last_name = 'J. Watson'
WHERE employee_id = 3 | SELECT * FROM employee WHERE employee_id=2
System will provide result; Since row with employee_id = 3 only blocked | SELECT * FROM employee WHERE employee_id=2
System will provide result; Since row versioning is used | SELECT * FROM employee WHERE employee_id=2
System will provide result; Since row versioning is used |
| | SELECT * FROM employee WHERE employee_id=3
Since row with employee_id=3 is blocked system will not provide result until session1 gets committed/roll backed. | SELECT * FROM employee WHERE employee_id=3 No Blocking user can able to see output.
| SELECT * FROM employee WHERE employee_id=3 No Blocking user can able to see output.
|
| DELETE FROM employee WHERE employee_id = 3 | SELECT * FROM employee WHERE employee_id=3
Since row with employee_id=3 is blocked system will not provide result until session1 gets committed/roll backed. | SELECT * FROM employee WHERE employee_id=3 No Blocking user can able to see output.
| SELECT * FROM employee WHERE employee_id=3 No Blocking user can able to see output.
|
| COMMIT TRAN | SELECT * FROM employee WHERE employee_id=3
User can able to see no records returned, since block is released. | SELECT * FROM employee WHERE employee_id=3
Even though record is deleted and got committed user can still able to see record with employee_id=3 until this transaction get committed or roll backed. | SELECT * FROM employee WHERE employee_id=3
User can able to see no records returned, since other transaction is committed. |
 | | |  |
 | |  | |
Benefits of Snapshot Isolation:-
- Read transaction do not issue share lock and block write transaction.
- Write transaction do not block read transaction.
Error messages received while using Snapshot Isolation:-
Msg 3952, Level 16, State 1, Line 2
Snapshot isolation transaction failed accessing database 'Snapshot' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
Solution:-
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM employee WHERE employee_id = 3
No error received while setting transaction isolation level. Received following error while executing select query,
Enabling transaction isolation level at database level will fix this error.
ALTER DATABASE <databasename> SET ALLOW_SNAPSHOT_ISOLATION ON
Msg 3951, Level 16, State 1, Line 4
Transaction failed in database 'Snapshot' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level.
Solution:-
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Enabling transaction isolation level before transaction, isolation can be set session level and not at transaction level.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
Msg 3960, Level 16, State 2, Line 3
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.' directly or indirectly in database 'Snapshot' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
Solution:-
| SESSION 1 | SESSION 2 |
| SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE employee
SET employee_last_name = 'Watson1'
WHERE employee_id = 3 WAITFOR DELAY '00:00:05' COMMIT TRAN | SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE employee
SET employee_last_name = 'Watson2'
WHERE employee_id = 3 COMMIT TRAN |
Same record get updated on parallel session causing this issue. In above example record which gets updated by Session1 is updated by Session2 also before Session1 transaction gets completed.
Solution for this problem could be using Read Committed Snapshot Isolation instead of Snapshot.
Disclaimer:-
Before changing your application database isolation level as Snapshot or Read Committed Snapshot on production, perform a complete end to end testing on development environment with isolation level change.
