ACCESS 2003 record locking vs page frame locking

#1 (**permalink**) 28-Jul-2006, 08:29 AM

which is the better locking option to use?
are there occassions when you would use one in preference over the other?
do both locking methods work equally well?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12462-access-2003-record-locking-vs-page.html

*

#2 (**permalink**) 28-Jul-2006, 08:29 AM

Advantage of Record-level locking
- Useful where many users are editing at once.

Disadvantage
- Slower performance, and probably less tested.

The only serious problem I have experienced was with a JET 4 front end
connected to an Access 97 back end. If record-level locking was enabled,
some fairly involved action queries executing inside a transaction failed to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12462
run to completion. Disabling record-level locking in the front end solved
the problem. Since Access 97 did not have record-level locking, the front
ends should have just ignored the setting. We experienced this in Access
2000 and in 2002.

In general, therefore I suggest you use page-level locking unless you have a
need for record-level.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12462
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"simcon" wrote in message
news:A93E38DF-CC30-49A6-81FF-D90BEF7547D3@microsoft.com...
> which is the better locking option to use?
> are there occassions when you would use one in preference over the other?
> do both locking methods work equally well?

#3 (**permalink**) 28-Jul-2006, 08:29 AM

Thanks Allen. My only issue with Page Frame locking is that it can lock many
other records that you don't want to lock, depending on record size.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12462

"Allen Browne" wrote:

> Advantage of Record-level locking
> - Useful where many users are editing at once.
>
> Disadvantage
> - Slower performance, and probably less tested.
>
> The only serious problem I have experienced was with a JET 4 front end
> connected to an Access 97 back end. If record-level locking was enabled,
> some fairly involved action queries executing inside a transaction failed to
> run to completion. Disabling record-level locking in the front end solved
> the problem. Since Access 97 did not have record-level locking, the front
> ends should have just ignored the setting. We experienced this in Access
> 2000 and in 2002.
>
> In general, therefore I suggest you use page-level locking unless you have a
> need for record-level.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12462
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "simcon" wrote in message
> news:A93E38DF-CC30-49A6-81FF-D90BEF7547D3@microsoft.com...
> > which is the better locking option to use?
> > are there occassions when you would use one in preference over the other?
> > do both locking methods work equally well?
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:29 AM

"simcon" wrote in message
news:A93E38DF-CC30-49A6-81FF-D90BEF7547D3@microsoft.com...
> which is the better locking option to use?
> are there occassions when you would use one in preference over the other?
> do both locking methods work equally well?

If you have a choice?, then you should AVOID record locking, but use page
locking.

The reason is that of file bloat. Record locking works by padding the
records to fill up a frame/page. So, in effect, it is a "fake" way of
achieving record locking, and the penalty is considerably MORE file bloat.

Also, remember that usually only the MAIN table needs locking. So, for
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12462
example, if I have a customer table, and a table of invoices, you likely
have that classing setup with a customer form, and a sub-form for details.
You do NOT need any locking on the details table since you can ONLY get to
that data through the main parent table/form. So, use caution with locking
anyway.

So, if your application can function fine with page locking, then that
should be your choice. Use record/row locking with caution, as it is source
of bloat in a application.

however, the feature is there for you use. If you need it, then use it. We
have to deal with file bloat, and compacting the file on a regular bases is
a requirement for any application.

Just keep in mind there is a penalty for using row locking...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12462

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal