Re: Transaction Issue while maintaining sum

Hi Puneet.

Not all database systems use locks.

I suggest that you look at:
http://developer.mimer.se/features/features_result.tml?ID=15

for information about optimistic concurrency control (OCC).

--
Daniel Gustafsson
Validate SQL statements at http://developer.mimer.se/parser

"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

> >