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