Subject RE: [IBO] Correct Transaction Handling
Author Alan McDonald
> 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.

My approach is to not use this design of achieving running totals but
instead, use a selectable stored procedure to return values (the running
totals per accounting transaction) which are not (never) stored in the
database. You get good internal consistency and you never get an issue from
multi-user environment. You never see accounting transactions which are yet
to be db committed.
The only thing to watch is to make sure the design always returns a
"relevant" window of transactions (e.g. YTD, MTD) so your returns are never
very large (especially when you db grows to include 10 yers of accounting
transactions). And make a "rollover" procedure where previous period
accounting transactions are parked away from the "current" set.