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.

UPDATE (U) locks behavior in MERGE statement vs UPDATE statement

When analyzing deadlocks with MERGE and UPDATE statements,
I have noticed that UPDATE (U) Locks behave in a different way in

an UPDATE statement and in a MERGE statement.

Deadlock graph between MERGE and UPDATE statements on the same PK value

Let`s assume you want to update 2 rows in a table 

and the search for these rows using NC index,
the question is whether SQL Server will apply U locks one by one

and release each U lock after each row or it will apply U locks 

for all matched rows and only after the update will release them.

Let`s create a test table that will have 3 fields with CL and NC index:

CREATE TABLE dbo.Customers

(

CustomerID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

EmailAddress VARCHAR(50) NOT NULL,

FullName VARCHAR(50) NOT NULL

)

GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Customers_EmailAddress ON dbo.Customers(EmailAddress)

GO

INSERT INTO dbo.Customers (EmailAddress,FullName)
SELECT 'dan@gmail.com' AS EmailAddress,'Dan' AS FullName
UNION ALL
SELECT 'rita@gmail.com' AS EmailAddress,'Rita' AS FullName
UNION ALL
SELECT 'greg@gmail.com' AS EmailAddress,'Greg' AS FullName
UNION ALL
SELECT 'mike@gmail.com' AS EmailAddress,'Mike' AS FullName
UNION ALL
SELECT 'bob@gmail.com' AS EmailAddress,'Bob' AS FullName

GO

Customers Table

So we have the table with CL index on CustomerID and NC index on EmailAddress.

What we want to do is to update the FullName field of 2 rows that

will be filtered by EmailAddress column.

One way to do it is to use a simple UPDATE statement as below:

UPDATE c

SET c.FullName='N/A'

FROM dbo.Customers AS c

WHERE c.EmailAddress IN ('rita@gmail.com','mike@gmail.com')

GO

The second way we can use MERGE statement as below:

MERGE INTO dbo.Customers AS TGT USING ( VALUES( 'rita@gmail.com'),('mike@gmail.com')) 

AS SRC (EmailAddress)

ON SRC.EmailAddress= TGT.EmailAddress

WHEN MATCHED THEN 

UPDATE SET TGT.FullName='N/A'

WHEN NOT MATCHED THEN 

INSERT VALUES( 'rita@gmail.com','N/A'); 

Now let`s see what locks are acquired and when they released
if we run the UPDATE statement (only 'X' and 'U' locks shown in the screenshot).
SQL Server will first go to NC index to search by EmailAddress,
apply U locks for our 2 records and then go to CL index for the UPDATE.

In the screenshot above we have value 99 in Res0 column representing NC index

and value 98 representing CL index.

According to the locks sequence above,
We can see is that U lock applied to the first NC index key (Row #1, ID =4),
then U lock applied to CL index key(Row #2, ID =6),
then U lock converted to X lock on the CL key (Row #3, ID =8),
and finally, U lock released on NC index key (Row #4, ID =11).

Then the same order for the second row.

Now let`s see the locks sequence in case of the MERGE statement:

We can see that SQL Server first acquired U locks on the both NC keys
(2 keys, one after other), Rows #1,2,3,4 with Res0 =99.
Then applied U lock on CL index key(Row #5, ID =14)
Then converted the U lock to X lock of that CL index key(Row #6, ID =16)
and then applied U lock for the second key on CL index and converted it to an X lock
(Rows #7,8, IDs =20,22)

In summary, in the case of the UPDATE statement, SQL Server
acquired U locks on NC index one by one, first for 1 record
and then for the second record and released the U lock on NC index
after updating the CL index.
And in case of the MERGE statement, SQL server first acquired U locks
for both records in the NC index and did not release them until and of the transaction.

Key Range Locks (RangeS-U) in the default Isolation Level Read Committed

As we all know, SQL Server applies Key Range locks in the Serializable isolation level to prevent Phantom Reads,
But recently, in a deadlock graph, I have encountered Key Range (RangeS-U) locks in the default Isolation Level - Read Committed.

My question was, how come, why?
Well, the answer is: foreign key constraints with Cascading Deletes.

Once you have FK defined with “ON DELETE CASCADE” and you delete records from the parent table, SQL Server will use Key Range Locks at the child table to prevent insertion of new records during the Cascading Delete operation.

So now it makes sense why you can see the Key Range Locks (RangeS-U) in the default Isolation Level Read Committed:)