Thursday, May 24, 2012

dead lock with insert

Recently I encountered a strange problem of dead lock. Our code deadlocks when 2 process insert record into the same table (Both process insert into table A with different record). I was amazed that this could happen, since a deadlock always requires 2 resources and the table only have 1 index. A closer look and the table was selected before, got rangeS-S lock, and the insert tries to upgrade to rangeI-N, which dead locked with the first. But why this have not happened before? Well this will only happen for serialize isolation level. Our database is on read committed, and after I took the code out and run in multiple test programs they are all fine, this lead me to the last suspect, nservicebus (we use it to receive messages). NserviceBus default to serialized isolation level, so one line of code to set it to read committed fixed the dead lock.

No comments: