We try to create a simple class for managing sp_getapplock/sp_releaseapplock. A test does the following simultaneously from multiple threads:
Open an own connection (for each thread)
Begin an own transaction on this thread (so each thread has its own connection and own transaction)
Execute "EXEC sp_getapplock @Resource = @ResourceName, @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0;" with the same (!) resource name from each thread, thus in each transaction and connection.
And sp_getapplock returns 0 (success) for each thread. I want to have only one call succeed and all others return a negative number, as from the second lock on, sp_getapplock should fail in my opinion.
Is this behaviour by design? Because I execute the calls from the same process? Despite the fact that each thread uses its very own connection and transaction to SQL Server?
Note: I have to synchronize some long running .net code this way (using sp_getapplock, if possible). So I cannot have sp_getapplock and sp_releaseapplock in the same stored procedure. Therefore, I keep the transaction open until I release. But the problem is that sp_getapplock is willing to aquire the same lock twice (before a lock gets released)!
What do I make wrong please?