Post reply

Isolation Levels doubt

  • April 19, 2012 at 5:14 pm

    #1476357

    GilaMonster (4/19/2012)

    SQLKnowItAll (4/19/2012)

    I think it is assumed that the shared lock is only placed on committed data, as you cannot lock data that is not committed. EDIT: I think...

    Sure you can. All updates, deletes and inserts lock uncommitted data. If they didn't, then any select could read that uncommitted data regardless of isolation level, which would be bad. Now it won't be a shared lock, it'll be X because for the data to be uncommitted, it must have been modified and hence it'll be X-locked til the end of the transaction.

    Read uncommitted does not take shared locks. That's how it can read uncommitted rows that inserts, updates and deletes have locked. Because it does not take locks, it can't be blocked waiting for a lock to be granted. It has no effect on data modifications which will still take their X locks as required.

    Thanks Gail! I'll edit my posts to not confuse anyone. I couldn't wrap my head around it and I couldn't find too much info. I figured you'd chime in with some real knowledge. Something that may help all of us with this... Is there a book out there that would help with these types of things? I think you had mentioned Kalen Delaney before, but there are several books authored by her (I assume either Storage Engine or System Internals would be the correct one).

    Jared
    CE - Microsoft

  • April 19, 2012 at 5:20 pm

    #1476360

    Chapter 6 of this has a 'short' summary of locks and locking modes

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Then chapter 10 of SQL 2008 internals for the detail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 19, 2012 at 5:28 pm

    #1476363

    GilaMonster (4/19/2012)

    Chapter 6 of this has a 'short' summary of locks and locking modes

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Then chapter 10 of SQL 2008 internals for the detail.

    Thank you!

    Jared
    CE - Microsoft

  • April 20, 2012 at 12:54 am

    #1476438

    Thank you all for your reponses. I have been able to get a better picture but lets just hope I dont confuse the next time I read some other article on isolation levels

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply