The Lock Escalation can happen from Keys/Rows level to the Table/Partition level
or from Pages level to the Table/Partition level, but not from rows to pages.
SQL Server decides to do the escalation when any of the following conditions met:
- The number of locks (including intent locks) held by a single statement
on an index or a heap within a statement exceeds the threshold (Approximately >5000 locks).
on an index or a heap within a statement exceeds the threshold (Approximately >5000 locks).
- The memory taken by lock resources is more than 40%
Now consider a situation when you have multiple INSERTs or UPDATEs statements
Now consider a situation when you have multiple INSERTs or UPDATEs statements
of a table inside a transaction.
So my question was:
When SQL Server decides to do the lock escalation is it takes into account/counts
all locks acquired for the specific table of all statements in the transaction
Or it counts only locks for a single statement?
For example, if I want to update/insert high amount of rows in a table,
if I do it in one statement it will escalate the locks to table level,
But if I do it in batches/chunks under a transaction, will it count each batch/statement
Or it counts only locks for a single statement?
For example, if I want to update/insert high amount of rows in a table,
if I do it in one statement it will escalate the locks to table level,
But if I do it in batches/chunks under a transaction, will it count each batch/statement
separately or it will count all locks acquired by all batches because we do it
in the transaction and the X locks are held until the end of the transaction
in read committed isolation mode.
Well, the answer is:
The escalation will occur if the threshold is exceeded for a single statement
Well, the answer is:
The escalation will occur if the threshold is exceeded for a single statement
on a single resource. Meaning if you have multiple UPDATE/INSERT
statements for a specific table inside a transaction,
SQL server will treat each statement individually/separately
and will not count X locks held by previously executed statements in this transaction.
SQL server will treat each statement individually/separately
and will not count X locks held by previously executed statements in this transaction.
No comments:
Post a Comment