Record Locking 1

[lameid]

I am having a hard time finding specific details on record locking and I have not had to do a deep dive into all the nuances before.

Below is what I think and have questions about. Hoping others can confirm, correct and complete. Many thanks.

In Access 2010...
File, Client Settings, Advanced section there are several settings...

Default Open Mode - Clearly for multi-user this has to be shared - mentioned for completeness

Default Record locking option group (No Locks, All Records, Edited Records) - The options are well defined and the internet/help but what does it really do? It seems to set the RecordLocks property on supporting objects when created. Is that it or does it do something else? I guess the point being is I don't think this is a global setting like Default Open Mode it is rather the default behavior for new things.

Open databases by using record-level locking - I believe this is a global setting for Access.... However I think it behaves differently for MDB's and ACCDB's. For MDB's I think it fills the record to the page size (2K for MDB?) and for ACCDB's I think it works without bloating?

 

[CharlieT302]

Locking can be done on a per object basis; meaning each form or query can have its own setting.

No Locks - Optimistic Record Locking; all users can access and edit the same record simultaneously. When saving the record, the first person to save does so without incident. Subsequent users (who were editing the record during the same session) will receive a message stating that the record has just been saved. They will be given the choice of cancelling, saving their changes to the clipboard, or overwriting the record with their changes.

All Records - No one else can edit any record in the dataset until the data set is closed by the person accessing it originally.

Edited Record - Pessimistic Record Locking; Only the current record is locked. Other records in the dataset may be altered by other users. The locked record is released (unlocked) once the person who first accessed it closes the dataset (form, query) or moves to another record.

Note: that Edited Record actually locks a "page" of data rather than an actual record. In prior versions of Access, this amounted to 2k of data. Currently, it locks 4K. This means that you may find that, in reality, two or more records are actually locked. How many depends on the size of each record.

Access provides a workaround of sorts to ensure that only a single record is locked. Open the File tab and click Options. In the Object Pane, choose the option: Client Settings. In the list of options, check the box: Open databases by using record level locking.

This option, when necessary, artificially bloats a record to use the entire 4K. The result is that a single record is locked rather than a page of records. The downside is also that same bloat. The database can be greatly enlarged. You just have to test. Unless you have a very large database to begin with, this may not be an issue.

 

[lameid]

Interesting on Record locking forcing a Page per record I had hoped they fixed that with some sort of workaround in newer versions.

Locking can be done on a per object basis; meaning each form or query can have its own setting.

Right, the Record locks property on those objects which seems to default from Default Record locking. My remaining question is does that Default Record locking setting impact anything else? I'm guessing it must control the behavior of tables when used directly (Not that I would make that part of a design) and may impact Recordsets but I have not gotten around to testing just yet. I also thought I may be overlooking something entirely.

Record locking instead of page locking is a concern as a table does have small records but a lot of them. On at least MDB's, the bloat seems to be remedied by compact up until the records are used again, I would expect the same of ACCDB's. That is at least one mitigation but from a practical side I may lean towards optimistic locking and page locks for that scenario.

 

[CharlieT302]

Actually, my understanding is that Record Locks apply only to forms and queries and not tables. In reality, I cannot envision allowing users to have direct access to tables anyway. The Client Settings simply defines the option for all forms and queries that may be created; sort of a "Global" setting.

I am not aware of other impacts of the default settings; perhaps someone else here has an opinion. The main impact is the results of bloat. I believe that the option: Open databases by using record level locking, is on by default. So an expanding database might be an issue. However, you would probably need a very large dataset to encounter an issue. Compacting a database does help.

 

[lameid]

Access 2010:

In testing I found that tables always behave with optimistic locking.

I also found that the default lockedits property on a DAO recordset is always true... I didn't test ADO because I don't care (DAO is the faster way to use Access Data) but I assume there is an object default independent of the application setting like DAO.

Sub Test()
    Dim rst As DAO.Recordset
    Set rst = fnThisDB.OpenRecordset("tbl_skin")
    
    MsgBox "Default Lock edits: " & rst.LockEdits

    rst.Close
    Set rst = Nothing
End Sub