I need to keep a running balance and allow users to subtract from it unless doing so would result in a balance less than zero. This must be a common requirement, and I wondered what approach others are using.

1) The naive approach is to have a class with a balance field and something like

Withdraw(amount) {
//(checking for overwithdrawal not shown)
balance = balance - amount;
}

but if we use have transaction isolation of READ COMMITED, this would allow
concurrent threads to over-write each other.

2) The next appoach might be to use hibernates pessimistic locking, but as far as I can see this is only possible if you load the object containing the balance directly- and I would like to be able to load it via an association, or at least to be able to load it at will in different areas of the code without havin to remember to lock it.

3) Another approach might be to maintain balance and total withrawals fields in the domain object.

private withdrawals

Withdraw(amount) {
//(checking for overwithdrawal not shown)
withdrawals = withdrawals + amount;
}

and then in the hbm configure the object to be saved via a straight SQL call like

SET BALANCE = BALANCE - withdrawals WHERE ID = id;

This prevents concurrent users overwriting each other, but it still allows the balance to change between checking for overwithdrawal and committing the change. Perhaps a constraint could catch these rare cases, although it would be difficult to present a nice error to the user.

4) another approach might be to store each withdrawal as a seperate row in a table and calculate the balance dynamically by adding each withdrawal and then subtracting from the original balance.

This would also allow concurrent updates to the balance, but also allows for the possibility that the balance would change between checking for an overwithdrawal and comitting the change.

any other ideas? what are other people doing?

cheers

to answer my own question..

another approach is to use the 'naive' approach above and implement optimistic locking, then catch an optimistic locking exception and re-try the
operation.

The exception would be thrown when the session is flushed, so you have to
deal with it on transaction commit ( or flush the session explicitly )