Action Queries in Multi-User Database

Question

Something I've been wondering over the last few weeks while I've been developing my current database project is about how Access handles Action Queries in a multi-user environment.

I'm using Action Queries quite extensively throughout the database and I'd like to hear from anyone else who has tried something similar.

To give an idea of what I'm working on we're looking at up to 20 users logged into the database as once, with as many as 1000 records being added per day across mainly two tables. That sounds like a lot of records but I've done some estimates and I think we'll be adding ~2-3mb to the database per month. Not being an experienced database developer I don't know if that is a lot or not, but it's certainly a lot less then the Excel files we're currently storing this information in!

Due to the nature of how the work will be done we should never have a situation of two users working on the same record at once, so with the record locking set to 'edtted records only' on the queries I believe that I shouldn't have any issues, although my experience with any software is 'never assume anything, especially that there won't be any problems!'.

My question is how well does Access handle multiple users launching action queries at the same time on the same tables?

Answer 1

If two users run an action query at very nearly the same time, the slightly earlier one will lock the table so that the slightly later one will get an error message.

If there is more time between execution, the second user will run the update query on the data already updated by the first one, which could have unexpected/unwanted consequences.

You have to think carefully about whether you want end users to be able to run update queries.

---

Regards, Hans Vogelaar

Answer 2

Hans

Thanks for the explanation, but this has me worried.

Under the settings for action queries there is the option 'Record Locks' with the options 'edited record' and 'all records', what you say there suggests this option is ignored and it locks the entire table, please could you clarify?

I'm worried because of the fact that the database manages a lot of internal things using action queries, this is all done automatically with macros. I was working on the basis that as long as we never have two users working on the same record at once, we shouldn't have too many issues.

Thanks.

Answer 3

When Access locks a record, it actually locks a "page" containing the record. Depending on the record size, this page may contain one or more neighboring records. So the lock might extend to more than just the record(s) being updated.

But if you can guarantee that users will never be updating the same records simultaneously, the probability of conflicts is small.

---

Regards, Hans Vogelaar

Answer 4

To guarantee this, I implement my own record locking system that allows me to detect cases where two users are attempting to "open" (in editable mode) the same record at the same time. Then, I can give the end-user a message saying they cannot open the record because the other user (name: ...) is already editing the record.

@Hans: I've been wondering whether it would be possible to tap into the native record locking system. Is this RDBMS dependent? Is this information typically handled at too low a level in the RDBMS design (say, of SQL Server) for it to be useful on the application level?

---

Matthew Slyman M.A. (Camb.)

Answer 5

As far as I know, you can't access the record locking system directly - by its nature, it must be pretty low-level.

---

Regards, Hans Vogelaar

Answer 6

Hans

Thank you again, that makes me feel a lot better.

I will still need to handle those possible conflicts, though. But this does mean I don't have to scrap everything I've done so far.

 

Matthew

Detecting if a record is locked before running action queries would be very useful, how do you go about doing that? What I would like to do is something that could queue the conflicting queries and run them once the records are not longer locked. Although this would slow down the program, it would better then giving the user an error message.

Answer 7

I do this on the application layer. All my code is compatible with a wide range of RDBMS systems - I do not depend on stored procedures, and in fact my code is presently quite basic, without database triggers etc.

At the core of my record locking system is a database table which I call "ui_recordlocks_tbl".  Primary key is foreign compound (user_id * db_key_name) - two fields in primary key.  This table contains the fields,

• locked_time (date/time, required, validation rule: <Now()+1)
  
• user_id (text string * 32, required)
• db_key_name (text string * 64, required)
• record_id (text string * 32, required) - I generally use hexadecimal-as-text md5 hashes as surrogate keys. One might instead opt for binary packed SHA-256 hashes though not sure whether Access supports this.
• permissions_code (number: byte, required, default: 1) - permissions code 1 enables records to be "locked" non-exclusively as read-only, for example. Value of this field varies between 1 and 15 (four bits) depending on permissions required (SEL=1, INS=2, UPD=4, DEL=8) [I do realise that only certain combinations make sense]. These codes are also helpful for my "ui_recordlocks_history_tbl" which records a full history of who read/inserted/updated/"deleted" any record in the database (which can then be queried by a db user's history or by a record's history) - the "_history_" table is just like the other table but with "locked_time" included in the primary key (three fields in foreign "primary" key).

db_key_name might have values such as "user_id", "pgr_id" [person/group id], "postaladdress_id" etc.

THEN, when some user asks to open a record with update permissions, I query the table "ui_recordlocks_tbl" to see whether this conflicts with any current record locks before allowing them to proceed. When permission is granted, I immediately insert 1 record into the ui_recordlocks_tbl plus 1 record into the ui_recordlocks_history_tbl.

It's all done at the application level, via a database table stored on the server and indexed appropriately for all the purposes I need it for.  If someone hacks my front-end, there's no "paper trail" other than what the RDBMS itself provides.  I think this is the best I can do for now, if Hans V is right that the RDBMS' record locking systems are not going to be accessible.

Some code that goes along with this: [put this in a module called "SQL"]

Option Compare Database
Option Explicit
Private Const recordlock_duration_seconds As Long = 300 ' 300 seconds = 5 minutes
Public Const SEL As Byte = 1
Public Const INS As Byte = 2
Public Const INS_SEL As Byte = 3
Public Const UPD As Byte = 4
Public Const DEL As Byte = 8

Public Function GetRecordLock(db_key_name As String) As Variant
    GetRecordLock = DLookup("record_id", "ui_recordlocks_tbl", "user_id='" & Usr.id & "' AND db_key_name='" & db_key_name & "'")
End Function

Public Sub ClearRecordLock(db_key_name As String)
    DB.Execute "DELETE FROM ui_recordlocks_tbl WHERE user_id='" & Usr.id & "' AND db_key_name='" & db_key_name & "';"
End Sub

Public Sub ClearAllRecordLocks()
    DB.Execute "DELETE FROM ui_recordlocks_tbl WHERE user_id='" & Usr.id & "';"
End Sub

Public Sub RecordAction(db_key_name As String, permissions_code_for_actions_performed As Byte, Optional action_description As String = "")
    Dim where_condition As String, rlockdetails As DAO.Recordset
    where_condition = "user_id='" & Usr.id & "' AND db_key_name='" & db_key_name & "'"
    Set rlockdetails = DB.OpenRS("SELECT locked_time, record_id FROM ui_recordlocks_tbl WHERE " & where_condition & ";")
    If Not (rlockdetails.BOF And rlockdetails.EOF) Then
        Dim record_id As String, actions_performed As Byte, created_time As Date
        rlockdetails.MoveFirst
        created_time = rlockdetails(0)
        record_id = rlockdetails(1)
        actions_performed = DLookup("actions_performed", "ui_recordlocks_history_tbl", where_condition & " AND created_time=#" & SQL.FormatTime(created_time) & "#")
        DB.Execute "UPDATE ui_recordlocks_history_tbl SET actions_performed=" & (actions_performed Or permissions_code_for_actions_performed) & IIf(action_description = "", "", ", notes_memo=IIf(notes_memo ALIKE '%" & action_description & "%', notes_memo, notes_memo & IIf(Not IsNull(notes_memo),', ','') & '" & SQL.EncodeString(action_description) & "')") & " WHERE created_time=#" & SQL.FormatTime(created_time) & "# AND " & where_condition & " AND record_id='" & record_id & "';"
    End If
    rlockdetails.Close
End Sub

Public Sub RecordActionByMode(mode_id As String, permissions_code_for_actions_performed As Byte, Optional action_description As String = "")
    Dim db_key_name As Variant
    db_key_name = DLookup("db_key_name", "ui_modes_lkp", "mode_id='" & mode_id & "'")
    If LenB(db_key_name) <> 0 Then RecordAction CStr(db_key_name), permissions_code_for_actions_performed
End Sub

Public Function SetRecordLock(db_key_name As String, record_id As String, Optional permissions_code_for_actions_performed As Byte = 1) As Boolean
On Error GoTo Failed
    SetRecordLock = False ' Unless SetRecordLock is subsequently set to True, it is False.
'    Debug.Assert Not IsNull(Usr.id)
    If IsNull(Usr.id) Then ' << Useful during code testing - when code execution is stopped, variable value Usr.id can be lost.
        APP.CloseAllDetailForms
        UI.SetMode "userlogin", True, True
        MsgBox "To access records, you must first log in.", vbCritical, "Must be logged in"
        Exit Function
    End If
    Dim recordlock_time_string As String
    recordlock_time_string = GEO.TimeUTCString
    DB.Execute "INSERT INTO ui_recordlocks_history_tbl (created_time, user_id, db_key_name, record_id, actions_performed) VALUES (#" & recordlock_time_string & "#, '" & Usr.id & "', '" & db_key_name & "', '" & record_id & "', " & permissions_code_for_actions_performed & ");"
' Is the record currently locked by someone else?
    Dim rl_details As DAO.Recordset
    Set rl_details = DB.OpenRS("SELECT locked_time, user_id FROM ui_recordlocks_tbl WHERE db_key_name='" & db_key_name & "' AND record_id='" & record_id & "' AND user_id<>'" & Usr.id & "';")
    If Not (rl_details.BOF And rl_details.EOF) Then
        Dim rl_time As Date, rl_user_id As String, rl_user_security_level As Variant
        rl_time = rl_details(0)
        rl_user_id = rl_details(1)
        rl_user_security_level = DLookup("security_level", "users_tbl", "person_fk='" & rl_user_id & "'")
        If IsNull(rl_user_security_level) Then rl_user_security_level = 0
        If rl_user_id <> Usr.id Then ' Should not block user when their own record-lock has erroneously remained in the system, lurking...
            If (rl_time + (CDbl(recordlock_duration_seconds) / 86400#) < GEO.TimeUTC) Or (Usr.security_level > rl_user_security_level) Then
                ' Recordlock has expired, or current user has higher privileges.  Ask whether to override existing recordlock.
                Dim user_reply As VbMsgBoxResult
                user_reply = MsgBox("The record you are attempting to access is being used by " & Norm.id2fullname_western(rl_user_id) & ".  Do you wish to override this person's record lock to access the record yourself?", vbYesNo, "Record locked...")
                If user_reply = vbNo Then Exit Function
                ' >>> IN [Form_index].SetMode, should check detail modes and cancel recordlocks???  Dangerous though...
                ' >>>> WHAT ABOUT HANDSHAKING - Get the other client to check whether screen form is dirty...
                ' >>>> OR BETTER...  Use a better system of record locking.  When form becomes dirty, request edit permission etc.  TABLES have been modified for this already.
            Else
                MsgBox "The record you are attempting to access is being used by " & Norm.id2fullname_western(rl_user_id), vbExclamation, "Record locked..."
                Exit Function
            End If
        End If
        DB.Execute "DELETE * FROM ui_recordlocks_tbl WHERE db_key_name='" & db_key_name & "' AND record_id='" & record_id & "';"
    End If
    If IsNull(DLookup("record_id", "ui_recordlocks_tbl", "user_id='" & Usr.id & "' AND db_key_name='" & db_key_name & "'")) Then
        DB.Execute "INSERT INTO ui_recordlocks_tbl (locked_time, user_id, db_key_name, record_id) VALUES (#" & recordlock_time_string & "#, '" & Usr.id & "', '" & db_key_name & "', '" & record_id & "');"
    Else
        DB.Execute "UPDATE ui_recordlocks_tbl SET locked_time=#" & recordlock_time_string & "#, record_id='" & record_id & "' WHERE user_id='" & Usr.id & "' AND db_key_name='" & db_key_name & "';"
    End If
    SetRecordLock = True
    Exit Function
Failed:
    MsgBox "Your attempt to access this record has failed.  " & APP.NAME_FORMAL & " does not know why.", vbExclamation, "Failed (caused by unknown fault)"
    Debug.Print "#ERROR# SQL.SetRecordLock: " & Err.Description
    Err.Clear
End Function

There might be a couple of ancillary bits missing from this code (present in my other modules) but this should give you some idea.

---

Matthew Slyman M.A. (Camb.)

Answer 8

Matthew

That's a lot more detail then I was expecting you to offer, I feel like I should've thought of something like that.

I can see that this method would introduce an additional over-head when processing, but this comes at the benefit of additional control and records of what has been going on.

Thanks for the info.