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.
Best Answer
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 about26 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.
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 theresource_description
column ofsys.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.