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:
- Open Connection
- Begin Transaction
- Execute sp_getapplock. Returns 0.
- 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