In what appears to be a deadlock scenario, why doesn’t the deadlock manager engage and determine a victim?
Here is an example of what I am talking about:
Query 1
Select statement on table "A" with a suitable where clause to define only the records desired.
Query 2
Multiple insert statements within a single transaction to several different tables including table "A."
The resulting scenario is that query 2 has crossed the magic threshold of 1250 locks and therefore escalates its lock to a table lock. Query 1, which is now blocked, also escalates to a table lock. The two queries then sit around waiting for each other to
finish, but they never do.
Here are my questions:
The lock manager will begin escalation if a single resource is using more than 1250 locks on table resources, or 765 locks on index resources. If the machine in question has 2GB of physical memory, how big is the memory pool for locks?
Even though this isn't the classic deadlock scenario, why doesn't the deadlock manager recognize this as a deadlock and choose a victim to help free up the resources?
Thanks.
> The lock manager will begin escalation if a single resource is using more
than 1250 locks on table resources, or 765 locks on index resources. If the
machine in question has 2GB of physical memory, how big is the memory pool
for locks?
>
If default not changed, it is 40% of memory allocated.
> Even though this isn't the classic deadlock scenario, why doesn't the
deadlock manager recognize this as a deadlock and choose a victim to help
free up the resources?
>
Since your situation is not a deadlock (it is a block), it will try to
finish the first (blocking) process. Deadlock does not have any hope of
finishing, and therefore sql server has the mechanism of getting itself out
of it.
Quentin
No comments:
Post a Comment