Database Reference
In-Depth Information
Avoiding Deadlocks
The methods for avoiding a deadlock scenario depend upon the nature of the deadlock. The following are some of the
techniques you can use to avoid a deadlock:
•
Access resources in the same physical order.
•
Decrease the number of resources accessed.
•
Minimize lock contention.
Accessing Resources in the Same Physical Order
One of the most commonly adopted techniques for avoiding a deadlock is to ensure that every transaction accesses
the resources in the same physical order. For instance, suppose that two transactions need to access two resources.
If each transaction accesses the resources in the same physical order, then the first transaction will successfully
acquire locks on the resources without being blocked by the second transaction. The second transaction will be
blocked by the first while trying to acquire a lock on the first resource. This will cause a typical blocking scenario
without leading to a circular blocking and a deadlock.
If the resources are not accessed in the same physical order (as demonstrated in the earlier deadlock analysis
example), this can cause a circular blocking between the two transactions.
•
Transaction 1:
•
Access Resource 1
•
Access Resource 2
•
Transaction 2:
•
Access Resource 2
•
Access Resource 1
In the current deadlock scenario, the following resources are involved in the deadlock:
Resource 1, hobtid=72057594046578688
: This is the index row within index
PK_ PurchaseOrderDetail_PurchaseOrderId_PurchaseOrderDetailId
on the
Purchasing.PurchaseOrderDetail
table.
•
Resource 2, hobtid=72057594046644224
: This is the row within clustered index
PK_PurchaseOrderHeader_PurchaseOrderId
on the
Purchasing.PurchaseOrderHeader
table.
•
Both sessions attempt to access the resource; unfortunately, the order in which they access the key is different.
It's common with some of the generated code produced by tools such as nHibernate and Entity Framework to see
objects being referenced in a different order in different queries. You'll have to work with your development team to
see that type of issue eliminated within the generated code.
