Re: Transaction Issue while maintaining sum

Sounds like you don't want to use transactions given:

" I need to update
> the sums as soon as the row is inserted or amount column is updated so
that
> the effect is reflected in the users interface.?

Some transactions may take more than 1 sql statement so at some time you may not "foot" and if the system crashes in the middle (or the user loses connection) you will have an inconsistent data.

e.g. transfering money from savings to checking. You want the whole transfer to happen in a transaction. Otherwise if things go south then you are missing money.

1.debit savings by x
2. credit checking by x
If something happens between step 1 and 2 you are missing x amount of money. Not good. So you handle this problem by wrapping it in a transaction. commit
1.debit savings by x
2. credit checking by x
commit;
Then you are guarrenteed that either the whole thing goes through or none of it goes through.

From my understanding of your situation you would show at some time money just gone! (while it went from savings to checking)

Jim

"Puneet Agarwal" <NOSPAM__puneetagarwal_at_india.com> wrote in message news:9sfqhc$12nsq0$1_at_ID-99266.news.dfncis.de...
> 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.
>
> 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. 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.
>
> 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
> >
>
>
Received on Fri Nov 09 2001 - 07:45:44 CST