lock escalation

Question

lock escalation

Sam 1,476


Hi All, 

I am trying to understand the lock escalation behavior in Microsoft SQL Server 2017 (RTM-CU31).
As per my knowledge, lock escalation is done to minimize the lock memory when it is exceeding 5000 locks.

Here, in this example, I have create a table with 100 million records and I am trying to update 1 row (i.e. pk = 3).
Since, there is no index on pk column, I understand that SQL Server has to do a table scan. The point, I am not able to understand is, I tried to capture the trace during the update operation to see how locks are being acquired. I see that locks are being acquired and getting released on each row. if that's the case, why should SQL has to hold all these 5000 locks? Am I, missing anything here? If so, please help me understand the locking behavior. Another observation is, if I create a non-clustered idx on pk column, then there is no lock escalation as it is doing index seek and no table scan.

Here is my demo code.

use master 
go 
create database testdb 
go 

use testdb
go
CREATE TABLE dbo.Sample 
(
    pk integer IDENTITY NOT NULL, 
    i bigint NOT NULL, 
    lob nvarchar(max) NOT NULL
);

-- Add data
INSERT dbo.Sample 
    (i, lob)
SELECT
    i = ROW_NUMBER() OVER (ORDER BY @@SPID),
    lob = CONVERT(nvarchar(max), N'big string')
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2
go 

select count(*) from Sample; --( 106502400 rows affected)  --100 million rows
go 


select name, lock_escalation_desc from sys.tables where name = 'sample'
go
/*
Sample	TABLE
*/

select count(*) from Sample
where pk=3;
go 
--1 row

BEGIN TRAN
update sample set lob='test string' where pk=3;
--rollback;


NOTE: For some reason, I am not able to attach the .zip file. so, I had to rename .zip to .log. 
Once you are able to download the locktrace.log , rename it to locktrace.zip and unzip it. you should find the trace file inside.

Regards,
Sam 

Locktrace.log

Sam 1,476 Reputation points

2023-09-05T10:39:16.0766667+00:00

:

:

:

:

:

:

so on and finally I see a lock escalation at the bottom of the trace.

Answer accepted by question author

1 additional answer

Your answer

Sam 1,476 Reputation points

2023-09-05T10:39:16.0766667+00:00

:

:

:

:

:

:

so on and finally I see a lock escalation at the bottom of the trace.

Answer 1

Erland Sommarskog 129.6K MVP Volunteer Moderator

Not sure why you posted all those screen shots as an Answer. Did they actually answer anything? I converted them to a comment.

Your question was difficult to read since it was a single long line. If you want to get help, you may want to make an effort to make your posts readable.

Anyway, I ran this:

BEGIN TRAN
update Sample set lob='test string' where pk=3

SELECT resource_type, request_mode, resource_associated_entity_id AS id,
       object_name(try_convert(int, resource_associated_entity_id)), 
       COUNT(*) AS [Count]
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@spid
GROUP  BY resource_type, request_mode, resource_associated_entity_id,
          object_name(try_convert(int, resource_associated_entity_id))

And this is what I see:

User's image

I don't lock escalation is going on here, but I don't sure. There are 24 locks on object level. The reason that there is more than one is that with a table of this size, there will be parallelism and lock partitions. Lock partitions is a concept that is difficult to grasp, and to be honest, it may be above your own level. (Hint: I have difficulties to grasp the concept myself. And they call me an expert.) Also, because the table is that big, SQL Server may have taken these locks already from the start.

There is also one intent lock on page and one RID lock, that is a row lock, which is a little puzzling. But that must be for the row begin updated.

I would recommend that you redo the exercise for a table of a more moderate size. One million rows should do.

Sam 1,476 Reputation points

2023-09-06T20:32:20.13+00:00

My Apologies for the readability aspects of this post. This is what I was trying to understand.

As per my knowledge, lock escalation is done to minimize the lock memory when it is exceeding 5000 locks.

Here, in this example, I have create a table with 100 million records and I am trying to update 1 row (i.e. pk = 3).

Since, there is no index on pk column, I understand that SQL Server has to do a table scan. The point, I am not able to understand is, I tried to capture the trace during the update operation to see how locks are being acquired. I see that locks are being acquired and getting released on each row. if that's the case, why should SQL has to hold all these 5000 locks? Am I, missing anything here? If so, please help me understand the locking behavior. Another observation is, if I create a non-clustered idx on pk column, then there is no lock escalation as it is doing index seek and no table scan.
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2023-09-06T21:13:23.6733333+00:00
I changed the example to:

INSERT dbo.Sample (i, lob) SELECT TOP (100000) i = ROW_NUMBER() OVER (ORDER BY @@SPID), lob = CONVERT(nvarchar(max), N'big string') FROM sys.all_columns AS AC1 CROSS JOIN sys.all_columns AS AC2 BEGIN TRAN update Sample set lob='test string' where pk=3 SELECT resource_type, request_mode, resource_associated_entity_id AS id, object_name(try_convert(int, resource_associated_entity_id)), COUNT(*) AS [Count] FROM sys.dm_tran_locks WHERE request_session_id = @@spid GROUP BY resource_type, request_mode, resource_associated_entity_id, object_name(try_convert(int, resource_associated_entity_id)) go ROLLBACK TRANSACTION

Now I see this:

There is a single row lock, so there is no lock escalation. The locks on table and page level are intent locks, also known as "I am here locks". The block any attempt to take a lock on table level.

The reason that there is no lock escalation is that while scanning the table, SQL Server takes an Update lock every row, but when it finds that the row does no qualify, it release that lock.

Change the UPDATE statement to:

update Sample set lob='test string' WHERE pk <= 4999

Now there are 5251 RID locks. (5251? No idea.)

Change to 5912 and there are 6209 locks. Then step up to 5913, and the scene changes:

I can't say that I understand exactly what is going on here, but apparently there is some lock escalation going on, and there are slightly fewer RID locks. Actually, the number now matches the number of rows update.

It's easier to understand if you add a clustered index on the table. In this I find a threshold point somewhere between 6150 and 6200 rows. Above this level, all I have is 24 X locks on table level.

As for why the threshold point is higher than advertised, I don't know. But maybe the lock escalation is asynchronous with regards to the rest of execution, so there is a timing issue.
Sam 1,476 Reputation points

2023-09-07T16:57:24.2566667+00:00

Thanks for taking time and providing the example. Mostly, likely it is kind of timing issue or even after 5000 locks , it checking for next 2500 rows. I am not sure but read or heard someone telling that. or else, the optimize is smart enough and jsut bailed out thinking no more rows to process. Anyways, Thanks a lot for the help Sir.
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2023-09-07T20:55:52.1833333+00:00

Now there are 5251 RID locks. (5251? No idea.)

I figured that out. The new string you are writing is longer, so there is not always space on the page for the longer value. When this happens in a heap, there is no page split. Instead the new value is written on a new page, and there is a forward pointer. So when updating 4999 rows, there are also 252 forward pointers.

I was never able to understand what your question was really about, but searching for an answer to why the X locks remain on RID level after lock escalation, I found Paul White's article on https://sqlperformance.com/2022/09/sql-performance/lock-escalation-threshold-part-1 And judging from the similarity of the repro script, I suspect you've been reading that too.

Paul's article answers why lock escalation does not happen after 5000 locks, but why it happens after 6250 locks.

In the example you posted, you had a crazy amount of rows, which triggers parallelism, and as I understood part II in Paul's series, lock escalation is counted per thread when there is parallelism, which raises the limit quite higher.

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

lock escalation

1 additional answer

Your answer