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)

 

[Pat Hartman]

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

 

[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?

 

[Pat Hartman]

Transactions are useful for large batch processes that may require a rollback. Batch processes are not generally run during primetime when users are interacting with the database.

From JET 4.0, Row level locks will be automatically promoted to page or table level when the number of rows locked reaches a threshold

You didn't specify whether you are using Jet or ACE. There isn't much written about ACE so I don't know if it works the same way or not. There is an excellent book which is long out of print but copies can be found for $5.00 that actually still include the CD. There were two versions. I think the latest version was for Jet 3.6. Look for the Microsoft Access Jet Programmer's Guide. It is full of technical details on how Jet works. It would be wonderful if MS would reissue an ACE version.

Here's some links that may help -
http://msdn.microsoft.com/en-us/library/windows/desktop/ms681754(v=vs.85).aspx
http://en.wikibooks.org/wiki/JET_Database/Data_integrity
http://support.microsoft.com/kb/815281
http://office.microsoft.com/en-us/a...t-4-0-database-engine-driver-HP001032161.aspx
http://msdn.microsoft.com/en-us/library/aa140022(v=office.10).aspx

 

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

 

[Pat Hartman]

Ions,
I sent all those links because some of them describe settings that you can control. As you see by the quote I posted, the locks don't start out as table locks but they get promoted when a certain threshold is reached. You can probably control that.

 

[ions]

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