At 05:59 PM 21/06/2004 +0000, you wrote:

>THE PROMBLEM:
>
>I have an accounting application which tracks customer accounts (and
>the running balance) using a growing ledger. A trigger on the ledger
>table looks up the last ledger entry's balance, adds the new amount,
>and saves a new calculated balance. Records appear like this...
>Amount Balance
>1 1
>2 3
>4 7
>The result is a quick reference to where a balance was at any given
>time. Works great on one machine.
>
>But when TWO applications run, the balance is mis-read by IBO as follows:
>Amount Balance
>1 1
>1 1 (second machine does not see prior ledger entry)
>4 5 (first machine is not seeing the second machines addition)
>... You get the idea.
>
>IBO transaction levels tiConcurrency and Committed both allow this
>logical error to occur.

It is not a logical error, from a multi-user point of view. The purpose of
transaction isolation is to prevent one transaction from seeing the
uncommitted work of others. The work from a trigger is inside the
transaction that performs the operation. If the transaction rolls back,
the trigger's work is undone. Therefore, it would be a logical error if
one transaction performed a calculation that included the uncommitted work
from another transaction. (= "dirty read").

>tiConsistancy causes deadlock errors all over
>the place and destroys my nice customer browsing screen.

tiConsistency should never be used.

>QUESTION:
>
>The problem is that the two machines are calculating a new balance
>without seeing the other computers records.
>
>Is this a bad way to track customer account balances?

It isn't a great idea in a multi-user system but sometimes we need to do
things that aren't a great idea...

>Would a trigger level update of the customer record fix this issue by
>allowing tiConcurrency but still force a deadlock when one occurs?

>Is there a better way?

From the database side, the better way would be to If you want to prevent
two transactions from editing the same customer record simultaneously, then
use PessimisticLocking.

>Would isolating the ledger updates with a tiConsistancy transaction
>fix this issue? (I consider this to be a bad solution since the wrong
>setting creates logical errors in the database! I prefer my database
>to be able to somehow protect itself.)
>
>Can anyone advise me on the best solution?
>
>Thanks,
>Paul
>
>
>
>
>
>
>___________________________________________________________________________
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
>___________________________________________________________________________
>http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>Yahoo! Groups Links
>
>
>
>