Lock escalation on update statement that uses a primary key

91 views Asked by At

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?

0

There are 0 answers