To learn any technology and move to a more advanced level, it is very important to understand the fundamentals of the subject first. Today, we will be talking about something which has been quite introduced a long time ago but not properly explored when it comes to the isolation level.
Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands.
In one of the recent consultation project, I had same experience when developers have “heard about it” but have no idea about snapshot isolation. They were thinking it is the same as Snapshot Replication – which is plain wrong. This is the same demo I am including here which I have created for them.
In Snapshot Isolation, the updated row versions for each transaction are maintained in TempDB. Once a transaction has begun, it ignores all the newer rows inserted or updated in the table.
Let us examine this example which shows the simple demonstration. This transaction works on optimistic concurrency model. Since reading a certain transaction does not block writing transaction, it also does not block the reading transaction, which reduced the blocking.
First, enable database to work with Snapshot Isolation. Additionally, check the existing values in the table from HumanResources.Shift.
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO
Now, we will need two different sessions to prove this example.
First Session: Set Transaction level isolation to snapshot and begin the transaction. Update the column “ModifiedDate” to today’s date.
-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE HumanResources.Shift
SET ModifiedDate = GETDATE()
GO
Please note that we have not yet been committed to the transaction. Now, open the second session and run the following “SELECT” statement. Then, check the values of the table. Please pay attention on setting the Isolation level for the second one as “Snapshot” at the same time when we already start the transaction using BEGIN TRAN.
-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate
FROM HumanResources.Shift
GO
You will notice that the values in the table are still original values. They have not been modified yet. Once again, go back to session 1 and commit the transaction.
-- Session 1
COMMIT
After that, go back to Session 2 and see the values of the table.
-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO
You will notice that the values are yet not changed and they are still the same old values which were there right in the beginning of the session.
Now, let us commit the transaction in the session 2. Once committed, run the same SELECT statement once more and see what the result is.
-- Session 2
COMMIT
SELECT ModifiedDate
FROM HumanResources.Shift
GO
You will notice that it now reflects the new updated value.
I hope that this example is clear enough as it would give you good idea how the Snapshot Isolation level works. There is much more to write about an extra level, READ_COMMITTED_SNAPSHOT, which we will be discussing in another post soon.
If you wish to use this transaction’s Isolation level in your production database, I would appreciate your comments about their performance on your servers. I have included here the complete script used in this example for your quick reference.
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE HumanResources.Shift
SET ModifiedDate = GETDATE()
GO
-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
COMMIT
-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 2
COMMIT
SELECT ModifiedDate
FROM HumanResources.Shift
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
30 Comments. Leave new
Hi ,
We have implemented snapshot isolation level in one of our production database in order to avoid blocking at the time of snapshot generation process in replication..I have impletemented on database level used below query
alter database dbname set read_committed_snapshot ON.
Without using allow_snapshot_isolation ON does it work ?
Is there any size limit on the database to have snapshot isolation.
what will happen if in –session 2 we will update again same record or another record
insted of
— Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate
FROM HumanResources.Shift
Hello Pinal,
I have doing snapshot replication on my db It is work fine. Now i am host one website which is used the subscriber db. Now the problem is, when subscription process is running and some one access the web at that time some operation like insert update is perform in db by web.
Now when subscription script is running that gives the error because of the identity issue (duplication record).
So what i am thinking whenever subscription process is running at that time i want to disable the use of that database.
Can u please tell me the step.
Or can we check the status of the subscription process is running or not using sql query or sp.
Thanks & Regards
Tushar
Is there any difference between updating data with snapshot isolation level and with read committed?
Thank you for reply.
Hi Pinal,
I have implemented Snapshot Isolation in Production database. However it’s not working. I have enabled Snapshot Isolation and Read Committed for my database. I have used the below query in my procedure. After implementing this still I am getting Deadlock errors. Could you please help me?
SET XACT_ABORT ON
BEGIN TRY
IF @UPDATEFOR = ‘UPDATE1’
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE Table1 set col1 = GETDATE() WHERE ID = 1
COMMIT TRANSACTION
END
IF @UPDATEFOR = ‘UPDATE2’
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE Table1 set col1 = GETDATE() WHERE ID = 2
COMMIT TRANSACTION
END
IF @UPDATEFOR = ‘UPDATE3’
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE Table1 set col1 = GETDATE() WHERE ID = 2
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
Select XACT_STATE()
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN
END
END CATCH
Thanks,
Murali