SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

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

SQL SERVER - Simple Example of Snapshot Isolation - Reduce the Blocking Transactions modifieddate1

Solarwinds

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

SQL SERVER - Simple Example of Snapshot Isolation - Reduce the Blocking Transactions modifieddate1

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

SQL SERVER - Simple Example of Snapshot Isolation - Reduce the Blocking Transactions modifieddate1

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

SQL SERVER - Simple Example of Snapshot Isolation - Reduce the Blocking Transactions modifieddate2

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)

Solarwinds

,
Previous Post
SQLAuthority News – Updated Favorite Scripts and Best Articles Page
Next Post
SQLAuthority News – Downloads Available for Microsoft SQL Server Compact 3.5

Related Posts

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 ?

    Reply
  • Is there any size limit on the database to have snapshot isolation.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Artashes Khachatryan
    April 30, 2015 6:54 pm

    Is there any difference between updating data with snapshot isolation level and with read committed?

    Thank you for reply.

    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

    Reply

Leave a Reply

Menu