Locking set on BE or FE?

[Jonathan]

Client has requested that if one user has started editing a record, then the
record should become read only for all other users.

Is this what is known as pessimistic locking?

Where do I set the locking option? On all the Front Ends, or is it enough to
set it on the Back End?

 

[Pat Hartman \(MVP\)]

Locking is set at the form and query level so it is specified in the FE.
The default is No Locks. You can change the default to Edited Record in
Tools/Options/Advanced but this will not change any existing forms/queries.
Keep in mind that a physical record (which is what is being locked) may
include more than a single logical record so setting locks to Edited record
may in fact lock more than the current record depending on the logical
record size and the physical record size.

If you want to go back and change the lock property on all existing
forms/queries, try this - (no guarantees though. If this doesn't work,
you'll need to either do it manually or write a code routine to loop through
objects and set the lock property.
1. Create a new empty database.
2. Set the default lock property
3. Import all objects from original database.

You can try this with a single object as a proof of concept.

 

[Armen Stein]

On Fri, 15 Jun 2007 15:47:49 -0400, "Pat Hartman \(MVP\)" <please no

Locking is set at the form and query level so it is specified in the FE.
The default is No Locks. You can change the default to Edited Record in
Tools/Options/Advanced but this will not change any existing forms/queries.
Keep in mind that a physical record (which is what is being locked) may
include more than a single logical record so setting locks to Edited record
may in fact lock more than the current record depending on the logical
record size and the physical record size.

If you want to go back and change the lock property on all existing
forms/queries, try this - (no guarantees though. If this doesn't work,
you'll need to either do it manually or write a code routine to loop through
objects and set the lock property.
1. Create a new empty database.
2. Set the default lock property
3. Import all objects from original database.

You can try this with a single object as a proof of concept.

We've built hundreds of database applications and we never use
pessimistic locking. It's more trouble than it is worth in most
cases.

I wonder if your customer truly understands all the ramifications? And
how rare it is that two different users will actually try to modify
the same record at the same time? We find that it almost never
happens in real life.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Pat Hartman \(MVP\)]

Good point Armen. I should have mentioned that. I always use optimistic
locking (no locks) myself. Although, in a heads-down data entry situation
with many users adding records to the database, I wonder if there would be
locking issues due to everyone working in the same part of the file.

 

[Armen Stein]

On Tue, 19 Jun 2007 09:03:51 -0400, "Pat Hartman \(MVP\)" <please no

Good point Armen. I should have mentioned that. I always use optimistic
locking (no locks) myself. Although, in a heads-down data entry situation
with many users adding records to the database, I wonder if there would be
locking issues due to everyone working in the same part of the file.

Hi Pat,

A database internals guru could confirm this, but I believe both JET
and SQL Server allocate fresh new pages when each record is added.
This avoids "adjacent record" locking conflicts in data entry
scenarios. We've observed no issues in these kinds of applications.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com