Re: Transaction Issue while maintaining sum

In article <9se6g0$12t0l9$1_at_ID-99266.news.dfncis.de>, "Puneet says...
>
>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.

Look at materialized views, for example:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:424949760671

you can create an ON COMMIT mv that can incrementally refresh upon commit the "aggregate table".

You can interact with the data transactionally, you do not have to "cache" and do the work of a database on the client side.
>
>
>Thanks
>
>Puneet
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp