locked
SNAPSHOT ISOLATION LEVEL QUESTION RRS feed

  • Question

  • I thought that when this isolation level is set, any changes made to the data outside the transaction AFTER the transaction begins, are not visible to this transaction. However, if the transaction itself makes any updates, those shall be visible in a subsequent select in the transaction.

    So I had a little test run:


    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN

    WAITFOR DELAY '00:00:10'

    SELECT * FROM CallImp WHERE PName = 'Prime' AND CallDt = '2012-03-27'

    COMMIT

    Once, I kicked this tran off, I have the following transaction run:

    UPDATE CallImp
    SET PrimeYN = 'Y' WHERE PName = 'Prime' AND CallDt = '2012-03-27' (update successful)

    The original value of PrimeYN, when the first transaction began was 'N'.
    However, after the 10 sec delay, the select In the transaction shows the updated value i.e., 'Y'.

    Am I missing something here? Or did I entirely misunderstand what the snapshot isoaltion level does?

    Shouldn't the transaction be showing up with the value 'N' instead?
    Monday, April 9, 2012 4:20 PM

Answers

  • I believe you don't even need to modify data in order to obtain LSN here. Just read the data - put: select top 1 * from [AnyOtherTable] immediately after begin tran

    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by SQL Novice 01 Monday, April 9, 2012 6:55 PM
    Monday, April 9, 2012 6:53 PM

All replies

  • Hi,

    Please refer the link for details of SET TRANSACTION ISOLATION LEVEL,

    http://msdn.microsoft.com/en-us/library/ms173763.aspx

    and link that helps to change TRANSACTION ISOLATION LEVEL

    http://blog.sqlauthority.com/2010/05/21/sql-server-simple-example-of-snapshot-isolation-reduce%C2%A0the%C2%A0blocking%C2%A0transactions/


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, April 9, 2012 4:27 PM
  • Suresh,

      Firstly, thank you for your reply.

      However, here's my concern: When in the 1st session, I do not include a COMMIT, the SNAPSHOT Isolation works perfect. But if I include the Commit, it shows the updated value (from session 2). Why is this happening even though I have my select statement BEFORE the COMMIT?

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRAN

    WAITFOR DELAY '00:00:10'

    SELECT * FROM CallImp WHERE PName = 'Prime' AND CallDt = '2012-03-27'

    COMMIT


    Monday, April 9, 2012 4:53 PM
  • You are correct,

    you have to start 2nd transaction(select query) before you commit. The link has explained very clearly with an example.

    http://blog.sqlauthority.com/2010/05/21/sql-server-simple-example-of-snapshot-isolation-reduce%C2%A0the%C2%A0blocking%C2%A0transactions/

    -- 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 begin the transaction.
    -- Session 1
    COMMIT

    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.

    Hope this helps.


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, April 9, 2012 4:58 PM
  • That's quite interesting effect. That happens because LSN for snapshot transaction is not obtained at BEGIN TRAN stage. This comes in par with what we have in books online for begin transaction:

    Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. 

    You can check it by running the following statement (in the different session):

    select * from sys.dm_tran_active_snapshot_database_transactions

    There is no records returned until you execute any DML statements in the transaction. Try, for example, to read something (even from different table) immediately after BEGIN TRAN - it obtains LSN at this point and starts working as you expect it to. Check "How snapshot isolation and row versioning works" from http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx . The key point is to have LSN obtained - and you have transaction data consistency from that point



    Thank you!

    My blog: http://aboutsqlserver.com



    Monday, April 9, 2012 5:02 PM
  • Dimitri,

      Thank you for your input. Interesting catch there. It makes sense. So now, to test the above scenario, i.e., 

    Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. 

    I have modified, my tran as follows:

    --Session 1

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRAN

    INSERT INTO CallImp 

    SELECT 'Prime', getdate(),'Y' --So at this point, the Tran begins, as per the post above

    WAITFOR DELAY '00:00:10'

    SELECT * FROM CallImp WHERE PName = 'Prime' AND CallDt = '2012-03-27'

    COMMIT

    --Session 2:

    UPDATE CallImp
    SET PrimeYN = 'Y' WHERE PName = 'Prime' AND CallDt = '2012-03-27' (update successful)

    Now here's what's tricky, as the Insert is going to obtain an exclusive lock, the other UPDATE (session 2) will not execute until this lock is released, i.e, the end of the tran. So the whole purpose of the test is defeated. I'm playing around with locks for the first time so please let me know if there is another way around this. I meant to test this Isolation level. 



    Monday, April 9, 2012 6:46 PM
  • I believe you don't even need to modify data in order to obtain LSN here. Just read the data - put: select top 1 * from [AnyOtherTable] immediately after begin tran

    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by SQL Novice 01 Monday, April 9, 2012 6:55 PM
    Monday, April 9, 2012 6:53 PM
  • Dimitri,

      It WORKED!!!!

      Here's what I did (like you suggested)....

    --Session 1

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRAN

    SELECT TOP 1 * FROM CallImp

    WAITFOR DELAY '00:00:10'

    SELECT * FROM CallImp WHERE PName = 'Prime' AND CallDt = '2012-03-27'

    COMMIT

    The update went through and the ISOLATION level still showed the Data at the snapshot.

    So in other words, if the UPDATE (session 2) is run after kicking the Session1 off and BEFORE the SELECT TOP 1, the result again would be showing the UPDATED value.

    Seems like timing is everything.

       

    Monday, April 9, 2012 6:58 PM
  • Yes, but I would not worry much about it. If I use SNAPSHOT isolation level, I want data consistency. And consistency starts from the moment I access the data (any data) for the first time rather than from time of BEGIN TRAN statement. I don't see any real-life scenarios when that can make any difference and even if there are any it's easy to work-around by selecting something immediately after BEGIN TRAN statement. 

    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, April 9, 2012 7:04 PM