Subject Correct Transaction Handling
Author doughboypfb

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. tiConsistancy causes deadlock errors all over
the place and destroys my nice customer browsing screen.


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?
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?
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?