Isolation Levels doubt

Question

Isolation Levels doubt

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Jared SSC Guru Points: 61793 More actions · Answer 3

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

itskanchanhere SSC Eights! Points: 802 More actions · Answer 4

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