Limits of applock in MS SQL Server

Question

Microsoft SQL Server offers an arbitrary application-defined locking mechanism through the sp-getapplock command and related commands.

The key, or resource_name, is specified as a string, a varchar 255. But the documentation also mentions that the key/name is hashed.

So that leads me to wonder:

What kind of hashing function is used?
How well-distributed are the results of that hashing function?
What kind of input should I use to widen the distribution of hash result, to minimize chance of coincidental collision?
What is the theoretical and practical limits on the number of applocks I can have active?
What resources are used by applocks, such as amount of memory or limits on memory?

I am implementing a pessimistic locking mechanism for the rows of many tables for a special-needs app. As my applock key/name I plan to use a number representing each table combined with an ID number stored in each row. An example key/name might be something like mydb_myschema_table0000000142_row0000241738. I may have many of these active at a time, so I need to know about these limits.

Geoff Patterson · Accepted Answer · 2017-06-20T13:18:35.160

I didn't see official documentation answering your questions, but some of these things you can yourself (on a testing/dev server first!). Here's what I found:

Unless you are hoping to use a very large number of concurrent locks, you likely will not have any problems. On SQL Server 2017 CTP2.1, I was able to acquire 1MM concurrent locks in about 26 seconds. There were no collisions, and this appeared to use ~286MB of memory.

The SQL Server documentation specifies a maximum of 2,147,483,647 locks for 32 bit and "Limited only by memory" for 64 bit (which I assume you're using).

Test script

Below is a script that acquires 1MM locks and does basic monitoring of the elapsed time, memory consumed, collisions encountered.

BEGIN TRAN;

DECLARE @result INT, 
    @i INT = 1,
    @locks_attempted INT = 1000000,
    @locks_acquired INT,
    @ts DATETIME2 = SYSDATETIME();

WHILE (@i <= @locks_attempted)
BEGIN
    DECLARE @Resource NVARCHAR(255) =
        'LongStringSoThatTheFirst32CharactersAreIdentical_' + CAST(@i AS VARCHAR)
    EXEC @result = sp_getapplock @Resource = @Resource, @LockMode = 'Exclusive';
    IF (@result <> 0) THROW 50000, 'Failed to acquire lock', 0
    SET @i = @i + 1;
END

DECLARE @elapsed_ms INT = DATEDIFF(MILLISECOND, @ts, SYSDATETIME());

SELECT @locks_acquired = COUNT(*)
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
    AND resource_type = 'APPLICATION';

-- Acquired 1000000 of 1000000 locks in 26351 milliseconds
RAISERROR('Acquired %i of %i locks in %i milliseconds', 0, 1,
    @locks_acquired, @locks_attempted, @elapsed_ms);

-- ~286MB of memory for OBJECTSTORE_LOCK_MANAGER
SELECT *
FROM sys.dm_os_memory_clerks
WHERE type = 'OBJECTSTORE_LOCK_MANAGER';

ROLLBACK;

What about collisions?

I didn't observe collisions in any of my trials, but based on the documentation for sys.dm_tran_locks, it does appear that a 32 bit hash is used. With such a short hash, the odds of a collision should be fairly high (see chart below). However, even with 10MM concurrent locks, I did not achieve a collision. This might indicate that the hash is more complex under the hood, and that the resource_description column of sys.dm_tran_locks simply shows the first 32 bits of the hash (similar to the way it shows only the first 32 characters of the @Resource parameter). Or it might indicate that I was lucky.

If you are going to use this strategy, you should check to see if you get any collisions on your actual data. If you do, you could try to put unique elements (e.g., the schema_id, object_id, and your rowid) at the beginning of the @Resource. (It's not fully clear, but the documentation suggests that the first 32 characters of @Resource might be combined with the hash in order to uniquely identify the lock.)

(Image from Preshing on Programming)

Why do you want to do this?

It seems unusual to use sp_getapplock so heavily for something that seems similar the type of row-level locking that SQL Server provides on its own. I'm assuming you've carefully considered other approaches, but if you haven't yet done so, you might want to think about other solutions that don't involve sp_getapplock.

Limits of applock in MS SQL Server

1 Answers1