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

[RayPower]

I'm having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.

Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset

Set wkSpace = DBEngine.Workspaces(0)
Set db = CurrentDb()

wkSpace.BeginTrans

'some code...

Set rstTrans = db.OpenRecordset("tblTrans", dbOpenDynaset,
dbAppendOnly, dbOptimistic)
With rstTrans
.AddNew
'set field values...
.Update
.Close
End With

'codes updating other tables

wkSpace.CommitTrans

The process is fine if the tables are local tables. But if the tables
are linked tables, it locks the entire tblTrans table once it execute
the Update method. No other user can update or append the tblTrans
table at all (with "Could not update; currently locked." error)

I'm sure that nothing else lock the table as I did run the code (just
the recordset append code) in the frontend and backend.

It seems the lock apply properly only to local tables?

[Allen Browne]

I imagine that the lock would be due to the transaction.

See if it makes any difference if you omit the line:
wkSpace.BeginTrans

--
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.

"RayPower" <ra********@yahoo.com.au> wrote in message
news:11********************@u72g2000cwu.googlegrou ps.com...

I'm having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.

Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset

Set wkSpace = DBEngine.Workspaces(0)
Set db = CurrentDb()

wkSpace.BeginTrans

'some code...

Set rstTrans = db.OpenRecordset("tblTrans", dbOpenDynaset,
dbAppendOnly, dbOptimistic)
With rstTrans
.AddNew
'set field values...
.Update
.Close
End With

'codes updating other tables

wkSpace.CommitTrans

The process is fine if the tables are local tables. But if the tables
are linked tables, it locks the entire tblTrans table once it execute
the Update method. No other user can update or append the tblTrans
table at all (with "Could not update; currently locked." error)

I'm sure that nothing else lock the table as I did run the code (just
the recordset append code) in the frontend and backend.

It seems the lock apply properly only to local tables?

[RayPower]

Hi Allen,

Thanks for your prompt reply.

Yes, I know the lock is due to the transaction. But I need to use
transaction as all updates should rollback if anything goes wrong.

The problem is whether the tables are link tables or local tables. With
local tables, the tblTrans table will not be locked. But with link
table, the entire tblTrans table just being locked even though I'm only
appending one record in that table.

Is that something wrong with JET or just Access 2000?

[RayPower]

Did anyone encounter this problem before?

[Laurie]

Have you tried opening just a single record instead of the entire
table?

strSQL = "SELECT TOP 1 * FROM tblTrans;"
Set rstTrans = db.OpenRecordset(strSQL, dbOpenDynaset,
dbAppendOnly, dbOptimistic)

RayPower wrote:

Did anyone encounter this problem before?

[RayPower]

I tried that but it still lock the entire table. I even tried to open
an empty recordset with "SELECT * FROM tblTrans WHERE TRUE=FALSE" but
with no luck.

The DAO append method locks the entire table if the target table is a
linked table (no matter the back end is located in the same PC or on
the Windows server).

Laurie wrote:

Have you tried opening just a single record instead of the entire
table?

strSQL = "SELECT TOP 1 * FROM tblTrans;"
Set rstTrans = db.OpenRecordset(strSQL, dbOpenDynaset,
dbAppendOnly, dbOptimistic)

RayPower wrote:

Did anyone encounter this problem before?

[Lyle Fairfield]

RayPower wrote:

The DAO append method locks the entire table if the target table is a
linked table (no matter the back end is located in the same PC or on
the Windows server).

DAO's Recordset Locking seems very complex. I suppose one could wade
through the various switches and optional parameters until one got what
one wanted, or realized that one could not get what one wanted.

Are you sure the entire Table is locked as opposed to just the 2K page
where the record lives?

Have you considered using ADO?

Have you considered Updating the Record with an SQL UPDATE call (using
DAO or ADO) and not opening a Recordset at all, or opening it only long
enough to get values as the basis for your Edit? I believe this is
almost always the most efficient way tp Update Data.

IMO Recordsets should be opened only as a last resort; although I have
opened Recordsets [as in Declaring a Variable/Pointer and "Setting" it]
in the past, I have not done so for a very long time, and plan to
continue that practice.

[David W. Fenton]

"RayPower" <ra********@yahoo.com.au> wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:

The DAO append method locks the entire table if the target table
is a linked table (no matter the back end is located in the same
PC or on the Windows server).

This was true with Jet 2.x, but is not true with Jet 3.5 and beyond.

Are you using dbAppendOnly?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[RayPower]

I know the transaction works find if I use append queries & the table
will not be locked. Unfortunately I need to use the recordset as I need
to process updates on various tables one record at a time (with
different conditions etc).

I'm using JET 4 & tried using different parameters in the OpenRecordset
method including dbAppendOnly.

In the testing of locking entire table, there is interesting thing. I
run the program from the front-end with the breakpoint set at the
AddNew method & step through the code. I also open the tblTrans table
in te back-end & test if the table is locked. Before hitting the Update
method, I can still update records in the tblTrans table in the
back-end datasheet. But once the Update method was executed, I cannot
update records except those I updated before the Updated method was
executed. I also tried not to update any record in the tblTrans table
in the back-end, the entire table was locked from update.

[Lyle Fairfield]

RayPower wrote:

Unfortunately I need to use the recordset as I need
to process updates on various tables one record at a time (with
different conditions etc).

And ... why can't you do this executing SQL statements?

[RayPower]

Hi Lyle,

Thanks for your prompt reply. The only reason is I try to get the
transaction ID which is an autonumber. With using the recordset, the
AddNew method would create a new record and the new auto transaction ID
is generated that I can retrieve it right away. The transaction ID then
used as parameter in other functions.

But you are right, I can use SQL statements if the DAO recordset cannot
be used in appending new records to linked tables within a transaction.
Thanks.

Regards,
Ray

Lyle Fairfield wrote:

RayPower wrote:

Unfortunately I need to use the recordset as I need
to process updates on various tables one record at a time (with
different conditions etc).

And ... why can't you do this executing SQL statements?

[Lyle Fairfield]

RayPower wrote:

But you are right, I can use SQL statements if the DAO recordset cannot
be used in appending new records to linked tables within a transaction.

I don't know whether or not this will be helpful but .... by using an
ADO connection you can use something like this to get the last id:

Sub temp()
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
..ConnectionString = CurrentProject.BaseConnectionString
..Open
..Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
..Close
End With
Set c = Nothing
End Sub

The ADODB connection TTBOMK should be one we are sure no one else is
using or can use, which I why I declare a distunct variable pointer to
it.

ADODB connections have methods:
BeginTrans
RollbackTrans
and
CommitTrans
as well.

Circling right around ... maybe we wouldn't get the lock if we used
ADODB recordsets? Naah ... I don't think we want to go there/

[RayPower]

Unfortunately using the SQL INSERT statement also locks the entire
table..

I tried DAO, ADO, recordset & SQL statements but still locks the table!

Is it something to do with JET & linked tables? I'm testing the code
with both front-end & back-end on the same PC.

[David W. Fenton]

"RayPower" <ra********@yahoo.com.au> wrote in
news:11**********************@75g2000cwc.googlegro ups.com:

I tried DAO, ADO, recordset & SQL statements but still locks the
table!

Did you try executing a SQL APPEND with ADO? I don't use ADO, but I
believe it supports retrieving the last-added "identity" value even
with Jet. But I could be mistaken on that, as I don't use ADO
myself.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[RayPower]

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?

[Lyle Fairfield]

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 *****

[dreadnought8]

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 *****

[david epsom dot com dot au]

If it is in a transaction, I don't need to do multiple
AddNew -Update. Because it is in a transaction, I can
add all of the records with one append query, and then
deal with them one at a time or as a group.

Inside a transaction, I sometimes look for the last autonumber
value, so that i can insert it into other tables. But
if I have multiple records, I use another identifying field
so that I can find the correct record and still use a bulk
update query.

For example, I may have a 'pin' field and an 'idxRun' field
so that I can find item 3 on run 457 for record 23457.
Then I can do a bulk update or append using the correct
autonumber id for each record.

I don't have any difficulty appending multiple times to a
table inside a transaction, using a dao append query.

Using the methods mentioned I don't have to use a recordset
to find the last affected record: my next dao query inside
the transaction just uses the pin, run and record id fields
to find the correct record for sourcing the autonumber
value just created.

(david)

"RayPower" <ra********@yahoo.com.au> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

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?

[Lyle Fairfield]

david epsom dot com dot au wrote:

If it is in a transaction, I don't need to do multiple
AddNew -Update. Because it is in a transaction, I can
add all of the records with one append query, and then
deal with them one at a time or as a group.

Inside a transaction, I sometimes look for the last autonumber
value, so that i can insert it into other tables. But
if I have multiple records, I use another identifying field
so that I can find the correct record and still use a bulk
update query.

For example, I may have a 'pin' field and an 'idxRun' field
so that I can find item 3 on run 457 for record 23457.
Then I can do a bulk update or append using the correct
autonumber id for each record.

I don't have any difficulty appending multiple times to a
table inside a transaction, using a dao append query.

Using the methods mentioned I don't have to use a recordset
to find the last affected record: my next dao query inside
the transaction just uses the pin, run and record id fields
to find the correct record for sourcing the autonumber
value just created.

(david)

I have spent my spare time today fooling with this problem.

I think Ray wants to accomplish this:

1. begin a transaction;
2. change Nancy's name in a linked employees table to Marilyn in
whatever way;
3. go for a cup of coffee without committing or rolling back the
transaction;
4. While he's having his coffee he wants another user to be able to
change Jennifer's name to Esmeralda.

But once Ray changes Nancy's name in a linked table within a
Transaction, it seems that Jet locks the ENTIRE table, not just
Nancy's row but all the rows, not just the page where Nancy's
record resides but all the pages. Now only Ray, from within his
transaction can change anything in that Employee's table,
UNTIL the Transaction is Committed or Rolled Back.

Argggggggghhhhhhh. This seems less than perfect to me. I agree that
Nancy's record should be locked until the Transaction is ended. But
why Jennifer's?

I tried ADO connections. I tried ADO recordsets saved as XML and then
updated from the original. I tried linking the Northwind.mdb to my
local SQL Server and running ADO Transactions.

I was unsuccessful everywhere. I learned that the JET OLEDB provider
works just like JET (Big Surprise, eh?).

I really hate it when I think there should be a way ... but I can't
find a way. No, I've never had any practical use for this, and doubt
if I ever will.

But I want to know!

If someone knows a way, please, tell me ... gently.

(David ... if your post has a way for others to change data in the
table, please, explain again ... slowly ....)

[Lyle Fairfield]

Did I mention that I tried NOT linking, just beginning the transaction
and doing an UPDATE query with the database and table specified in the
SQL string .... same luck ... same lock!

[david epsom dot com dot au]

> (David ... if your post has a way for others to change data in the

table, please, explain again ... slowly ....)
No, my way is to avoid using .AddNew ... .Update
so I don't get the locking described.

I don't have to use .AddNew ... .Update to find the
last Autonumber value, because I have (effectively)
a natural primary key that allows me to find the last
Autonumber value.

Also, because I can find the last Autonumber using
what is (effectively) a natural primary key, I can
do bulk updates and update all of the records in one
pass.

I don't think Ray actually wants some one else to add
new records to a table while he has it locked: it seems
to me that his actual problem is a code loop that tries
to add records after he has locked the table.

If he changes his methods, he can avoid locking the
table, so his code loop will work. But if he changes
his methods, he can probably avoid the whole code loop
thing.

(david)
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@m73g2000cwd.googlegro ups.com... david epsom dot com dot au wrote:

If it is in a transaction, I don't need to do multiple
AddNew -Update. Because it is in a transaction, I can
add all of the records with one append query, and then
deal with them one at a time or as a group.

Inside a transaction, I sometimes look for the last autonumber
value, so that i can insert it into other tables. But
if I have multiple records, I use another identifying field
so that I can find the correct record and still use a bulk
update query.

For example, I may have a 'pin' field and an 'idxRun' field
so that I can find item 3 on run 457 for record 23457.
Then I can do a bulk update or append using the correct
autonumber id for each record.

I don't have any difficulty appending multiple times to a
table inside a transaction, using a dao append query.

Using the methods mentioned I don't have to use a recordset
to find the last affected record: my next dao query inside
the transaction just uses the pin, run and record id fields
to find the correct record for sourcing the autonumber
value just created.

(david)

I have spent my spare time today fooling with this problem.

I think Ray wants to accomplish this:

1. begin a transaction;
2. change Nancy's name in a linked employees table to Marilyn in
whatever way;
3. go for a cup of coffee without committing or rolling back the
transaction;
4. While he's having his coffee he wants another user to be able to
change Jennifer's name to Esmeralda.

But once Ray changes Nancy's name in a linked table within a
Transaction, it seems that Jet locks the ENTIRE table, not just
Nancy's row but all the rows, not just the page where Nancy's
record resides but all the pages. Now only Ray, from within his
transaction can change anything in that Employee's table,
UNTIL the Transaction is Committed or Rolled Back.

Argggggggghhhhhhh. This seems less than perfect to me. I agree that
Nancy's record should be locked until the Transaction is ended. But
why Jennifer's?

I tried ADO connections. I tried ADO recordsets saved as XML and then
updated from the original. I tried linking the Northwind.mdb to my
local SQL Server and running ADO Transactions.

I was unsuccessful everywhere. I learned that the JET OLEDB provider
works just like JET (Big Surprise, eh?).

I really hate it when I think there should be a way ... but I can't
find a way. No, I've never had any practical use for this, and doubt
if I ever will.

But I want to know!

If someone knows a way, please, tell me ... gently.

(David ... if your post has a way for others to change data in the
table, please, explain again ... slowly ....)

[RayPower]

Hi Lyle & David,

Thanks for helping me, but the locking problem only happens when adding
a new record in the table inside a BEGINTRANS, COMMITTRANS block.

The record level locking is fine when updating records. But when I try
to add new record using AddNew method of DAO/ADO or just execute an
INSERT statement, the table is locked from allowing other users to add
record to the same table. The lock only released after the whole
transaction is committed or rollback.

Also, in my testing database, the lock only promotes to page lock. But
in the live database, the table with 1.5 million records actually
promoted to table lock. The action on the table was just adding one
single record (even using a simple insert statement).

At the moment the situation is not too serious as the transaction takes
around 2 seconds to commit. But the management has decided to outsource
some process and the other company will send back the batch of records
in the morning, imported into database, add/update different tables
accordingly. If the import/update database process takes 15 minutes,
I'm worry users cannot create new records during the period.

BTW, how can I work out the size of a table. I think Access 2000 has a
size limit of a table under 1GB?

Regards,
Ray

[david epsom dot com dot au]

Yes, I see that you are talking about transactions. So
am I.

Access 2000 has a database limit of 2GB. Any table
can take up a whole database, 2GB. According to reports
here, Access may fail if you try to exceed that size:
you should stay well within the limit.

I think that the process you have designed for importing
records is not appropriate, even if you could get it
to work.

Also I wonder why users need to add records to the
database in real-time if you have a separate off-line
process that adds records. Maybe you should have
separate transactional and warehousing databases.

(david)

"RayPower" <ra********@yahoo.com.au> wrote in message
news:11**********************@h44g2000cwa.googlegr oups.com...

Hi Lyle & David,

Thanks for helping me, but the locking problem only happens when adding
a new record in the table inside a BEGINTRANS, COMMITTRANS block.

The record level locking is fine when updating records. But when I try
to add new record using AddNew method of DAO/ADO or just execute an
INSERT statement, the table is locked from allowing other users to add
record to the same table. The lock only released after the whole
transaction is committed or rollback.

Also, in my testing database, the lock only promotes to page lock. But
in the live database, the table with 1.5 million records actually
promoted to table lock. The action on the table was just adding one
single record (even using a simple insert statement).

At the moment the situation is not too serious as the transaction takes
around 2 seconds to commit. But the management has decided to outsource
some process and the other company will send back the batch of records
in the morning, imported into database, add/update different tables
accordingly. If the import/update database process takes 15 minutes,
I'm worry users cannot create new records during the period.

BTW, how can I work out the size of a table. I think Access 2000 has a
size limit of a table under 1GB?

Regards,
Ray