Subject Re: [ib-support] wait transactions and rollback
Author Woody
From: "Ann Harrison" <aharrison@...>

> My concern with live-lock is the possibility that
> two transactions are interlocked. Transaction 1
> updates record A and tries to update record B.
> Transaction 2 updates record B and tries to update
> record A.

True, if there are 2 records being updated in one transaction. However, the
original intent of the poster was to just read and update a counter field.
Such a simple action should not require a miriad of complexities to
accomplish. A simple procedure that attempts a dummy edit on the record is
enough in this case, IMO.

> In a "wait" transaction the server notices that
> the two transactions are waiting for each other
> and produces a "deadlock" error - not just an
> "update conflict".
> If both transactions are "no wait", then they
> are, in fact, never waiting for each other.
> The server has no way to recognize the problem,
> so they continue until they system reboots.

Unless a message is generated on the client to try again or quit trying.
This is easy to accomplish and doesn't require much effort as far as
programming goes.

> My recommendation? Use a wait transaction, commit
> or rollback when you see an "update conflict" error
> and retry your operation, including re-reading the
> records you based the change on. I realize that
> the IBX (BDE?) version of "data driven" controls
> makes that very awkward. My reaction to that is
> use a better tool, but that's getting political.

Too late, it's already political. <G> All tools except client datasets that
store the information locally are "data driven" and have the same type of
issues. How you resolve the issue may depend on the type of components you
use, but the basic problems still exist none-the-less. The best solution I
have found is to work only with one record when editing information on a
form, etc. That gives you the ability to test whether or not someone else
already has the record "locked" while editing and keeps transactions as
short as possible when working with live data.

> What works is to issue a dummy update while you're
> gathering data. The update can be to a specific
> record, or to a generic "gateway" record. Until
> that update succeeds, don't start reading data.
> When it does, your update will protect your access
> to the record (or set of records if you're using
> a gateway.)

I agree with this type of solution, however, reading data isn't really an
issue, only modifying it, except in specific cases.

Sorry to go on about this, Ann, but there are many people that I see post
similar problems and the answers are generally skirting around the real
issue which is the proper use of transactions. Too many times I've seen
posts that say something like "My app hangs until the other user releases
their transaction." Using wait transactions can cause just as serious a
problem for some applications as using no-wait transactions. It's all in
what you are trying to do. What works best in one situation may not work in
others. DIfferent situations require different and sometimes unique

Woody (TMW)