1

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..

Improve this question
2

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
Improve this answer
5
  • By per-session, to clarify my understanding, this means that Session A can take 4k locks and Session B can take 4k locks on the same table, with neither escalating to a table lock? That sufficiently negates most of my interactions concerns. – user2864740 Apr 19 at 18:41
  • Disabling tabling locks on table seems like enough potential fun (and is already largely discounted even with restricted access patterns 'in case' there is a nonsense query), I'll definitely skip the entire instance flag, and the DBAs might have a heart attack at even the mention (and promptly say no in a very scolding matter) ^_^ – user2864740 Apr 19 at 18:43
  • I want to have control over when table locks are taken because of knowledge of data and the access. For example, if a table lock is taken - say as 6k locks are acquired in the batch insert - then one of the updates may take "a second" where it normally takes "milliseconds". This will result in terrible performance over many small interactions, even though the actual insert operation is still small. The issue here is that the lock escalation happens at the 5k magic number making it go from a "non-blocking" to a "blocking" insert and there is no concern of lock resource exhaustion. – user2864740 Apr 19 at 18:46
  • Page locks effectively extend the insert batch size, and since the batch size is controlled at some level there is no concern that the specific query will jump to the many-millions of locks (although this is only guaranteed through properly written statements). The data access patterns are also such that there are effectively no actual contending locks due to the huge distribution (these are TB tables with clustering along a leading time axis) and delay between how the data is inserted and later updated. – user2864740 Apr 19 at 18:50
  • See update to answer. – David Browne - Microsoft Apr 19 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.