Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

ACCESS 2003 record locking vs page frame locking

Discussion in 'Information Technology' started by simcon, Jul 28, 2006.

  1. simcon

    simcon
    Expand Collapse
    Guest

    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?
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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
    Reply to group, rather than allenbrowne at mvps dot org.

    "simcon" <simcon@discussions.microsoft.com> 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. simcon

    simcon
    Expand Collapse
    Guest

    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.

    "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
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "simcon" <simcon@discussions.microsoft.com> 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?

    >
    >
    >
     
  5. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    "simcon" <simcon@discussions.microsoft.com> 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
    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...

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

Share This Page