I am implementing pseudo-locking in an Access project. I have a table, tblLock which holds the lock records (includes the area being locked, the user, a timestamp).

I could simply rely on this table be the "gatekeeper" as it were, but it seems to me that there is a potential for race conditions.

Example:

• User A opens recordset to add a new lock record for area Z
• User B opens recordset to add a new lock record for area Z
• Both check the recordset to see that the record is not currently locked
• Both are then able to add lock records for area Z.

To solve this, when the application was MS Access only, I use the following to eliminate concurrent editing:

    Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenDynaset, dbDenyWrite)

where stSQL is something like

SELECT * FROM tblLock WHERE [Area] = 'TestArea'

With this, only one user can make edits to tblLock at any given moment. User B in the above scenario cannot even open the RecordSet for editing tblLock. Thus the race condition is eliminated. I'm basically trying to use a coding pattern similar to Java's synchronization.

The problem, the presence of dbDenyWrite elicits the following error message:

ODBC--Cannot lock all records.

NOTE: for the version with SQL Server in the backend, the OpenRecordset line has been changed to read as follows:

    Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenDynaset, dbSeeChanges + dbDenyWrite)    

It there a way to encode OpenRecordset so that it prevents concurrent editing of tblLock? It would be nice if concurrent ReadOnly access were still allowed to the table, but if necessary, I can drop that requirement.

I am implementing pseudo-locking in an Access project. I have a table, tblLock which holds the lock records (includes the area being locked, the user, a timestamp).

I could simply rely on this table be the "gatekeeper" as it were, but it seems to me that there is a potential for race conditions.

Example:

• User A opens recordset to add a new lock record for area Z
• User B opens recordset to add a new lock record for area Z
• Both check the recordset to see that the record is not currently locked
• Both are then able to add lock records for area Z.

To solve this, when the application was MS Access only, I use the following to eliminate concurrent editing:

    Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenDynaset, dbDenyWrite)

where stSQL is something like

SELECT * FROM tblLock WHERE [Area] = 'TestArea'

With this, only one user can make edits to tblLock at any given moment. User B in the above scenario cannot even open the RecordSet for editing tblLock. Thus the race condition is eliminated. I'm basically trying to use a coding pattern similar to Java's synchronization.

The problem, the presence of dbDenyWrite elicits the following error message:

ODBC--Cannot lock all records.

NOTE: for the version with SQL Server in the backend, the OpenRecordset line has been changed to read as follows:

    Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenDynaset, dbSeeChanges + dbDenyWrite)    

It there a way to encode OpenRecordset so that it prevents concurrent editing of tblLock? It would be nice if concurrent ReadOnly access were still allowed to the table, but if necessary, I can drop that requirement.

• sql-server
• ms-access
• locking

• I suggest you handle this via SQL-Server stored procedures called from access. SQL-Server can then make use of Transactions to ensure only one user is able to set a lock record at a time. – Bart McEndree Commented Feb 5 at 19:44
• 1 Transactions don't ensure one user at a time... that would be a table lock. – Dale K Commented Feb 5 at 19:50
• Some suggest you create an index and simply handle the error when user A or B violates it. stackoverflow.com/questions/20971680/… – Bart McEndree Commented Feb 5 at 20:46
• 2 If you have a unique index on the locking table rows, you can see which "side" wins and proceed accordingly, as they say, there can be only one. Darn, @BartMcEndree just beat me to it, shouldn't have wasted time quoting Highlander – siggemannen Commented Feb 5 at 20:49
• I agree with Dale that transactions don't eliminate race conditions. At least, not in any way that I've been able to determine so far. I'm going to think through whether there's a way to make use of the lock table index. Unfortunately, it seems the answers on question 20971680 (from Bart) admit to permitting race conditions. (This is part of why I hate working with MS--with Java they've got concurrency nailed.) The whole point of adding SQL Server as the back end was to avoid Access kloojiness and occasional crashes. – Phil Freihofner Commented Feb 6 at 5:11

1 Answer 1

As suggested in the comments?

Just create the table locks, and add a unique index to the two columns you want to use for the lock.

Public Function Lockit(sForm As String, PK As Long) As Boolean

    Dim rstLock          As DAO.Recordset
    Dim strSQL           As String
   
    strSQL = "SELECT * FROM tblLocks WHERE Form = '" & sForm & "' AND " & _
             "PK = " & PK
    
    Set rstLock = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    If rstLock.RecordCount <> 0 Then
       Lockit = False
    Else
       rstLock.AddNew
       rstLock!Form = sForm
       rstLock!PK = PK
       rstLock.Update
       Lockit = True
    End If

    rstLock.Close

End Function


Public Sub UnLockIt(sForm As String, PK As Long)

    Dim strSQL       As String
   
    strSQL = "DELETE FROM tblLocks WHERE Form = '" & sForm & "' AND PK = " & PK
    
    CurrentDb.Execute strSQL, dbSeeChanges
    

End Sub


Sub TestLock()


   Dim PK       As Long
   Dim sForm    As String
   
   PK = 123
   sForm = "frmHotels"
   
   Debug.Print Lockit(sForm, PK)
   
   
End Sub

So, since we have a unique index on the Form and PK?

Then no insert to the table is possible, and thus 2 records can never be added to the lock table as a result.

So, just create a index on the SQL table based on 2 columns, and set the index to unique.

So, adding the index looks like this:

And then setting index to unique:

So, you don't need to "really" lock any table, since SQL server will prevent two rows with the same "form" and "PK". Of course, you can change "Form" to "MyTable" or some such (table is a reserved word - so, don't use that).

I also have as a personal preference have the routine returning True if the lock was granted. I suppose some developers might prefer the reverse, but regardless, the above sample code is a proof of concept based on this simple idea of using a unique index on the two columns.

Now, it "might" be possible that 2 users enter the else condition (in which a lock was not found). I actually don't believe this will occur due to how SQL accepts requests from many users, but will sequential execute those requests.

If for some reason over time, you did have this occur?

You could I suppose add the following code in the lock routine to deal with this case (which I don't think will occur).

Hence, this:

    Else
        rstLock.AddNew
        rstLock!Form = sForm
        rstLock!PK = PK
        On Error Resume Next
        rstLock.Update
        If Err.Number = 0 Then
            Lockit = True
        Else
            Lockit = False
        End If
        On Error GoTo 0 ' turn off error handling
    End If

So, you could I suppose trap a failed insert with error handing, and also assume that is a failed lock. As noted, you probably don't need to add this extra precaution, but might as well.