Table Locking when using .mdb transactions

[ions]

Dear Access Expert.

Using a .mdb file as the backend and DAO:

When the execution is inside a .BeginTrans ... .CommitTrans / .Rollback block does any table opened in that block become locked for editing in a multi-user environment?

I am getting entire table locking issues and I believe it's related to Transactions.

Thank you.

 

[joeKra]

try to open table \ query Dynaset like
currentdb.openrecordset("table \ QueryName ",dbopenDynaset)

 

[ions]

Yes. If you open it inside the transaction, Access thinks you may want to update it and so locks it.

I am a little shocked and just want to get a confirmation.

Pat so if I open a table within a transaction the entire table, not just the record being edited (or record block surrounding the record) is locked?

If the entire table is locked then this severely limits the use of transactions in a multi-user environment using .mdb.

Why don't .mdb transactions do record level locking?

 

[ions]

Thank you for the response and information Pat.

I am disappointed. I thought transactions were record level locking or perhaps page locking but not table locking.

I am using a .mdb / Jet backend and I assume ACE works the same way. Are transactions table locking in MS SQL Server as well?

One instance, where I am using transactions is when the user wants to copy a service.

I use a transaction because each service has many related records in other supporting tables such as pricing table, documents table, scheduling preferences table etc...

If any of the related records were not copied successfully I rolled back the copy / transaction and informed the user the process was unsuccessful.

I guess what I will have to do now is delete all the newly created records myself if there is an error along the way.

Also I will refrain from using Transactions for processes that occur during regular working hours as users are experiencing locking issues.

Thank you again for your help Pat.

 

[TexanInParis]

Dear Ions-

It's tough to see what might be causing a full table lockout without seeing your code. You would normally wrap a transaction around updates to rows in multiple tables to ensure they either all commit or none commit. Such "batches" of updates should be well-defined and compact. In other words, they shouldn't take more than a few milliseconds (if not microseconds). Even if you've done something to lock out the entire table within the transaction, it should have little to no impact unless you're updating thousands of rows.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[ions]

Thank you for your response TexanInParis.

 

[ions]

Pat yes I read the links. They were very informative. Thank you for all the info.