Sql-server – Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included

indexsql-server-2005

I am currently experiencing the error (logged via traceID 1222 ):

keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S AssociatedObjectID = 8205698989 
waiterID = process 85b2566 mode=S requestType=wait
waiter-list
keylock hobtid= 820562208 objectname=mydb.dbo.Orders indexname = pk_Orders_OrderID id=lock4012341 mode=X AssociatedObjectID = 820562208 
resource-list
Update Orders set OrderType = 1 where OrderID = 52000 and DeliveryID = 1236
inputbuf
Update [Orders] set [OrderType] = @1 where [OrderID] = @2 and [DeliveryID] = @3

Table: 

Orders

Columns: 

OrderID         int (pk)
OrderNumber     Varchar(50)
OrderDate       datetime
OrderType       int
DeliveryID  int
DateDelivered   datetime
CustomerID  int

Index:

There is a Clustered Index on the PK:

OrderID         int

name pk_Orders_OrderID

The one causing the issue: (I got it by getting the object from AssociatedObjectID )

_dta_Index_Orders_5_120345_K1_K2_7_12: (nonclustered)

Index Key Columns:

OrderNumber     Varchar(50)
OrderID         int (pk)

Included Columns:

OrderDate       datetime
OrderType       int

Now option "use page locks when accessing the index" caused a lot of pagelocks, so it was unchecked.
The "Use row locks when accessing the index" is still on.

But I am getting the above error as mentioned, and the funny thing is that when customers sometimes run a lengthy report, the keylock occurs
even though the specific report is for Customer A, and the update happening to Orders is for Customers B – thus the CustomerIDs don't match and therefore the should not be no lock, or am I mistaken?

The keylock word in question – is this directly related to the "use row locks when accessing the index"?

Do row locks cause keylocks?

Would it be that the deadlock message:
Transaction (Process ID xy) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
was caused when the _dta_Index_Orders_5_120345_K1_K2_7_12 index was scanning, and at that stage it happend to scan on orderID 52000 (even though OrderID 52000 is not in the resultset)?

I am stunned and have no clue now.
Thank you

Best Answer

Now option "use page locks when accessing the index" caused a lot of pagelocks, so it was unchecked

When you play with fire, you get burned. Have you considered what alternative will be used? With page locks disabled, there are two possibilities: use row lock granularity or use rowset granularity. Since there must be at least one row per page, using row granularity will turn into locking at least as many individual locks as page lock, but much more likely more, many many many more locks. So it would turn a bad situation into a far worse situation. Using rowset granularity will 'heal' the lock manager health as only one lock will be acquired, at the cost of throwing all concurrency down the drain. Do no mess with settings you don't understand. BTW, your question 'do row locks cause key locks' clearly expose the level of your understanding of locking as inadequate to be trusted in changing such important settings, no offense intended.

The important question is why was the query taking page locks? Page locks are the hallmark tellsign of scans: the engine chooses a high granularity because it knows it has a lot of rows to look at. Your post is missing critical information needed to understand the deadlock, please post the entire original deadlock XML, along with the complete schema definition.

As for the question: the 'rowlocks' resource is 'keys' in B-Trees and 'rowids' in heaps.

Related Question