Pages

Wednesday, October 2, 2019

Lock escalation: Multiple batches inside a transaction

As you know,
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).

-  The memory taken by lock resources is more than 40%

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
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
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.

No comments:

Post a Comment