25

The sp_getapplock stored procedure has the following return values:

0: The lock was successfully granted synchronously.
1: The lock was granted successfully after waiting for other incompatible locks to be released.
-1: The lock request timed out.
-2: The lock request was canceled.
-3: The lock request was chosen as a deadlock victim.
-999: Indicates a parameter validation or other call error.

I am writing a wrapper for calling sp_getapplock in our data access layer and I want to know under which circumstances -2 can be returned so that I can throw a descriptive and helpful exception. It is obvious what return values of -1 and -3 mean and I can easily create test conditions that cause those values to be returned. How would I manage to get a return value of -2?

Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
Heinzi
  • 3,210
  • 2
  • 32
  • 43

3 Answers3

6

Looking at the source of the sp_getapplock wrapper proc, all the return values except for -999 originate from the underlying sys.xp_userlock internal stored procedure. I'd bet the internal proc returns a -2 when the request is cancelled by an attention event (client query timeout or explict client query cancel). However, no further sp_getapplock code executes after the batch is cancelled, including the RETURN statement. Consequently, the -2 return code may be returned internally but there's no practical way for the client to get the value.

Assuming this theory is correct, there isn't any value in translating to -2 to more descriptive message since it's the client that cancelled the request in the first place.

I'll leave it to Paul to confirm this by stepping through the SQL database engine code with a debugger :-)

Dan Guzman
  • 28,989
  • 2
  • 46
  • 71
1

sp_getapplock creates locks on semaphores, not physical objects (per MSDN). It will only block another process if it is sp_getapplock with same string and an incompatible lock mode.

So lock requests would be cancelled under circumstances such as: a user with higher privileges cancels the lock, a server process cancels the lock, the user running the stored procedure or an administrator kills the lock process. Your description might be "lock cancelled by the system or another user." I'm not sure how you would determine the actual process/user that cancelled the lock.

Ben Schmeltzer
  • 87
  • 4
0

There's a corresponding Release application lock stored procedure called sp_releaseapplock.

I wrote an article confusingly titled "Mutexes in SQL" here on SQL Server Central about using these stored procedures to control application flow.

Toby
  • 159
  • 5