sp_getapplock aquires the same lock successfully in 2 threads of the same app

Question

Hello everybody,

We try to create a simple class for managing sp_getapplock/sp_releaseapplock. A test does the following simultaneously from multiple threads:

1. Open an own connection (for each thread)
2. Begin an own transaction on this thread (so each thread has its own connection and own transaction)
3. 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?

Best Regards,
Stefan Falk

Answer 1

I ran the following from 2 different SSMS query windows and the first returned zero and the second -1 as expected.

BEGIN TRAN;
DECLARE @return_code int;
EXEC @return_code = sp_getapplock @Resource = 'MyResourceName', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0;
PRINT @return_code;
GO

Please share your .NET code.

Answer 2

Hello Dan,

Thank you for your super-fast response! I digged into that more deeply and found that even using only one thread I have a problem:

1. Open Connection
2. Begin Transaction
3. Execute sp_getapplock. Returns 0.
4. Execute sp_releaseapplock in a new command with the same connection and transaction: Error. SQL Server says, the resource is not locked at all, thus I cannot release it.

This is the ApplicationLockManager class:

Public Class ApplicationLockManager  
    Implements IDisposable  
  
    ' See https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15  
  
    Private ReadOnly myResourceName As String  
    Private ReadOnly myLockObject As Object = New Object  
    Private myLockConnection As IDbConnection  
    Private myLockTransaction As IDbTransaction  
  
    Public Sub New(resourceName As String)  
  
        If String.IsNullOrEmpty(resourceName) Then  
            Throw New ArgumentNullException(NameOf(resourceName))  
        ElseIf resourceName.Length > 255 Then  
            Throw New ArgumentException(NameOf(resourceName) & " is too long.", NameOf(resourceName))  
        Else  
            myResourceName = resourceName  
        End If  
    End Sub  
  
    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities",  
                                  Justification:="Statements stem from private callers and are static.")>  
    Private Function ExecuteSql(sqlText As String) As Integer  
  
        If String.IsNullOrEmpty(sqlText) Then  
            Throw New ArgumentNullException(NameOf(sqlText))  
        Else  
            Using command = myLockConnection.CreateCommand  
                command.CommandText = sqlText  
  
                Dim parameter = command.CreateParameter  
  
                parameter.DbType = DbType.String  
                parameter.ParameterName = "@ResourceName"  
                parameter.Value = myResourceName  
  
                command.Parameters.Add(parameter)  
                command.Transaction = myLockTransaction  
                Return CInt(command.ExecuteScalar())  
            End Using  
        End If  
    End Function  
  
    Public Function TryLock() As Boolean  
  
        SyncLock myLockObject  
            If myLockConnection IsNot Nothing Then  
                Throw New InvalidOperationException("TryLock cannot be called reentrant.")  
            Else  
                myLockConnection = DefaultObjects.DBFactory.CreateConnection  
                Try  
                    myLockConnection.Open()  
                    Try  
                        myLockTransaction = myLockConnection.BeginTransaction  
                        Try  
  
                            Dim result = ExecuteSql(  
                                "DECLARE @Result INTEGER; EXEC @Result = sp_getapplock @Resource = @ResourceName, @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0; SELECT @Result;")  
  
                            If result >= 0 Then  
                                Return True  
                            ElseIf result = -999 Then  
                                Throw New InvalidOperationException("Lock operation failed.")  
                            Else  
                                Return False  
                            End If  
                        Catch  
                            Try  
                                myLockTransaction.Rollback()  
                            Finally  
                                myLockTransaction = Nothing  
                            End Try  
                            Throw  
                        End Try  
                    Catch  
                        myLockConnection.Close()  
                        Throw  
                    End Try  
                Catch  
                    myLockConnection = Nothing  
                    Throw  
                End Try  
            End If  
        End SyncLock  
    End Function  
  
    Public Sub ReleaseLock()  
  
        If myLockConnection IsNot Nothing Then  
            SyncLock myLockObject  
                If myLockConnection IsNot Nothing Then  
  
                    Dim result As Integer  
  
                    Try  
                        result = ExecuteSql("EXEC sp_releaseapplock @Resource = @ResourceName, @LockOwner = 'Session';")  
                    Finally  
                        Try  
                            Try  
                                myLockTransaction.Rollback()  
                            Finally  
                                myLockTransaction = Nothing  
                            End Try  
                            myLockConnection.Close()  
                        Finally  
                            myLockConnection = Nothing  
                        End Try  
                    End Try  
                    If result <> 0 Then  
                        Throw New InvalidOperationException("Unlock operation failed.")  
                    End If  
                End If  
            End SyncLock  
        End If  
    End Sub  
  
#Region "IDisposable Support"  
  
    Private disposedValue As Boolean ' To detect redundant calls  
  
    ' IDisposable  
    Protected Overridable Sub Dispose(disposing As Boolean)  
        If Not disposedValue Then  
            If disposing Then  
                ' TODO: dispose managed state (managed objects).  
                ReleaseLock()  
            End If  
        End If  
        disposedValue = True  
    End Sub  
  
    ' This code added by Visual Basic to correctly implement the disposable pattern.  
    Public Sub Dispose() Implements IDisposable.Dispose  
        ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.  
        Dispose(True)  
    End Sub  
  
#End Region  
  
End Class  

And this is the unit test:

    <TestMethod> Public Sub TestApplicationLockManagerSuccess()  
  
        Const ResourceName = "TestResource"  
  
        Using manager As New ApplicationLockManager(ResourceName)  
            Assert.IsTrue(manager.TryLock)  
        End Using  
    End Sub  

The test fails in its End Using statement, which calls ReleaseLock(), and where I get SQL error 1223 (the lock cannot be released because it is not being hold at all).

So the lock gets aquired fine, but it does not get hold after the execution of the first command.

Best Regards,
Stefan

Answer 3

I'll try to create super-simple sample code to avoid having made some stupid small error. Please stand by ;-)

Answer 4

This code is failing:

    <TestMethod> Public Sub TestGetAppLock()  
  
        Using connection As New SqlClient.SqlConnection("Server=.;Trusted_Connection=yes")  
            connection.Open()  
            Using transaction = connection.BeginTransaction  
                Using cmd = connection.CreateCommand  
                    cmd.Transaction = transaction  
  
                    cmd.CommandText =  
                        "DECLARE @Result INTEGER; EXEC @Result = sp_getapplock @Resource = 'TestResource', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0; SELECT @Result;"  
  
                    Assert.AreEqual(0, cmd.ExecuteScalar())  
  
                    cmd.CommandText =  
                        "DECLARE @Result INTEGER; EXEC @Result = sp_releaseapplock @Resource = 'TestResource', @LockOwner = 'Session'; SELECT @Result;"  
  
                    Assert.AreEqual(0, cmd.ExecuteScalar()) ' Fails as resource is not locked at all.  
                End Using  
            End Using  
        End Using  
    End Sub  

OMG. I aquired using Transaction, but released using Sesssion. Fixed that, works. Now trying the big class. Sorry!

Answer 5

My deepest apologies for posting all this nonsens here and for being so blind and stupid! Sorry!

If I can give back something, here is the working class including its unit tests:

Public Class ApplicationLockManager  
    Implements IDisposable  
  
    ' See https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15  
  
    Private ReadOnly myResourceName As String  
    Private ReadOnly myLockObject As Object = New Object  
    Private myLockConnection As IDbConnection  
    Private myLockTransaction As IDbTransaction  
  
    Public Sub New(resourceName As String)  
  
        If String.IsNullOrEmpty(resourceName) Then  
            Throw New ArgumentNullException(NameOf(resourceName))  
        ElseIf resourceName.Length > 255 Then  
            Throw New ArgumentException(NameOf(resourceName) & " is too long.", NameOf(resourceName))  
        Else  
            myResourceName = resourceName  
        End If  
    End Sub  
  
    <CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities",  
                                  Justification:="Statements stem from private callers and are static.")>  
    Private Function ExecuteSql(sqlText As String) As Integer  
  
        If String.IsNullOrEmpty(sqlText) Then  
            Throw New ArgumentNullException(NameOf(sqlText))  
        Else  
            Using command = myLockConnection.CreateCommand  
                command.CommandText = sqlText  
  
                Dim parameter = command.CreateParameter  
  
                parameter.DbType = DbType.String  
                parameter.ParameterName = "@ResourceName"  
                parameter.Value = myResourceName  
  
                command.Parameters.Add(parameter)  
                command.Transaction = myLockTransaction  
                Return CInt(command.ExecuteScalar())  
            End Using  
        End If  
    End Function  
  
    Private Sub RollbackAndClose()  
  
        If myLockConnection IsNot Nothing Then  
            Try  
                myLockTransaction.Rollback()  
            Finally  
                Try  
                    myLockConnection.Close()  
                Finally  
                    myLockTransaction = Nothing  
                    myLockConnection = Nothing  
                End Try  
            End Try  
        End If  
    End Sub  
  
    Public Function TryLock() As Boolean  
  
        SyncLock myLockObject  
            If myLockTransaction IsNot Nothing Then  
                Throw New InvalidOperationException("TryLock cannot be called reentrant.")  
            Else  
                myLockConnection = DefaultObjects.DBFactory.CreateConnection  
                myLockConnection.Open() ' Todo: Create your connection here.  
                Try  
                    myLockTransaction = myLockConnection.BeginTransaction  
                    Try  
  
                        Dim result = ExecuteSql(  
                            "DECLARE @Result INTEGER; EXEC @Result = sp_getapplock @Resource = @ResourceName, @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0; SELECT @Result;")  
  
                        If result = -999 Then  
                            Throw New InvalidOperationException("Lock operation failed.")  
                        ElseIf result >= 0 Then  
                            Return True  
                        Else  
                            RollbackAndClose()  
                            Return False  
                        End If  
                    Catch  
                        RollbackAndClose()  
                        Throw  
                    End Try  
                Catch  
                    If myLockConnection.State = ConnectionState.Open Then  
                        myLockConnection.Close()  
                    End If  
                    Throw  
                End Try  
            End If  
        End SyncLock  
    End Function  
  
    Public Sub ReleaseLock()  
  
        If myLockConnection IsNot Nothing Then  
            SyncLock myLockObject  
                If myLockConnection IsNot Nothing Then  
  
                    Dim result As Integer  
  
                    Try  
  
                        result = ExecuteSql(  
                            "EXEC sp_releaseapplock @Resource = @ResourceName, @LockOwner = 'Transaction';")  
  
                    Finally  
                        RollbackAndClose()  
                    End Try  
                    If result <> 0 Then  
                        Throw New InvalidOperationException("Unlock operation failed.")  
                    End If  
                End If  
            End SyncLock  
        End If  
    End Sub  
  
#Region "IDisposable Support"  
  
    Private disposedValue As Boolean ' To detect redundant calls  
  
    ' IDisposable  
    Protected Overridable Sub Dispose(disposing As Boolean)  
        If Not disposedValue Then  
            If disposing Then  
                ' TODO: dispose managed state (managed objects).  
                ReleaseLock()  
            End If  
        End If  
        disposedValue = True  
    End Sub  
  
    ' This code added by Visual Basic to correctly implement the disposable pattern.  
    Public Sub Dispose() Implements IDisposable.Dispose  
        ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.  
        Dispose(True)  
    End Sub  
  
#End Region  
  
End Class  
  
  
  
  
    <TestMethod> Public Sub TestApplicationLockManagerSuccess()  
  
        Const ResourceName = "TestResource"  
  
        Using manager As New ApplicationLockManager(ResourceName)  
            Assert.IsTrue(manager.TryLock)  
        End Using  
    End Sub  
  
    <TestMethod> Public Sub TestApplicationLockManagerFail()  
  
        Const ResourceName = "TestResource"  
  
        Using manager1 As New ApplicationLockManager(ResourceName)  
            Assert.IsTrue(manager1.TryLock)  
            Using manager2 As New ApplicationLockManager(ResourceName)  
                Assert.IsFalse(manager2.TryLock)  
            End Using  
        End Using  
    End Sub  

Again, apologies, and thank you for answering.

Stefan