0

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?

Mulciber Coder
  • 127
  • 9
  • 1
    Why the `INNER JOIN` to a derived table here? Why not just have `[PKID] <= @p__linq__0` in the `WHERE`? – Thom A Feb 27 '23 at 11:03
  • @Larnu: Not sure. The "Extent1" alias makes me think that this was a query that was somehow generated by one of the older versions of Entity Framework. Back then, it used to do this kind of thing quite a bit and the inner selects were always called Extent1..2..3 . That's not a reason, I know, but I think that's why it looks like this. Functionally - I don't see any reason for it either. Unfortuantely I don't have access to the C# code for this solution, so short of a rewrite of that component - I might not be able to change that. Can we point at that as reason for lock escalation though? – Mulciber Coder Feb 27 '23 at 11:17
  • I'm, (un)fortunately, not familiar with EF, however, I would suggest that the derived table is only a hindrance to the query performance (and potential locking). – Thom A Feb 27 '23 at 11:19
  • 1
    I would expect an index seek in the plan instead of scan. Upload the actual execution plan to [Paste The Plan](https://www.brentozar.com/pastetheplan/) and add the link to your question. Maybe there's something else going on. – Dan Guzman Feb 27 '23 at 11:24
  • @DanGuzman: Sorry - I mistyped. It is doing a SEEK. Larnu has a point. It's actually doing 2 seeks due to the inner join and the one really isn't needed at all. Any idea why the single update statement might cause a lock escalation though? That one bugs me.... – Mulciber Coder Feb 27 '23 at 12:53

0 Answers0