locked
Transaction Isolation Level RRS feed

  • Question

  • Hello,

    I have a question about transaction isolation level.

    In the 1st Sql Server Management Studio query window, I have the following:
         while @@TRANCOUNT > 0 rollback
         set transaction isolation level serializable
         begin tran
             select * from LockTest where id = 3

             ...

    In the 2nd Sql Server Management Studio query window, I have the following:

        insert into LockTest(name) values (‘Z1zzxx2zcc’);

     

    I execute the 1st query window up till the select and then I switch over to the 2nd query window and execute it.

    The 2nd query window's insert statement blocks. That surprises me because what I am inserting does not affect the result set returned by the 1st query window.

    My understanding is that I SHOULD be able to do inserts, updates and deletes in the 2nd query window, AS LONG AS the insert/update/delete does not affect the result set of the 1st query window. After all that is what transaction isolation level serializable is all about. 

    LockTest has 2 columns and identity column id and a name varchar(5)

    Any help will be appreciated.

    Thanks.

    Saturday, January 28, 2012 8:58 PM

Answers

  • My understanding is that I SHOULD be able to do inserts, updates and deletes in the 2nd query window, AS LONG AS the insert/update/delete does not affect the result set of the 1st query window. After all that is what transaction isolation level serializable is all about.

    The behavior will depend on both indexes and data.  Assuming you have a primary key on the id column and have a row with id = 3, the first query will hold a shared lock on key 3 and the insert will succeed. OTOH, if you have no index on the id column, a shared table lock will be acquired and the insert will be blocked.  If you have a non-unique index on id and value 3 is the highest row in the table, the insert will be blocked by a shared range lock if 3 is the highest value in the table.

     

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by RahulMisra2000 Sunday, January 29, 2012 12:04 AM
    Saturday, January 28, 2012 9:32 PM

All replies

  • Try reading about snapshoot isolation, that is default for oracle afaik.


    ow, sorry, you mean row locks instead of table locks?  select with(rowlock)...

    serializable transaction ensures, that any repetative selects inside it will return identical datasets, so insert is blocked.

    in snapshoot isolation inserts are not blocked, but it has other disadvantages

    • Edited by Kazzman Saturday, January 28, 2012 9:08 PM
    Saturday, January 28, 2012 9:04 PM
  • My understanding is that I SHOULD be able to do inserts, updates and deletes in the 2nd query window, AS LONG AS the insert/update/delete does not affect the result set of the 1st query window. After all that is what transaction isolation level serializable is all about.

    The behavior will depend on both indexes and data.  Assuming you have a primary key on the id column and have a row with id = 3, the first query will hold a shared lock on key 3 and the insert will succeed. OTOH, if you have no index on the id column, a shared table lock will be acquired and the insert will be blocked.  If you have a non-unique index on id and value 3 is the highest row in the table, the insert will be blocked by a shared range lock if 3 is the highest value in the table.

     

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by RahulMisra2000 Sunday, January 29, 2012 12:04 AM
    Saturday, January 28, 2012 9:32 PM
  • Dan,

    really appreciate your taking the time on a weekend to assist me out.

    Could have never figured this out without your kind help.

    Thanks again !

    Sunday, January 29, 2012 12:04 AM
  • I'm glad I was able to help out.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, January 29, 2012 3:00 AM