Hi,

referring to Database Transaction locking (ie. StartTransaction, Commit, Rollback).

Reason for asking:  I can simulate "deadlocks" if

a) I initiate transactions at high frequency from different application processes (eg. small starts and commits). In this case (lets say I have 5 applications running), 4 of the apps continuously post successfully to the database, whereas the 5th is 'unlucky' in the sense that it can never gain opportunity to lock the tables for posting - hence getting "Could not Lock the database...". The other 4 processes keep grabbing the locks.

b) I initiate a very large transaction - thus locking the database for a lengthy period, resulting in a second application process reaching the lock timeout (ie. cannot gain a change to lock the tables).

I may be totally wrong in my descriptions above, but this is what I have experienced.

My question  (specifically around scenario a above): I experienced this using a Local Session (ie. NOT client server, but file based / file server access).
Will I benefit (with regards to database transaction locking) if I use a Remote session with Database Server? Does the Database Server prioritize the table locks (allowing the 5th process to gain it's lock based on priority / schedule)?

Thx

Danie

>My question (specifically around scenario a above): I experienced this using a Local Session (ie. NOT client server, but file based / file server access).
>Will I benefit (with regards to database transaction locking) if I use a Remote session with Database Server? Does the Database Server prioritize the table locks (allowing the 5th process to gain it's lock based on priority / schedule)?

I don't know anything like that built in - someone else may do. C/S will help to give you stability (not that DBISAM F/S suffered from any lack of it) and speed with (generally) a reduction in network traffic. That's it. If you want some sort of serialisation you'll probably have to build it in yourself. I'd be tempted to build my own program, have a small table that you dump the requests in and then process them one after the other.

If you can give some more information about just what it is the various clients are doing someone may be able to make a better suggestion.

Roy Lambert

On 6/9/2015 11:17 AM, Danie van Eeden wrote:

> My question  (specifically around scenario a above): I experienced this using a Local Session (ie. NOT client server, but file based / file server access).
> Will I benefit (with regards to database transaction locking) if I use a Remote session with Database Server? Does the Database Server prioritize the table locks (allowing the 5th process to gain it's lock based on priority / schedule)?

I don't believe so.

The reality is that transactions require a database lock so you can run
into locking issues (and doing a long transaction is specifically not
recommended). If apps are not updating the same tables then you might
benefit from a restricted transaction but that of course will not help
is sames table(s) are involved.

You can use engine TableTransLockWaitTime and TableTransLockRetryCount
to either increase the wait time /retry count or alternatively dial them
down so your app times out quickly (make sure you catch the exception) -
then you still need to decide what to do (keep trying/cancel etc).

The other question of course is whether you need a transactions at all -
they are useful if you're doing multi-table updates and/or multiple
updates (like batch loading) and you want it to be all or nothing.

For scenarios where you're just inserting new records one by one for
example the transaction is usually not needed at all and regular locking
is all you need.

The other option is to redesign - for example do your own C/S
implementation where the clients just talk to the server and server is
the only one doing db operations. This would be bit more involved.

Raul

Hello,

Your question has been already answered by Roy and Raul, I just want to add that I can't see how would you get a deadlock in those circumstances... none of them would ever lead to a deadlock. Just a lock ... or a tedious death

--
Fernando Dias
[Team Elevate]

Fernando

>or a tedious death

Love it <VVBG>

Roy

Hi Fernando,

yes you are correct. Not a deadlock, but an extended lock resulting in other clients 'timing out' with "Cannot lock database". Or when we have numerous small transactions, a single client always 'misses' the opportunity to gain the lock (pure bad luck due to other clients gaining the lock first). These are the only two scenarios I struggle with (the second one seemingly the one requiring serialization)

@ All the others, many thanks for the replies. I can vouch for the speed improvement and security benefits from client / server (vs file).


Kind Regards
Danie

Fernando Dias wrote:

Hello,

Your question has been already answered by Roy and Raul, I just want to add that I can't see how would you get a deadlock in those circumstances... none of them would ever lead to a deadlock. Just a lock ... or a tedious death

--
Fernando Dias
[Team Elevate]