Re: DAO recordset append single record locks entire table (linked table)

With such an impasse, have you thought of just working around the
problem, ie trapping locking errors and automatically retrying an
arbitrary number of times?
This worked very well for me with complex transactions in an order
entry application I developed; if there was a locking error, I'd flash
up a message saying "please wait - retrying commit" or something and
retry 5 times with 2 second delays.
The more sophisticated approach is to increase the delay each time.
Downside is programming overhead ...
Terry Bell

Lyle Fairfield wrote:
RayPower wrote:
I have tried the locking problem by creating a testing front-end &
back-end .mdb files.

I found that Access 2000 keeps doing page lock whenever a new record is
added into the table inside a transaction. No matter if I use DAO, ADO
or just execute an INSERT statement, the table was locked from
appending any new record from other sessions until the transaction is
commited or rollback.

I have tried various settings for the default locking with the "record
level locking" selected. I can see the record lock indicator (with
"Edited record" as default locking) showing the last 76 records (out of
50000 records in the testing) were locked once I append the new record
in a transaction.

The record locking for updating a record is fine, but appending new
records locks the page.

Is there anyway I can use transaction but allow users create new
records in the same table concurrently?

I can't find one. I suppose this problem is based on this description
of locking in Jet 4.0:

***** begin quote *****
Lock promotion
When a SQL statement is executed or when a transaction is executed that
modifies a large number of records in a table, Write locks will be
placed on all corresponding index and pages in the database. Although
the placing of discrete locks maximizes concurrency, it can
significantly decrease performance because of the overhead involved in
setting and maintaining the locks. This is particularly true when the
database is on a server and is being accessed over a local area
network.

Capability has been added that permits a user to open a table
exclusively and modify records in a table without locks being placed on
either corresponding index or pages. This reduces concurrency (only one
user is being permitted to update the table), but will increase the
performance where large numbers of records are being modified. As an
option to requiring a user to programmatically request exclusive access
to a table for performing updates, Microsoft Jet will, when large
numbers of page locks are being placed on a table, attempt to promote
the page locks to an exclusive table lock. Whether or not this
capability is turned on is controlled by a registry entry,
PagesLockedToTableLock. The default value for the registry entry is 0,
which disables the capability. A value greater than 0 specifies the
page lock count at which promotion to an exclusive table lock should be
attempted. For example, if the PagesLockedToTableLock entry is set to a
value of 50, then on the 51st page lock, Microsoft Jet will try to
promote the user's shared table read locks to an exclusive table lock.
If the attempted promotion is unsuccessful, it will retry on the 101st
page lock, and so on.
***** end quote *****

.

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: [RFC] fsblock
    ... the transaction code, without taking the page lock. ... The best long term option may be making the locking order ... and change writepage to punt to some other ...
    (Linux-Kernel)
  • Re: using clustered index to optimize inserts ...
    ... I will try to explain locking in terms of Sybase docs... ... Allpages Locking: Allpages locking locks both data pages and index ... the data page is locked with an exclusive lock. ... Clustered Index: The datarows will be arranged as per the clustered ...
    (comp.databases.sybase)
  • Re: CSingleLock - known behaviour?
    ... It is better to design code that doesn't require locking. ... If you don't need the resource, don't lock it. ... magnitude less efficient, than locking once. ...
    (microsoft.public.vc.mfc)
  • Re: parallel interpreter/db problem. transaction didnt fix.
    ... I had *thought* a transaction would fix that, ... I've never used table locking in PHP ... error on your queries - and not checking for them. ... so I changed the LOCK TABLES to a write lock. ...
    (comp.lang.php)
Loading