SQL Server 2005: Key-Range Locks in Read Committed Transaction Isolation Level?

You are using READ COMMITED, as you expect.

You are using READ COMMITED, as you expect. Deadlocks like this can occur if an UPDATE acquires an eXclusive key lock on the clustered index and modifies a row, and that lock blocks a SELECT’s bookmark lookup on the clustered index. Locks of this nature can often be eliminated by creating a covering non-clustered index.

Adding the NOLOCK hint to the offending SELECT statement is one solution provided you can tolerate un-commited reads. Another option available to you is to set READ_COMMITED_SNAPSHOT ON for the database. This changes how the way SELECT statements read commited data; instead of taking shared locks, they read prior versions (a snapshot) of any data changed by transactions that began at the start of the SELECT statement.

This doesn't come entirely for free though; the cost is increased activity in tempDB. There is also the potential for issues with triggers in in READ COMMITED SNAPSHOT mode.

I agree those are all possible explanations and good suggestions for deadlocks. But I still don't understand why the RangeX-X lock is occurring if the isolation level is read committed. I'd like to understand why we have an exclusive lock on a range of keys instead of a single key.

– Amy T Jan 16 '09 at 9:41.

According to SQL Docs, somehow, your transaction (or some other one) was running at level serializable.

I believe this answer is incorrect. – Mitch Wheat Jan 15 '09 at 14:50.

The RangeX-X lock on PK_Exp_Experience_PriorFirm was being taken as part of a cascading delete. SQL Server automatically upgrades the isolation level to serializable for certain operations, such as cascading deletes. This is described in more detail here: Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.