It is quite common that when a new feature gets introduced, there is a lot of talk about them. Sometimes even the old capabilities are lesser known because they can be used interchangeably in conversations. I have seen many DBAs getting confused between these two terms because both of them have a word snapshot in it.
- Read Committed Snapshot
- Snapshot Isolation level
I must point out that first one is NOT an isolation level. It is the behavior in reading committed isolation level, which gets activated only if we turn on database level property. Here are the commands to change them.
ALTER DATABASE SQLAuthority SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE SQLAuthority SET ALLOW_SNAPSHOT_ISOLATION ON
If you are having problem with blocking between readers (SELECT) and writers (INSERT/UPDATE/DELETE), then you can enable first property without changing anything from the application. Which means application would still run under read committed isolation and will still read only committed data.
Let us have a look at this with an example:
Data Preparation
SET NOCOUNT ON
GO
USE MASTER
GO
IF DB_ID('SQLAuthority') IS NOT NULL
BEGIN
ALTER DATABASE SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE SQLAuthority
END
GO
CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE DemoTable(
i INT
,j VARCHAR(100)
)
GO
USE SQLAuthority
GO
INSERT INTO DemoTable VALUES (1, 'ONE')
GO
INSERT INTO DemoTable VALUES (2, 'TWO')
GO
INSERT INTO DemoTable VALUES (3, 'THREE')
GO
Read Committed (Default Behavior)
Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1
Session 2
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM DemoTable
WHERE i = 1
Result – Query in Session 2 is blocked.
Session 1
ROLLBACK
Result – Query in Session 2 would be unlocked and would show result. It would show last committed data. Since we have done rollback in session 1, we would see original values. (1, ONE)
Read Committed Snapshot
Change the database property as below
ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO
Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1
Session 2
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM DemoTable
WHERE i = 1
Result – Query in Session 2 shows old value (1, ONE) because current transaction is NOT committed. This is the way to avoid blocking and read committed data also.
Session 1
COMMIT
Session 2
USE SQLAuthority
GO
SELECT *
FROM DemoTable
WHERE i = 1
Result – Query in Session 2 shows no rows because row is updated in session 1. So again, we are seeing committed data.
Snapshot Isolation Level
This is the new isolation level, which was available from SQL Server 2005 onwards. For this feature, there is a change needed in the application as it has to use a new isolation level.
Change database setting using below. We need to make sure that there is no transaction in the database.
ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON
Now, we also need to change the isolation level of connection by using below
Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2
Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM DemoTable
WHERE i = 2
Result- Even if we have changed the value to 10, we will still see old record in session 2 (2, TWO).
Now, let’s commit transaction in session 1
Session 1
COMMIT
Let’s come back to session 2 and run select again.
Session 2
SELECT *
FROM DemoTable
WHERE i = 2
We will still see the record because session 2 has stated the transaction with snapshot isolation. Unless we complete the transaction, we will not see latest record.
Session 2
COMMIT
SELECT *
FROM DemoTable
WHERE i = 2
Now, we should not see the row as its already updated.
In summary, SQL Server 2005 onwards, SQL engine provides only one new isolation level AND an optimistic implementation of READ COMMITTED. Isolation level SNAPSHOT is a new isolation level and READ COMMITTED SNAPSHOT is the same isolation level as READ COMMITTED but is the optimistic implementation of it.
I would be glad and happy to know the scenarios you have used to play around with Snapshot isolation for your servers. Do let me know via comments below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
Excellent !!
I have one question in mind, as a developer when do I need to use “Snapshot Isolation Level” option in practise ?
Thanks
There are many situations. you can search and find use case scenarios for this.
Hi Chirag
Hope it will help you.
Hello Pinal,
Please make me correct if i am wrong.
Isolation levels in SQL server and why high isolation levels:-
In SQL Server we have below types of transactions:-
• Implicit transaction:-
• Explicit transaction:-
• Batch transaction:-
• Distributed transaction:-
• Implicit transaction:-in SQL server every DML statement execute as implicit transaction, implicit transaction can be auto committed or not. By default it is auto committed as implicit_transactions configured with OFF value. While execution of implicit transaction SQL server load all respective data pages from physical memory into buffer cache and perform transaction execution in tempdb and updates respective data pages in buffer cache only till complete execution of statement means till commit. Once it is committed , all changes get reflected in main physical memory.
• Explicit transactions:- collections of multiples implicit transactions and execute as a single logical unit called as explicit transaction.
In SQL server, every DML (Insert, Update, Delete) statement execute as new implicit transaction and execute in buffer cache by loading all respective table data pages in buffer cache till commit or successful completion of statement and final changes get reflected in physical memory. But if we are talking about select statement, it directly get data from physical memory every time means no transaction scope at all.
Consider below examples:
Example 1:
begin transaction
update employee set A.name=’AshishN’ where a.name=’Ashish’
select A.* from employee as A
——-
——-
select A.* from employee as A
commit transaction
Example 2:
begin transaction
select A.* from employee as A
——-
——-
select A.* from employee as A
commit transaction
We are fine with read committed isolation level for execution of implicit transactions but once we start working with explicit transaction we should visit high isolations for some scenarios only.
• Repeatable read isolation level
• Serializable isolation level
• Snapshot isolation level
For read committed isolation level, for example 1, as we are executing DML as first statement in explicit transaction, SQL server load all respective data of employee table from physical memory to buffer cache and keep exclusive lock on employee table till completion of complete explicit transaction. Once data get load into buffer cache for explicit transaction, remaining all statement from same explicit transaction refer data from buffer cache directly(as employee table have exclusive lock and no one can access table till release of exclusive lock). Select statement in same explicit transaction get data from buffer cache directly, not from physical memory as we have data in buffer cache already
As it is DML and load all respective data in buffer cache by keeping exclusive lock on employee table, the lock will get release only after completion of explicit transaction means after execution of commit or rollback. And because of exclusive lock kept on employee table till completion of explicit transaction, no other transaction can modify employee table till completion of current explicit transaction. And because of data loaded into buffer cache , all respective statements in same explicit transaction refer data from buffer cache hence there is no chance to get unexpected data or non-repeated data but we can get modified data modifying in buffer cache by any of the statement of current running explicit transaction.
Above all things possible only for DML in explicit transaction but once we are trying to execute select statement in explicit transaction for read committed isolation level, SQL server keep share lock on employee table only till selection of data means SQL server doesn’t wait till completion of explicit transaction and because of it any other transaction from other session can modify the employee table as it is free for lock and we ca get non repeatable data in same transaction.
To avoid it we need to manage select statement behavior same as DML in explicit transaction and it is not possible with read committed isolation level. For repeatable read isolation level, SQL server load all respective data from employee table in buffer cache and keep share lock on employee table till complete execution current explicit transaction and as SQL server load all data in buffer cache all next coming statements from same explicit transaction refer data from buffer cache only and that’s the reason, we are getting same data in same explicit transaction until it is modifying by same explicit transaction and that’s the reason no other transaction cannot access employee table as it already have share lock till complete execution of current running explicit transaction. Refer example 2
In simple, read committed isolation level is enough if we are using explicit transaction with DML (insert, update, delete) as it load all data in buffer cache and keep exclusive lock on table till completion of explicit transaction.
Read committed isolation level is not enough if we are using explicit transaction with select query as it doesn’t load all respective data into buffer cache for select statement and as it keep share lock on respective table till execution of select statement only ,it doesn’t wait for completion of explicit transaction.
Basically snapshot is support all features as read committed, repeatable read and serializable isolation levels only the thing is that it consume lots of system and SQl server resources as compare to other three.
Thank you very much.
Useful post..thanks.
Rahul – thanks!
Very nicely explained..Thanks Pinal !!
Prajakta – I am glad that you liked it.
Pinal, I played with th code and everything went correctly as stated but not the last scenario. I executed “SET TRANSACTION ISOLATION LEVEL SNAPSHOT, then the update staement in session 1, then the select in session 2 after commiting in both sessions, when I run the select in session 1 I don’t see the row but in session 2, even after commiting, I see the old record
i j
2 TWO
I am running SQL 2014.
By the way, once we issue “ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON”, should we revert back manualy by putting it OF instead of On or it is inly session-wide?
Thanks
Salam – You need to commit transaction locally as well before getting next data,
I have one query.. I have one table with primary key as varchar field eg:PK values AA1.AA2 , AA3. I want to increment it with each insert.. if there are two users simultaneously fetching the value, incrementing and inserting..it will give error for duplicate key.. can we handle this situation with isolation levels? currently i am manully incrementing and inserting after fetching.
Prajakta – No, you need to check the data before insert by using IF NOT EXISTS.
Hey Pinal Thank you so much !! It was of great help. Thanks.
Hello Pinal,
it looks that all isolation level have thee own advantages but snapshot is good one and support all advantages so we would like to go by last one i.e snapshot.
is there any disadvantages of the same by considering performance and storage.
Thank you very much.
Hello Pinal ,
Please make me correct if i am wrong.
for normal transaction i.e every DML statement execute as new implicit transaction and execute in buffer cache by loading all respective table data pages in buffer cache till commit or successful completion of statement.
but if we are talking about select statement , it directly get data from physical memory every time means no transaction scope at all.
please consider below examples:
Example 1:
begin transaction
update employee set A.name=’AshishN’ where a.name=’Ashish’
select A.* from employee as A
——-
——-
select A.* from employee as A
commit transaction
Example 2:
begin transaction
select A.* from employee as A
——-
——-
select A.* from employee as A
commit transaction
for read committed isolation level ,for example 1 ,as we are executing DML as first statement in explicit transaction, sql server get all respective data from employee table from physical memory to buffer cache and keep exclusive lock on employee table till completion of complete explicit transaction and next select statement in same explicit transaction get data from buffer cache directly, not from physical memory as we have data in buffer cache already.
and because of exclusive lock kept on employee table till completion of explicit transaction, no other transaction can modify employee table till completion of current explicit transaction.
and because of data loaded into buffer cache , all respective statements in same explicit transaction refer data from buffer cache hence there is no chance to get unexpected data or non repeated data.
but we can get modified data that gets modified in same explicit transaction but in buffer cache and it is expected.
it satisfied the situation that we are looking means all respective statement executing in same explicit transaction referring data from buffer cache.
for read committed isolation level, for example 2 , sql server start new explicit transaction but as we are executing select statement on employee table, sql server just get data from main physical memory by keeping share lock on employee table till selection and it doesn’t load data in buffer cache from main memory as it is not DML, it means sql server release the lock just completion of select it doesn’t wait for the completion of explicit transaction.
and in that case employee table free to access for another transaction hence another transaction can modify data from employee table even first main transaction still executing.
but if we set isolation level repeatable read and executing example 2.
in that case , sql server load all respective data from physical memory to buffer cache after first select execution and keep share lock on employee table till completion of complete explicit transaction.
and once data get loaded into buffer cache , all statements from same explicit transaction refer data from buffer cache and because of share lock no other transaction can modify the data for employee table.
that’s the cause we need repeatable and serialization isolation level.
in simple, read committed isolation level is enough if we are using explicit transaction with DML(insert,update,delete) as it load all data in buffer cache and keep exclusive lock on table till completion of explicit transaction.
read committed isolation level is not enough if we are using explicit transaction with select query as it doesn’t load all respective data into buffer cache for select statement and as it keep share lock on respective table till execution of select statement only ,it doesn’t wait for completion of explicit transaction.
once we set isolation level as repeatable read or serializable read, sql server load all respective data in buffer cache for select statement and keep share lock on table till complete execution of explicit transaction and once data get load into buffer cache , all further statements from same transaction refer data from buffer cache.
Please suggest me if i am wrong….
hello
Hi Pinal,
i have small question regarding isolation level and locks.
as READ COMMITTED is the default isolation level for SQL Server and it uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed.
but what type of lock does Snapshot isolation level uses, when Snapshot Isolation level is enabled for a database that changes the default READ COMMITTED isolation level of the SQL server.
does Snapshot Isolation still uses the shared lock or a different one?
is there any article that describes different isolation levels with the kind of locking implementation they consider, as i am unable to find such articles.
Please suggest something around this.
Regards,
Ranjan
Hi, thanks very much for this clear and concise explanation. It has enabled me to fix a problem that caused my application to hang due to a deadlock! Our previous testing was in PostgreSQL, which has the default behaviour which is the same as READ_COMMITTED_SNAPSHOT.
Thanks agan.
Your welcome John. I am glad that it helped you.
Hi, Thanks, just a question, How would I change the isolation back from “read committed snapshot” to “read commited”?
There is nothing called “read committed snapshot” isolation level.
You may want to state if example above in article if it solves Dirty Read, NonRepeatable Read, or Phantom row.
Thanks,
Great Idea.