Re: Transaction Issue while maintaining sum

From: Ivan Arjentinski <ivanarj_at_aloeco.com_>
Date: Thu, 8 Nov 2001 17:09:11 +0200
Message-ID: <O257BdGaBHA.2408@tkmsftngp05>

Puneet,

In my opinion you failed to note what is your problem.

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

This behaviour is by default. It should not be a problem for you. As soon as the first user transaction finishes creating the voucher lines (this should be very fast), the second user transaction will proceed automatically.

I think that you might be misusing the client/server architecture as a general. Maybe you start creating the voucher row (and the transaction) immediately as the client starts entering. Instead, you should cache the whole voucher at the client and when it is complete send it as a single batch (transaction) to the server.

NEVER, NEVER, NEVER do allow user interactions while in transaction!

Hope this helps,
Ivan Arjentinski

---

Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE scripts), sample data (INSERT INTO....) and desired result set when asking for assistance is highly appreciated

---

"Puneet Agarwal" <NOSPAM__puneetagarwal_at_india.com> wrote in message news:9se6g0$12t0l9$1_at_ID-99266.news.dfncis.de...
> 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 Thu Nov 08 2001 - 09:09:11 CST