At 12:09 PM 19/10/2005 +1300, Rob Martin wrote:

>Hi
>
>Firstly let me apologies if this is the wrong place o post this but here
>we go......
>
>
>We have a app converted from xbase and BDE, using IBO components.
>
>We have been using the built in TIBODatabase transaction. This is set to
>tiCommited and AutoCommit (tom mimic BDE functionality) We have
>recently noticed a number locking errors
>
>lock conflict on no wait transaction
>deadlock
>update conflicts with concurrent update
>
>So we are wanting to sort out our transaction handling. We have couple
>of procedures that cause this problem most frequently. We thought we
>would start by fixing these areas.

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.

>We are considering explicitly starting a transaction, placing the whole
>transaction in a try except (we are using Delphi) with a rollback at
>the end if a conflict occurs.

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.

>We would then have to code some sort or
>retry at a later point, probably including a number of times the
>transaction is allowed to retry.

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.

>The procedure is quite large and includes a number of tables to be updated.

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

>My questions are (bearing in mind we would like to do this
>incrementally)....

Not clear what you mean by "incrementally".

>1) Is this a reasonable thing to do?

It is always reasonable (nay, essential) that you trap and handle exceptions.

>2) What kind of structures would people recommend?

By "structures", do you mean workflow?

>3) Should we use the internal transaction of the try / except / rollback
>or should we point all components in this procedure (and any it calls)
>to a specific transaction?

You should NOT use an autocommit transaction for a task that has multiple
interdependent operations. Autocommit causes each DML request to be
committed immediately - so there is no going back if an earlier execution
succeeds and a later one fails because of an error in the earlier one. You
totally lose the overwhelming reason for using the protection of
transactions - atomicity.

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.

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.

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.

My preference when writing apps for multiple users is always to use
explicit transactions - as many as I need. That way, you can wrap all of
the pieces of a single task inside a single transaction that you
control. You'll start the transaction, do all of the jobs, handle
exceptions along the way and then, finally, try to commit the
transaction. If it excepts at that point, it's your choice whether to roll
back, retry, or give the user the opportunity to go back and fix an error.

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.

>4) Any other comments

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. :-)

Helen