November 15, 2017 at 8:43 am
Hi
We are experiencing lock escalation when inserting data in SQL2012 CU 10 SP3. Data is staged in a table variable before load and then inserted using an outer join referencing the primary key to prevent duplicates from being inserted. The application will not load more than 4k records into the table variable for each operation. The table is partitioned, all indexes are partition aligned, compressed, index padded and with row and page locks enabled. There are two indexes on the table the primary key (clustered) and a non clustered index.
I ran the query on a development environment checked out sys.dm_trans_locks to cofnirm it was escalating to a table level lock results below.
resource_type resource_associated_entity_id resource_subtype request_mode (No column name)
DATABASE 0 S 1
HOBT 72064096059588608 X 1
HOBT 72064116058685440 IX 1
KEY 72064116058685440 X 4000
OBJECT 2042486645 IX 1
PAGE 72064116058685440 IX 19
When i ran the query on the development environment I created an extended event (lock Acquired) to understand in more detail.
Secondary Index (HobtID 72064096059588608)
Mode Lock Count
IX 2534
RI_NL 2479
X 2480
SCH_S 6
Primary Key (Hobt_id 72064116058685440)
Mode Lock Count
IX 4039
RI_NL 4002
X 2087
SCH_S 639
I was under the impression that lock escalation shouldn't kick in when there are less than 5K row or page level locks on an object? Can someone confirm?
Cheers
Rob
November 15, 2017 at 3:36 pm
robnsilver - Wednesday, November 15, 2017 8:43 AMHiWe are experiencing lock escalation when inserting data in SQL2012 CU 10 SP3. Data is staged in a table variable before load and then inserted using an outer join referencing the primary key to prevent duplicates from being inserted. The application will not load more than 4k records into the table variable for each operation. The table is partitioned, all indexes are partition aligned, compressed, index padded and with row and page locks enabled. There are two indexes on the table the primary key (clustered) and a non clustered index.
I ran the query on a development environment checked out sys.dm_trans_locks to cofnirm it was escalating to a table level lock results below.
resource_type resource_associated_entity_id resource_subtype request_mode (No column name)
DATABASE 0 S 1
HOBT 72064096059588608 X 1
HOBT 72064116058685440 IX 1
KEY 72064116058685440 X 4000
OBJECT 2042486645 IX 1
PAGE 72064116058685440 IX 19When i ran the query on the development environment I created an extended event (lock Acquired) to understand in more detail.
Secondary Index (HobtID 72064096059588608)
Mode Lock Count
IX 2534
RI_NL 2479
X 2480
SCH_S 6Primary Key (Hobt_id 72064116058685440)
Mode Lock Count
IX 4039
RI_NL 4002
X 2087
SCH_S 639I was under the impression that lock escalation shouldn't kick in when there are less than 5K row or page level locks on an object? Can someone confirm?
Cheers
Rob
That's not my understanding although I've seen it written that "absolutely only when hitting 5000".
If the lock manager has hit its memory threshold, it can escalate locks even if the 5000 mark hasn't be hit yet. I've read about it a few times and I know I ran through a demo showing it before but just can't find any of the articles, links about it right now.
Sue
November 16, 2017 at 1:05 am
HI
The lock escalation extended event gives the reason for the lock escalation. In this case it isn't due to memory pressure. I managed to reproduce it last night on my local machine. See below for script finding so far.
Any help appreciated, not sure if this is by design or ....
/*
Script will enable read committed snapshot isolationlevel on for the database and then go on to create a numbers table
(dbo.numbers) and a table for the insert operation ( dbo.insert_into). We will populate this table to create duplicates records
on field1 and field3, this seems to be the cause of the lock escalation. When the number of duplicates (configured with @idx_max) is < 40 I don't usually see
lock escalation. When it is over 75 it usually escalates.
The insert statement will left loop join (rowlock) against the primary key to remove duplicates. The dbo.insert_into table
has a primary key consisting of three field (field1,field2,field3) it also has a non clustered index on field1 and field3. Hints applied to
this table have only been put in to remove differences between the execution plans when run locally. This shuld produce a query plan
with a nested loop join, eager spool and per row update on the two indexes.
NB: The rollback at the end of this script should be commented if you want to check out locks held before it commits.
When you disable lock escalation on the table you only get 4k X locks on each hobt_id
SELECT resource_type, resource_associated_entity_id, resource_subtype, request_mode, COUNT(*)
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND request_session_id = 55
GROUP BY resource_type, resource_associated_entity_id, resource_subtype, request_mode
*/
ALTER DATABASE [........] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO
-- CREATE NUMBERS TABLE TO HELP CREATE TEST DATA
if OBJECT_ID('numbers') is not null
DROP TABLE dbo.numbers
GO
CREATE TABLE dbo.numbers(i INT)
INSERT INTO dbo.numbers
SELECT TOP 4000 ROW_NUMBER() OVER (ORDER BY (o.object_id)) as i
FROM sys.objects o
CROSS APPLY sys.objects o2
CROSS APPLY sys.objects o3
-- CREATE A TABLE FOR THE INSERT STATEMENT + INDEXES
if OBJECT_ID('insert_into') is not null
DROP TABLE dbo.insert_into
GO
CREATE TABLE dbo.insert_into
(field1 INT NOT NULL,
field2 INT NOT NULL,
field3 INT NOT NULL,
value1 INT,
value2 INT)
ALTER TABLE dbo.insert_into ADD CONSTRAINT pk_insert_into PRIMARY KEY (field1,field2,field3)
CREATE INDEX idx_insert_into ON dbo.insert_into (field1, field3,value1) INCLUDE( value2)
-- POPULATE TABLE WITH SOME DUMMY DATA, WE WANT TO CREATE DUPLICATES ON THE NON CLUSTERED INDEX (field1, field3)
DECLARE @idx INT = 0
DECLARE @idx_max INT = 100 ---ON LAPTOP COUNT OF DUPLICATES <= 52 SEEMS TO RESULTS IN NO TAB LOCK, >= 53 RESULT IN TABLOCK
WHILE @idx < @idx_max
BEGIN
INSERT INTO dbo.insert_into
SELECT 1, @idx, i , RAND(), RAND()
FROM numbers
SET @idx = @idx + 1
END
-- TRY AND INSERT DATA INTO THE TABLE WITH A NOT EXISTS
DECLARE @staging TABLE (field1 INT, field2 INT, field3 INT, PRIMARY KEY (field1,field2,field3))
INSERT INTO @staging (field1,field2,field3)
SELECT top 4000 1,101,i
FROM dbo.numbers
BEGIN TRAN
INSERT INTO dbo.insert_into WITH (ROWLOCK) (field1,field2,field3)
SELECT s.field1,s.field2, s.field3
FROM @staging s
LEFT OUTER LOOP JOIN dbo.insert_into n
ON s.field1 = n.field1
AND s.field2 = n.field2
AND s.field3 = n.field3
WHERE n.field1 IS NULL
OPTION (FORCE ORDER, MAXDOP 1, RECOMPILE)
ROLLBACK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply