SQL 2008 r2 - Range deadlocks on READ COMMITTED

Question

Hi All, 

I have had a look in the forums but none of these quite explain what I am seeing. I am getting range S-S and Range I-N locks on indexes on numerous tables.

The scripts that are called do not have (HOLDLOCK)  and are not called by a SERIALIZABLE transaction but yet still hold the range locks. I know that there are instances where a lock will escalate but I can not see why this would occur in this instance

Any ideas?

Thanks in advance

Elliot

---

Developer

Answer 1

Are you deleting from a parent table, where the children are using CASCADE option on the foreign key constraints?

Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2009/03/13/conor-vs-isolation-level-upgrade-on-update-delete-cascading-ri.aspx

---

AMB

Some guidelines for posting questions...

Answer 2

Looks like they don't have to be running in SERIALIZABLE...

http://www.sqlservercentral.com/blogs/2cents/2010/09/22/range-locks-do-occur-in-serializable-transactions/

http://www.togsblom.com/2009/05/key-range-locks-during-cascade-updates.html

Answer 3

Hi Hunchback, 

Unfortunately not but thanks for the response

Cheers

---

DBA

Answer 4

Many thanks for your response Kev

---

DBA