Re: Transaction Issue while maintaining sum

From: Wayne Kidd <gwk_at_audiobookcentral.com>
Date: Thu, 08 Nov 2001 23:31:25 -0800
Message-ID: <3BEB864D.784EDACE_at_audiobookcentral.com>


You have to decide which of 2 conflicting unpleasant consequences you are willing to resolve.

1). The Account must be locked from other users during the think time of the user to insure that the account balance stays as the user saw it at the beginning of Voucher (and voucherdetail) entry. This is known as pessimistic locking.

2). The Account balance is generally assumed to be the same at the start of the Voucher (and voucherdetail) entry as at the completion(or close enought to continue). With this assumption, you must read the Account Balance without locking at the beginning and then read with locking at the end (to check that nothing has changed). If something has changed (in so horrible a way that you cannot proceed), then you must inform the user of the new situation and have them begin again. This is known as optimistic locking.

Let's say that your vouchers are for $100 total and the account balance tends to be around $1,000,000. Optomistic locking is a good choice in this case because you are rarely likely to run out of money. Even though you have to guard against the problems and re-find the balance at the end,. you will mostly never have to lock out users from the Account unless there is not enough money at the beginning (who knows, maybe receivables are improving the situation from the other side).

Wayne

Puneet Agarwal wrote:

> I am seriously stuck in a transaction handling issue.
>
> I have following tables
>
> Account
> AccountId INT PK
> AccountName String
> Balance INT PK
>
> Voucher
> VoucherId INT PK
> VoucherDate Date PK
>
> VoucherDetails
> VoucherDetailId Int PK
> VouhcerId INt FK for Voucher Table
> AccountId INT FK for Accounts Table
> Amount INT
>
> I have a Java Swing based thick client through which I want the users to
> enter data into these tables. Users need to simultaneously create vouchers
> which may corresponding to same account. Users need the capability to
> save(commit) or undo(rollback) changes on the voucher level. Each entry in
> voucher may have many entries in voucherdetails. I want to use a trigger on
> voucherDetail table insert/delete/update operations so that i can update the
> balance column in the account table accordingly. But in case two users are
> feeding data concurrently and they happen to start feeding a voucher which
> contains entry for same account in voucherDetails, the first users
> transaction will hold a lock on the corresponding account table row. And now
> if the second user tries to update that row, he will find the row locked and
> thus he will not be allowed to feed the voucher till the first user commits
> his data and releases the lock.
>
> Kindly suggest me a solution for this scenario. The requirement is very
> important for me that the users should be able to commit/rollback the
> changes on voucher level which may include many rows in voucherDetails
> table.
>
> Thanks
>
> Puneet
Received on Fri Nov 09 2001 - 08:31:25 CET

Original text of this message