Does SQL Server upgrade to table locks at total locks or session locks?

Question

I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so the read-only queries shouldn’t affect the lock count (?) in relation to the sequential insert.

My concern is that, even when limiting the maximum number of locks taken during the insert (eg. inserting in batches), it is possible for one (or more) of the OLTP updates to bypass this limit. If the lock count heuristic is per-session then it is less of potential issue.

Given the answer to the question in the title, then, what is the “best” way to prevent table lock escalation here?

My current approach/thought is to select a row count (eg. arbitrary of 1-4k) during the mass inserts to allow “some slack”, although this feels overall imprecise. While batches are essential a way to deal with replication and such, it would be nice to specify a batch size of 5k rows and move on. (To be fair, quick table locks aren’t really the issue: the intent of the question is more about finding the edge such that table lock escalation doesn’t happen.)

There has been DBA pushback on both 1) disabling row locks (to ensure page locks and thus reduce lock counts) and 2) disabling table lock escalation (with forced page locks to minimize worst-case). Are there any other relevant database properties to consider with respect to lock escalation? (Increasing the lock limit to say, 10k would then allow a much larger “slack” batch size.)

Using with PAGLOCK in the batch inserts results in page-row deadlocks with the current instead-of triggers and generally appears to be a pain to get correct. Although the instead-of triggers themselves are currently on the axing block for various technical complications. This hint also only increases the “slack”. I don’t suppose there is a NOINCRLOCKCOUNT hint that has been overlooked..

Answer 1

If the lock count heuristic is per-session then it is less of potential issue.

It is per-session.

Are there any other relevant database properties to consider with respect to lock escalation?

On the database, no, but you can disable lock escalation on the whole instance by enabling trace flag 1211.

what is the “best” way to prevent table lock escalation here?

Why would you want to?

Lock escalation serves a useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail

https://docs.microsoft.com/en-us/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation

That article also outlines an additional way to prevent lock escalation for a time on a table by running (from a seperate session) a query like:

BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN