How do I cancel an app lock request in SQL Server?

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?

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

StackExchange Database Administrators Q#170617, answer score: 6