Hot answers tagged lock-escalation
22
DB locks can exist on rows, pages or whole tables or indexes. When a transaction is in progress, the locks held by the transaction take up resources. Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover ...
11
It's a method for reducing system overhead, by converting many fine grained locks to fewer coarse grained ones. More detailed information can be found here and here.
For example, if you have many (usually hundreds or more) locks on specific rows in a table, once you exceed your maximum allowed number of locks, these might be exchanged for a lock on the ...
7
BOL has answer -- It is not ON by default as it may increases the potential for deadlock.
The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the ...
4
Deleted my previous answer when I realised the trace shows parallelism.
With a big warning to test this very very thoroughly, you might alleviate the deadlocks by restricting MAXDOP and adding a UPDLOCK hint on Table1. I would also (as per @Aaron's suggestion) try EXISTS.
DELETE
d
FROM
Table1 d WITH (UPDLOCK)
INNER JOIN
#deleteEntities de
ON ...
2
SQL Server can still choose to escalate the row lock to a table lock, even if WITH(ROWLOCK) is specified. This can depend on a number of things including: number of rows deleted, number of total locks acquired by transaction, and total memory pressure of all locks acquired.
Sunil Agarwal has written a great article describing this process, which can be ...
Only top voted, non community-wiki answers of a minimum length are eligible
