I'm having issues with 3.30 transaction lock time outs, and I've made a change to this constant from the dbisamcn.pas file.  I changed it from

MAX_HEADER_LOCK_RETRIES = (High(SmallInt)-1)

to

MAX_HEADER_LOCK_RETRIES = 5

It seems to work great in testing.  If a user attempts to start a transaction, and a transaction is already in process, it times out quickly so they can continue doing other things or try again.  

Are there any adverse effects from setting this retry count so low?

TIA,

John

John Easley wrote:

> I've made a change to this constant

A quick search shows that the constant is used only to set the default for a property on the engine. Therefore the smart thing to do would be to change it back, and then set it in the startup code for your application. Perhaps making it configurable, so that if it does cause problems, you can change it.

As for whether it is a bad thing or not, I guess depends on whether you want the database to "just make it work and wait until it can get the lock" or give up and let you know things are busy. I think I'd look at how long between retries too.

--

Matthew Jones

"Matthew Jones" wrote:

>>>As for whether it is a bad thing or not, I guess depends on whether you want the database to "just make it work and wait until it can get the lock" or give up and let you know things are busy. I think I'd look at how long between retries too.

Currently,  "make it work" causes the app to appear to hang and users kill the app. I don't understand why the default value is to try 32,000+ time before timing out.. I suppose if there is a dangling lock on the server, and users  time out quickly, then the app probably isn't going to work anyways as files are locked..

John

John Easley wrote:

> Currently,  "make it work" causes the app to appear to hang and users kill the app.

That suggests a lock is stuck somewhere, presumably with a transaction that started but not completed/rolled back. Check your try/finally blocks to ensure it is all good. That's worth digging into, as while it might come back, the core problem is still there stopping things happen. Me, I'd be logging it to death right now. Good luck!

--

Matthew Jones

John,

<< I'm having issues with 3.30 transaction lock time outs, and I've made a change to this constant from the dbisamcn.pas file.  I changed it from

MAX_HEADER_LOCK_RETRIES = (High(SmallInt)-1)

to

MAX_HEADER_LOCK_RETRIES = 5

It seems to work great in testing.  If a user attempts to start a transaction, and a transaction is already in process, it times out quickly so they can continue doing other things or try again.  

Are there any adverse effects from setting this retry count so low? >>

Yes, don't do that.  Such locks are intended to always wait until they can succeed, even in the face of heavy load.  Setting it that low will cause exceptions all over the place in your code when Post operations fail because they were only set to wait for a few milliseconds and contention is higher than expected.

If you have transactions that are blocking other transactions, then the problem is what Matthew indicated: you've got transactions that are being started and not being committed/rolled back, or are taking too long to accomplish what they need to within the confines of the transaction.


TIA,

John

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

>>Yes, don't do that.  Such locks are intended to always wait until they can succeed, even in the face of heavy load.  Setting it that low will cause exceptions all over the place in your code when Post operations fail because they were only set to wait for a few milliseconds and contention is higher than expected.

>>If you have transactions that are blocking other transactions, then the problem is what Matthew indicated: you've got transactions that are being started and not being committed/rolled back, or are taking too long to accomplish what they need to within the confines of the transaction.

I was under the impression that when a transaction is started, tables are locked.. and if they can't be locked, StartTransaction fails. Are you saying that tables are locked on demand as needed as part of the transaction?  If that's the case, then the number of retries might make sense, but if not, then I still don't understand why the default setting is so high.  If you could please explain the necessity of a value of (High(SmallInt)-1) , where did this number come from and why?  Why not 500 or 1,000? etc.

TIA,

John

Bump

John Easley wrote:

Tim Young [Elevate Software] wrote:

>>Yes, don't do that.  Such locks are intended to always wait until they can succeed, even in the face of heavy load.  Setting it that low will cause exceptions all over the place in your code when Post operations fail because they were only set to wait for a few milliseconds and contention is higher than expected.

>>If you have transactions that are blocking other transactions, then the problem is what Matthew indicated: you've got transactions that are being started and not being committed/rolled back, or are taking too long to accomplish what they need to within the confines of the transaction.

I was under the impression that when a transaction is started, tables are locked.. and if they can't be locked, StartTransaction fails. Are you saying that tables are locked on demand as needed as part of the transaction?  If that's the case, then the number of retries might make sense, but if not, then I still don't understand why the default setting is so high.  If you could please explain the necessity of a value of (High(SmallInt)-1) , where did this number come from and why?  Why not 500 or 1,000? etc.

TIA,

John

John,

Sorry for the delay in responding - I'm getting several releases ready this week and I'm a little swamped with documentation.

<< I was under the impression that when a transaction is started, tables are locked.. and if they can't be locked, StartTransaction fails. Are you saying that tables are locked on demand as needed as part of the transaction? >>

No, you are correct - they are locked at the start of the transaction.

<< If that's the case, then the number of retries might make sense, but if not, then I still don't understand why the default setting is so high.  If you could please explain the necessity of a value of (High(SmallInt)-1) , where did this number come from and why?  Why not 500 or 1,000? etc. >>

This number isn't the number that you think it is. It's not *just* for transactions, and in DBISAM 3.x is used for *both* transactions and write locks.  This means that DBISAM 3.x also uses this constant for write locks on the table during normal appends, updates, and deletes.  If the value was only 500, then during heavy contention it could easily be exceeded in a few hundred milliseconds (500 * DEFAULT_HEADER_LOCK_WAIT = 1500 msecs), meaning that you would see constant write lock exceptions in your application.  The High(SmallInt) is effectively saying "wait a really long time before bailing out and raising an exception", where a "really long time" is 32768 * DEFAULT_HEADER_LOCK_WAIT = ~ 1.5 mins.

DBISAM 4.x changed this so that it's more granular and configurable in the TDBISAMEngine component, and EDB went even further, offering the ability to specify the transaction timeout in the StartTransaction call itself.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the information.

I've added a lot of logging to my app, and it's created even more mystery.  I log before and after a call to StartTransaction, and the problem is, when a user locks, I see the call before.. but it doesn't offer any insight as to what is causing the locks.  My hunch is that I'm getting a table lock as part of a large SQL Update somewhere, which then creates a failure to StartTransaction.

As far as SQL UPDATE is concerned, would making this use an Optimistic session help? Or, does UPDATE still require full blown table locks?

I'm thinking I need to log absolutely every SQL INSERT, UPDATE, DELETE within my app to track this down, and that's no easy task, as this projects is huge.

John

John,

<< As far as SQL UPDATE is concerned, would making this use an Optimistic session help? Or, does UPDATE still require full blown table locks? >>

Yes, an SQL UPDATE in DBISAM 3.x will use a transaction, if one is not already in progress.  So, you will want to keep the execution time of such statements to a minimum.

Tim Young
Elevate Software
www.elevatesoft.com