question

StefanFalk-3673 avatar image
1 Vote"
StefanFalk-3673 asked MelissaMa-msft commented

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

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


sql-server-transact-sqldotnet-visual-basicdotnet-sqlclient
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi StefanFalk-3673,

Welcome to Microsoft Q&A!

Per your description, your issue might be related to .NET-VB. I have added related tag and you will get more professional help from many experts.

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
DanGuzman avatar image
0 Votes"
DanGuzman answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanFalk-3673 avatar image
0 Votes"
StefanFalk-3673 answered StefanFalk-3673 edited

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://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanFalk-3673 avatar image
0 Votes"
StefanFalk-3673 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanFalk-3673 avatar image
0 Votes"
StefanFalk-3673 answered Viorel-1 commented

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!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe LockOwner must be the same?


0 Votes 0 ·
StefanFalk-3673 avatar image
0 Votes"
StefanFalk-3673 answered

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://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.