Best Practice Row Locking - Multi User Environment (1 Viewer)

FrostByte

Registered User.
Local time
Today, 09:39
Joined
Jan 15, 2015
Messages
56
Hi All,

I currently have a small Access DB and looking for best practice settings in a multi user environment (20 users).

My current settings are...

Default Open View = Shared
Record Locking = No Locks

Other Options (which I'm unsure if relevant)...
OLE/DDS timeout = 30
Refresh Interval = 60
Number Update Retries = 2
ODBC Refresh Interval = 1500
Update retry interval = 250

DDE Operations...
Enable DDE refresh

- but from reading recently one change I should implement to is...
Record Locking = Edited Record?


Kind Regards
 

Minty

AWF VIP
Local time
Today, 09:39
Joined
Jul 26, 2013
Messages
10,353
Is this a split database ?
Does each user have a local Front end copy and your data tables are all stored in a networked back end database ?
 

FrostByte

Registered User.
Local time
Today, 09:39
Joined
Jan 15, 2015
Messages
56
Is this a split database ?
Does each user have a local Front end copy and your data tables are all stored in a networked back end database ?

Sorry yes. Backend on a NAS drive. Front end Local
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Feb 19, 2002
Messages
42,970
Using anything other than optimistic locking (no locks) can lead to bottlenecks. If you set locks to edited record, here's what happpens.

Julie opens a record and changes one field. The phone rings. She gets distracted. It's quitting time so she just locks her PC and leaves for vacation. No one will be able to update that dirty record until Julie returns. Granted, this is extreme but people walk away from dirty records all the time and the records are locked until they save the record.

If you use no locks. When Julie returns and closes the form, Access will attempt to save the dirty record but it will recognize that someone else has updated the record between the original read and the current save and will display a somewhat confusing message giving the user three options. Users should always be instructed to select cancel or whatever option doesn't save (I don't have the message in front of me).

Most applications don't have conflicts because users don't simply decide to update random records. Something prompts the update, a phone call, a piece of mail, a task from a task list. Therefore the likelihood of multiple people wanting to update the same record at the same time is small and the database engine (whichever one you are suing) does a good job of protecting the data..
 

FrostByte

Registered User.
Local time
Today, 09:39
Joined
Jan 15, 2015
Messages
56
Using anything other than optimistic locking (no locks) can lead to bottlenecks. If you set locks to edited record, here's what happpens.

Julie opens a record and changes one field. The phone rings. She gets distracted. It's quitting time so she just locks her PC and leaves for vacation. No one will be able to update that dirty record until Julie returns. Granted, this is extreme but people walk away from dirty records all the time and the records are locked until they save the record.

If you use no locks. When Julie returns and closes the form, Access will attempt to save the dirty record but it will recognize that someone else has updated the record between the original read and the current save and will display a somewhat confusing message giving the user three options. Users should always be instructed to select cancel or whatever option doesn't save (I don't have the message in front of me).

Most applications don't have conflicts because users don't simply decide to update random records. Something prompts the update, a phone call, a piece of mail, a task from a task list. Therefore the likelihood of multiple people wanting to update the same record at the same time is small and the database engine (whichever one you are suing) does a good job of protecting the data..

Thanks Pat,

What (if any) of the other settings should be considdered?


Regards
 

Ranman256

Well-known member
Local time
Today, 05:39
Joined
Apr 9, 2015
Messages
4,337
I've never used record locking,since no 2 people will ever edit the same rec at the same time.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:39
Joined
Oct 17, 2012
Messages
3,276
I have a couple applications where records are randomly pulled up and assigned to users as they work, so collisions are a distinct possibility, especially as the pool gets smaller.

Instead of using record locking, I created and use a 'check out' system instead.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Jan 23, 2006
Messages
15,361
@Frothingslosh,

Can you share the basic logic of your "check out" approach? It may be helpful to others.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:39
Joined
Oct 17, 2012
Messages
3,276
Sure.

Basically, you need to store who 'checks out' a record and when. You can do this either as part of the record being accessed, or as a separate table.

In my more recent app, our 'Case' table includes two fields: LockedByID and LockTime. They store the UserID of the person accessing the case, and when they did it. The form used to view cases only pulls a single record at a time, and the first thing it does is update these two fields, then saves the changes. When the form is closed, those fields are cleared, and the form saved. (If the user has other pending changes, those are saved too.)

The rest of the implementation is that the users first go through a lookup screen anyway, select the case they wish to view, then press a 'view case' button. The code behind it checks to see if these fields have values. If they do, the user is notified that the case is already 'checked out', and that they will not be able to access the case until it is released by the person currently using it. If they're blank, the screen is loaded and displayed for use.

A cleaner implementation would have been to keep a separate checkout table, but I didn't have the time available for the necessary approvals process.

I did have to do two other things for maintenance:
The admin menu of this application has a screen where an admin can enter a case number, check the status, and force an unlock by clearing those fields if necessary. 'Hanging' check-outs most commonly occur when my users force-close the application by right-clicking the task bar or power down without closing the app. Errors encountered can sometimes cause this, too, depending on how the trapping cleans up afterward.

There is also a nightly job on the SQL Server database that clears out all of the locks every night at 1 am.

If you want some generic code and structures, I can certainly do that this weekend, but I'll have to back engineer it, since the only examples I have right now technically belong to my employer (and honestly have a lot of application-specific stuff included as well that isn't necessary for an example).
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:39
Joined
Oct 17, 2012
Messages
3,276
Randomly assigning works much the same, except that instead of being done when a selected record is viewed, the query behind the random assignment includes criteria excluding any records showing an active checkout.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Jan 23, 2006
Messages
15,361
Thanks for sharing. I'm sure others will benefit.

I have seen similar set ups:
-assign available task to specific user based on some factor. Basically this is queuing records/tasks to specific individuals. Factor could be a company name, a province or state,or some sequence number...

-user selects an available record; record has flags set; and record is hidden or unavailable to others. That users outstanding list/to do list can be identified by the flags involved. Others can not see flagged records.

PhilS (codekabinett) has an article on queue type applications here that has a lot of related info.

Here is a link to post where user was struggling with locking records and answered by PhilS.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:39
Joined
Sep 12, 2006
Messages
15,613
I agree with Pat. Optimistic locking is strictly "no locking", but in practice not quite no locking. Access has a different strategy to check whether the record has been changed before allowing you to write your change. If it has changed you get the "another user changed your data" message. More often than not, the "another user" is yourself, because of a programming error. Basically editing the same record twice in two different forms.

Optimistic locking tends to avoid the inconsistent updates and potential deadlock Pat referred to above. I imagine you could still get into problems with optimistic locking if you ever got to a situation where multiple users had each edited records required by other users, and effectively you both become stymied, although I haven't seen it.

It's a lot easier to let Access do it for you, then try to write the complex transaction handling to recover from a "deadlock".

Research "Deadlock" among other things. Wikipedia has a lot of info on IT theory.
 

Users who are viewing this thread

Top Bottom