Locks and isolation levels

[expired user #8564]'s profile image [expired user #8564] posted 9 years ago in General Permalink
Hi,

I have some troubles understanding which locks are applied in which isolation levels in MS SQL.

I have found on MSDN (http://technet.microsoft.com/en-us/library/ms189122(v=SQL.105).aspx) table with information which phenomena (dirty read, nonrepeatable read and phantoms) happen for which isolation level (read uncommited, read commited, repeatable read, snapshot, serializable). I also know there are different kinds of lock - I'm most interested in shared locks, exclusive locks and range locks.

But I was able to find only partial information about which locks are applied for which isolation level.

* Shared locks: read uncommited (NO), read commited (YES by default, but you can use READ_COMMITTED_SNAPSHOT as ON for row versioning instead of shared locks), repeatable read (YES), snapshot (NO, only if you roll back - YES), serializable (I don't know).
* Exclusive locks: I don't know
* Range locks: I only know those happen for serializable level, I don't know about other levels.

Could you help me to understand those locks for isolation levels that are not mentioned above, please?

Regards :-)

Please login to leave a reply, or register at first.