5

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.

1 Answer 1

8

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

enter image description here

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

7
  • 1
    As for, "why", I need to replicate the behavior of a simplistic database environment that supports only pessimistic row-based locking that survives transactions. Think of a box of index cards where one person at a time can hold a card. So definitely a special case, as noted in my Question. After much research I've concluded that the optimistic locking systems of MS SQL Server and Postgres are not able to support a simplistic pessimistic record-lock behavior directly. As a workaround, the explicit arbitrary locks (sp_applock here, Advisory Locks in Postgres) can be employed for this purpose. Jun 19, 2017 at 22:18
  • Thanks so much for your excellent Answer. Good information that will help others in the future. Your chart is funny and informative. Can you cite the source? Jun 19, 2017 at 22:29
  • @BasilBourque I cited it in the answer (though in a different paragraph, so I'll also add a citation right next to the chart). Here's the link: preshing.com/20110504/hash-collision-probabilities Jun 20, 2017 at 13:17
  • @BasilBourque The locking I believe you're describing is equivalent to the SERIALIZABLE isolation level that you can set at a transaction level. If this is a test scenario, you should be able to set the isolation level for all transactions explicitly and take advantage of this without creating a custom locking routine. Jun 20, 2017 at 13:28
  • 1
    We have a hash collision using this function. The values that caused the collision: "5.14531731.0" hashes to "a56f30a2" as does "5.706010192.617831". SQL to duplicate collision: EXEC \@result = sp_getapplock \@Resource = '5.14531731.0', \@LockMode = 'Exclusive', \@LockOwner = 'Session', \@LockTimeout = '0', \@DbPrincipal = 'public'; After doing that, this one fails: EXEC \@result = sp_getapplock \@Resource = '5.706010192.617831', \@LockMode = 'Exclusive', \@LockOwner = 'Session', \@LockTimeout = '0', \@DbPrincipal = 'public'; Remove all backslashes
    – flxkid
    Mar 16, 2021 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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