Re: Transaction Issue while maintaining sum

In article <9sfqhc$12nsq0$1_at_ID-99266.news.dfncis.de>, "Puneet says...
>
>Thanks for reply.
>
>Now I see there are two different views. One saying that allowing client
>interactions is extremely not desirable and other i.e. yours suggesting that
>I may proceed this way i.e. allowing user interaction while in transaction
>to avoid the requirement of caching data on the client side. Can you please
>refer me a resource where I can get some more information regarding this.
>

In databases that use shared read locks for consistency, BLOCKING reads and such -- pulling the data out -- doing all of the work in the client and then putting it back in is normal. This includes databases like db2, informix, sqlserver and so on.

In databases that do not employ shared read locks and do not have blocking reads and such -- doing work in that way is not at all necessary and complicates your life (you have to worry about issues such as lost updates, not being able to do your updates since someone updated the data you were going after already (and beat you to it) and so on).

>Regarding the solution you have suggested to my problem, using on commit
>triggers or materialized views, I have a problem in that. When my user is
>entering the vouchers, he needs to see the total of transactions as well as
>the balances of the accounts which are updated simultaneously as he enters
>data.

Well, you are saying basically that you can have but one user at a time work on an account then! By definition!

If you have two users working on an account and they both need to see the balances *accurately* --- you by definition cannot have concurrent updates! You cannot have your cake and eat it too in this case.

If you have two users working on the account with outstanding transactions -- by definition they are not allowed to see eachothers work.

< Using on commit triggers does not solve my problem. I need to update
>the sums as soon as the row is inserted or amout column is updated so that
>the effect is reflected in the users interface. It is extremely complex to
>handle it otherwise because there can be numerous dependencies and
>conditions which is difficult to program even in the middle tier in business
>logic only, and implementing the same thing on the client side as well as
>maintaining the changes that happen later on at both ends is almost
>impossible.
>

people would query the sum of balance from accounts directly, it is somewhat unusual to maintain this balance in another table -- as you are finding, its a bit of a mess.

But bear in mind, if you go after the sum in accounts -- and you have two sessions working on it -- they will NOT see eachothers changes. If this was an ATM like thing -- you could get into a real mess unless you serialize the transactions physically. Say you have $100 in the bank. Session 1 withdraws $75. No problem the sum(bal) goes to 25. The other session does the same -- no problem here either, sum(bal) goes to 25. When both commit, the sum(bal) goes to -50, now we have a big problem. You need to serialize the account access -- meaning in a high volume system where >1 session working on an account at the same time is probable -- you will need to keep your transactions very short indeed.

>I do not understand how do people handle this situation. Because usually in
>thick client applications, the user interface is quite interactive and has
>to be always synchronized with the underlying data. The refresh on demand
>approach should be avoided in thick clients.
>
>Please help me.
>
>Thanks
>Puneet
>
>"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>news:9sesdc07i4_at_drn.newsguy.com...
>> 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:424949760
>671
>>
>> 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
>>
>
>

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