Trying to understand lock escalation behavior
-
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 UPDATE 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;Regards,
Sam
-
:
:
:
:
:
:
Finally, I see a lock escalation at the end of the trace.
Regards,
Sam
-
I believe you'll find the following article quite interesting. To summarize, Paul White proves that the 5000 row thing is a bit of a myth that, according to him, no one has ever successfully demonstrated. He has demonstrations that show that it doesn't happen at 5000 and that there may be other factors involved. He also provides a link to a very serious but long and someone convoluted white paper on the subject.
https://sqlperformance.com/2022/09/sql-performance/lock-escalation-threshold-part-1
Also, it appears that you're updating a LOB. Those are a bit of a different animal. I don't know if the additional documentation that Paul provides a link to covers that or not.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Sir. will go through Paul White's blogs.
- vsamantha35 wrote:
Thank you Sir. will go through Paul White's blogs.
You bet, Sam... let us know how it turns out, please.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply