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

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 (http://blog.sqlauthority.com)

33 thoughts on “SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

  1. Hi,

    Once we update the record we need to commit, i.e in Session 1. Then the date was not refreshed. But why it was given in session 2 date was refreshed? Can anyone please explain?

    Like

    • The thing goes like this:

      Session 1 begins transaction and updates the value but does not commit transaction. This means that old values are still in effect. Snapshot isolation causes that the old values can still be read.

      Session 2 begins transaction and reads the old values. In this phase database engine creates a copy of the read row to TEMPDB. All the reads session 2 does inside the transaction is read from TEMPDB.

      Session 1 commits the transaction thus storing the change value to the table. Take notice that the change is done to the table. Session 2 copy in TEMPDB is left untouched.

      Session 2 reads row again. Read is done from TEMPDB which still contains the original value. This is because session 2 hasn’t commited its transaction yet.

      Session 2 commits transaction. This destroys the row in TEMPDB. After this original value can’t be read anymore.

      Session 2 reads the row yet again. Now it gets the new value from the table since after the commit it has no row in TEMPDB.

      Now I would like to add to Pinal’s excellent example the following. In the final read in session 2, there’s no BEGIN TRANSACTION. This means that all the reads are done to the table and no copying is done to the TEMPDB. This also means that there will be read locks to the tables! If you don’t remember start transaction (with correct isolation level) you don’t get any benefits from SNAPSHOT.

      Here’s my blog entry about this if Pinal allows: http://weicco.spaces.live.com/blog/cns!D23A6DEABD865C68!521.entry

      Like

      • Marko,

        I always allow :) GREAT GREAT comment and wonderful write up.

        Kind Regards,
        Pinal

        PS. only thing I do not allow is bad words, personal emails, personal phone numbers and server addresses – as all of those can be mis used.

        Like

  2. Hi, Pinal

    I can understand what you tried here with example.

    But can you tell me in which real case we can use this kind of isolation level in production database?

    I am working & structured vertical database scheme.So in this case how can i get bebifit with snapshot or any other ?

    Dhaval.

    Like

    • “But can you tell me in which real case we can use this kind of isolation level in production database?”

      I’ve been working for a while now in a production control project. The system is responsible of tracking every parcel that is running around in our factory. When ever parcel is transferred from a location to another it’s location must be updated to the DB. This, of course, means heck load of updates to some tables.

      So to prevent locking issues with reads/writes we enabled snapshot isolation level and we don’t have to worry about lock storms anymore :)

      Like

  3. Very Good article. Is it possible to select certain columns from a .CSV file to SQL table using SQL Select statement? If so how? Please helt.

    Like

  4. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

  5. Can this technique reduce deadlocks?

    I have a database that’s been added to for the past 10-15 years and at this point contains tables with a massive amounts of indexes and various apps hitting the db go at the data from a slightly different angle, resulting in a lot of deadlocks.

    Like

  6. Frank,
    Here are the basic scenarios in which you may benefit from using SNAPSHOT ISOLATION –
    1. You have OLTP-type application with big number of small concurrent transactions. If someone is updating (locking) the table that’s being frequently used by those transactions you get Blocked Processes queue growing immediately.

    2. You have application where few tables present 80-90% of all data and being hit periodically (by SELECTs and UPDATEs). every UPDATE/INSERT on such table may take seconds which will cause others to stay in waiting list.

    These are examples from my own experience.

    Good luck!

    Like

  7. Hi Pinal,
    I have an application (from vendor) causing deadlocks to my database frequently. If I turn on Snapshot Isolation level in the database, will it reduce the occurence of deadlock ?

    Like

  8. I have two Sp,both insert Sp record insert record into same table.One sp call regular time interval and one sp call manually by user activation.when both sp call together at time deadlock is occurs,how can i solver this problem?

    Like

  9. Pingback: SQL SERVER – Importance of ANSI ISOLATION Levels in SQL Server Database – Quiz – Puzzle – 1 of 31 « SQL Server Journey with SQLAuthority

  10. Pingback: SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31 « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Locking, Blocking and Deadlock – Quiz – Puzzle – 9 of 31 « SQL Server Journey with SQL Authority

  12. From the article in order to take advance of isolation snapshot you have to set isolation snapshot level in your application source code queries and also all the queries in the application must begin with begin trans and end with commit

    Like

  13. What happens to the size of tempdb when the snapshot isolation is used? Does the number or update transactions cause tempdb to grow or does the number of queries running using snapshot isolation cause tempdb to grow?

    Like

  14. Hello,

    Great post. I hope this comment/question is not ‘too late’ …

    Every transaction that needs to use snapshot isolation level has to configure it explicitly before it begins with ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’, right? Setting ‘ALLOW_SNAPSHOT_ISOLATION’ on database level is not enough, but required of course?
    My point is if you want to use a snapshot isolation across your existing application you can not simply turn it on … code changes required for all tranactions.

    In SQL Server 2008, if I understand correctly, setting a ‘READ_COMMITTED_SNAPSHOT’ (under default READ_COMMITTED isolation) on db level is enough? That way no code changes are required.
    But READ_COMMITTED_SNAPSHOT allows multi-versioning for single statement(s) only (while SNAPSHOT isolation level does it for entire transaction).

    I’d like to hear your opinion … Thank you.

    Like

  15. Hi Pinal,

    Thank you for this excellent post!

    I tried the queries however didn’t get the expected result…

    Step 1:
    ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON
    GO

    Step 2:
    SELECT ModifiedDate
    FROM HumanResources.Shift
    GO

    Step 3:
    — Session 1
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN
    UPDATE HumanResources.Shift
    SET ModifiedDate = GETDATE()
    GO

    Step 4:
    — Session 2
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN
    SELECT ModifiedDate
    FROM HumanResources.Shift
    GO

    and I see the updated records!! Why? Dreadfully, I tried SET READ_COMMITTED_SNAPSHOT ON/OFF. It appears to be following Lost Update. I’m using SQLServer2008.

    Like

  16. Hi Pinal Dave

    here we get strange issue with ISOLATION LEVEL SNAPSHOT in one of our sql stored procedure

    we have used the ISOLATION LEVEL SNAPSHOT in stored procedure : test1.sql

    and from this sp we called the sql function name “testfun.sql” from this sp ,
    and as per my knowledge , we can not use any isolation level in sql function.

    and the our sql stored procedure “test1.sql” just have select query only, but it give me the below error

    and sql function “testfun.sql” have one temp declared table and we just insert data in it and return that table from the function

    “com.microsoft.sqlserver.jdbc.SQLServerException:
    Snapshot isolation transaction failed in database ‘test’ because
    the object accessed by the statement has been modified by a DDL statement
    in another concurrent transaction since the start of this transaction.
    It is disallowed because the metadata is not versioned.
    A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.

    now what is the root caue of the issue, can you help me to resolve my issue?

    Like

  17. Attempting to Understand:

    –UNIT PRICE IS 18 PRIOR TO TRANSACTION
    –THOUGHT ROW COPIED TO tempdb UNIT PRICE WOULD BE 18
    –WHY DOES IT COPY 19 AND NOT 18 PRIOR TO TRANSACTION START

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    BEGIN TRAN;
    UPDATE production.products SET
    unitprice +=1 FROM production.products WHERE productid=1;

    SELECT unitprice FROM production.products WHERE productid=1;
    COMMIT TRAN;

    –UNIT PRICE RESULTS RETURN 19

    Like

  18. Hello Pinal. Great article.

    I have a question about followiing scenario:

    I have excecuted the following command for session 1:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN
    SELECT entity_updateTime
    FROM user_Table
    GO

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN
    UPDATE User_Table
    SET entity_UpdateTime=GETDATE()
    GO

    Now let us say if i tried to execute the same command for session 2. It seems table is locked. I can not see output. Getting error message “Executing query…..”

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN
    SELECT entity_updateTime
    FROM user_Table
    GO

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN
    UPDATE User_Table
    SET entity_UpdateTime=GETDATE()
    GO

    Any help is greatly appreciated !

    Best Wishes,
    Brij

    Like

  19. 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 ?

    Like

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

  21. 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

    Like

  22. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s