Database Locking Problem

Question

Pls. we've been getting A LOT of locks on a production database that's recently witnessed substantially increased traffic. We are using IdeaBlade for most of the data access.

I got the following trace using Sql Profiler:

deadlock victim="process84af28"
  resource-list
    keylock hobtid="72057594096451584" dbid="6" objectname="cpc_db.dbo.Prefix_ChildTableName" indexname="PK_Prefix_ChildTableName" id="lock45982ac0" mode="X" associatedObjectId="72057594096451584"
      owner-list
owner id="processb852e8" mode="X" 
   owner-list
   waiter-list
    waiter id="process84af28" mode="S" requestType="wait" 
    waiter id="processb855b8" mode="RangeS-U" requestType="wait" 
   waiter-list
  keylock
  keylock hobtid="72057594096451584" dbid="6" objectname="cpc_db.dbo.Prefix_ChildTableName" indexname="PK_Prefix_ChildTableName" id="lock513c3bc0" mode="RangeS-U" associatedObjectId="72057594096451584"
   owner-list
    owner id="processb855b8" mode="RangeS-U" 
   owner-list
   waiter-list
    waiter id="processb852e8" mode="RangeS-U" requestType="wait" 
   waiter-list
  keylock
 resource-list
deadlock

Ideas anyone?

I'm not a DBA but this trace seems to indicate that:

1. A process with an exclusive lock X on a row in the Child Table is attempting to acquire a Select-Update lock on the same resource (doesn't seem to make sense)

2. Another process with a Select-Update lock is still trying to acquire a Select-Update lock

Clarifications anyone?

How can we minimize or eliminate the deadlocks?

Answer 1

mode="RangeS-U"

Range locks? Stop using high transaction isolation levels. Stick to read commited. If you use CLR TransactionScope object, make them use the Read Commited isolation (by default they use Seralizable, yuck). Try turning on read committed snapshot isolation on the database. See Using Snapshot Isolation.

Answer 2

Couple of things to note first off:

1. You're using serializable transactions, the most restrictive form of pessimistic locking. Chances are, you don't need this (we know you are using serializable transactions as KEY locks only apply to this isolation level). As Remus mentions above, you should definitely look into other options here most likely.

2. It seems like the output above has been truncated a bit, you should have sections called process-list with information mapping the process information to spids and queries

From what you can tell in the output above:

processb852e8 owns an exclusive lock on index "cpc_db.dbo.Prefix_ChildTableName.PK_Prefix_ChildTableName"
    process84af28 is waiting for a shared KEY lock
    processb855b8 is also waiting for a Shared Range-Update KEY lock

processb855b8 owns Shared Range-Update lock on index "cpc_db.dbo.Prefix_ChildTableName.PK_Prefix_ChildTableName" (the same index)
    processb852e8 is waiting on a Shared Range-Update KEY lock

The exclusive lock is a write of some kind (i.e. update, delete, insert), the RangeS-U locks is likely an update, but no way to tell without seeing the information mapped.

Bart Duncan has a couple of great posts on deciphering the trace output if you have it all, see part 1 and part 2. You can also see an overview of concurrency and scripts in general here.

Answer 3

the culprit seems to be:-

owner id="processb855b8" mode="RangeS-U"

This would seem to have locked a set of rows. Its waiting for a row to be realeaed by process84af28, which is waiting for a row to be realeased by processb852e8 which is waiting for a row to be released by the first process.

SQLServer is resolving the deadlock by killing the process in the middle allowing the other two to complete.

You should look at your isolation levels. Best practice is to use the lowest available level of locking when "select"ing multiple rows. Only use a higher level on a "select"ed row if you are very likely to update the row in the current transaction.

And NEVER, not ever, leave a row locked while waiting for an external service or a user action.

Answer 4

I've seen this deadlock problem myself with a different product (not IdeaBlade). In my experience, this is not a database problem; it's probably a problem with the software communicating with the database.

My issues were with the configuration of the components communicating with the database.

The first time, COM+ defaults to SERIALIZABLE and had to be configured to default to READ COMMITTED.

The second time, a COM+ to .NET interop condition caused the database connection to default to SERIALIZABLE.

For us, a quick and dirty solution was to prefix the SQL commands with "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" to override SERIALIZABLE until the core problem could be fixed.