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
OrderID int (pk) OrderNumber Varchar(50) OrderDate datetime OrderType int DeliveryID int DateDelivered datetime CustomerID int
There is a Clustered Index on the PK:
The one causing the issue: (I got it by getting the object from AssociatedObjectID )
Index Key Columns:
OrderNumber Varchar(50) OrderID int (pk)
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