I inherited a database that seems to have a LOT of lock escalation going on. I ran a trace and it's full of escalation events.
Some of it is caused by things like BULK INSERTS. I can understand how that causes an issue with locking.
There are some other queries that I didn't think should cause an escalation, but they do.
Maybe there is something basic that I'm misunderstanding about lock escalation...
Here are two examples that seems odd:
UPDATE Alerts
SET Comments = @Comments
WHERE PKID = @PKID
The PKID
on the Alerts
table is the primary key. So, this statement will alter exactly one row, and yet there is a lock-escalation happening on this query.
Is it possible for a single record update to take a full table lock? (clearly it must be)
The other query makes slightly more sense at first glance:
DECLARE @p__linq__0 bigint = 0 --This is a parameter that is passed in.
DECLARE @rowAffected INT
DECLARE @totalRowAffected INT
SET @totalRowAffected = 0
WHILE @rowAffected IS NULL
OR @rowAffected > 0
BEGIN
DELETE TOP (4000) *
FROM [dbo].[Alerts] AS A
INNER JOIN (SELECT [Extent1].[PKID] AS [PKID]
FROM [dbo].[Alerts] AS [Extent1]
WHERE [Extent1].[PKID] <= @p__linq__0) AS B ON A.[PKID] = B.[PKID]
SET @rowAffected = @@ROWCOUNT
SET @totalRowAffected = @totalRowAffected + @rowAffected
END
SELECT @totalRowAffected
The above query, I believe, is supposed to delete the oldest records from the table. The aim is to ensure that the database only has a certain number of years worth of data in it.
So, the PKID
is found for a cut-off point and everything before that PKID
is then deleted.
The delete statement seems sensible, since the TOP(4000)
should keep it under the 5000 row limit for lock escalation.
The query performs an index SEEK and that says that it estimates that it will read 474 000 records to find the 4000 in question.
So, could it be escalating the lock because of the large index read? How would I verify this?
Or, is something else potentially causing this?
For a start - I'd love to understand why it's behaving like this to begin with.
I have limited ability (but I do have some) to change the system. I might have to stick mostly to things like changing indexes though.
Is it possible that because there is a lot of table-locks already, SQL Server says that it will lock the table more aggressively? I'm just wondering if this is a problem that might be feeding itself?