sql server – Database Locking Problem

sql server – Database Locking Problem

Couple of things to note first off:

  1. Youre using serializable transactions, the most restrictive form of pessimistic locking. Chances are, you dont 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.

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.

sql server – Database Locking Problem

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 selecting multiple rows.
Only use a higher level on a selected 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.

Leave a Reply

Your email address will not be published.