Wow.

Thanks for the detailed response Helen.

>OK, let's start by understanding that, in principle, these are not "errors"
>(something that you have to avoid) but EXCEPTIONS. They are perfectly
>normal responses in a multi-transaction environment. It is just the server
>doing its job. What you need to do is catch these exceptions and handle
>them in whatever way gets you to the solution you need.
>
>
>

Got it. Thats what we want to do.

>Sure, a retry loop is a good way to manage exceptions; but it's not
>necessary under all circumstances to kill the transaction. If the
>applications that the conflicting users are running are all managing
>throughput efficiently, and not running transactions that last for long
>periods, you can work out a "wait time" that doesn't disturb the user too
>much and tailor your retry loop to suit. A brief wait, with an explanatory
>message, is usually more acceptable to a user than forcing him/her to begin
>again from the beginning.
>
>
>

Not an option for us, but a valuable idea for other applications.

>Is that a stored procedure call, or a series of DSQL requests from the client?
>
>
>

I is a large block of code running in multiple threads that runs a
series of DSQL requests. The issues have been occurring under load.

>Not clear what you mean by "incrementally".
>
>
>
>

Obviously you are right that the whole app should be handling this
situation correctly. However as code changes are required it would make
sense (to us) to deal with the problem areas first and test before
moving to other areas. So I mean, not dropping all existing code and
rewriting it in one go, but correcting one area at a time. Hope that
makes sense.

>Rolling back is the most drastic way of handling a lock conflict. Under
>some conditions, it will be what you want to do; in others, you will want
>to retry.
>
>
>

>By "structures", do you mean workflow?
>
>
>

What i meant by structures was kind of syntax / code layout. For
example you suggest retrying if a lock occurs. The 'structure' I see
for this might be something like...

t.StartTransacion;

//processing code here
.....


repeat
try
t.Commit;
SuccessfullyCommited := true;
except
//Is this a lock situation?
Dec(CommitRetries);
Sleep(500);
end
until (SuccessfullyCommited = True) or (CommitRetries = 0);


Is this a valid layout or is there a better structure?

>In the transactional environment, Autocommit should be used with great
>restraint, and with a full understanding of the implications of
>CommitRetaining, which is what Autocommit uses to commit these
>transactions. It is not pretty; and it really does demand that you
>intervene periodically to make a "hard commit" on this long-lived
>transaction context.
>
>
>

Will look into CommitRetaining :)

>You should also understand the implications of using ReadCommitted
>isolation for a task of many steps. More often than not, it will create
>the potential for inconsistencies, especially if you are passing variables
>through a chain of statements. If the task requires a consistent view of
>database state from start to finish, use Concurrency isolation.
>
>

We are happy with ReadCommited. A snapshot view is not necessary for
this task.

>If you are using TIBODatabase, you are stuck with the internal
>transaction. My policy for multi-user apps is to make sure that it's a
>complete no-op. Drop in a TIBOTransaction and take care to set its
>IB_Connection property. Then, assign the IB_Transaction property of all
>the statements involved in the task to this transaction explicitly. You
>can re-use the same transaction for other task groupings but, if there's a
>chance that the user will need to run two task groups concurrently then
>have a separate transaction for each.
>
>
>

Will do.

>A failed commit does not cause a rollback - simply the opportunity to wait
>(if it's a locking confict) or fix what's wrong (for other
>exceptions). But, of course, this means cutting your umbilical cord to the
>BDE way of doing things, which is to dumb-down multi-transaction processing
>so that the developer has the illusion that he is using Paradox or dBase on
>his own desktop harddrive.
>
>
>

Yes the umbilical cord is proving tricky to remove. It would be easier
if I was working with a new app instead of a large existing app.

>Well, sorry, no magic bullet here, but I do advise making it a priority to
>understand how transactions work and what the various locking exceptions
>mean in the overall scheme of things. If you haven't visited the TechInfo
>page yet, today would be a good day to do that. :-)
>
>
>

Damn. I feel I might need a bullet today :)

Thanks Helen



Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd