Read Data before Commit ?
Hi I have a situation as follows
TABLE MONTHLYBILL [ NO INSERT, ALWAYS UPDATE ONLY]
MONTHLYBILL_ID [PK]
MEMBER_ID
AMOUNT
TABLE MONTHLYBILLDETAIL [INSERT / UPDATE]
MONTHLYBILLDETAIL_ID [PK]
MONTHLYBILL_ID [FK]
LEDGER_ID
AMOUNT
In a multi user environment, let us say user1 is trying to do as follows
1 UPDATE MONTHLYBILL SET AMOUNT = AMOUNT +100 WHERE MEMBER_ID=1
2 INSERT INTO MONTHLYDETAIL LEDGER_ID=1 AMOUNT= AMOUNT +75
3 INSERT MONTHLYDETAIL LEDGER_ID=1 AMOUNT = AMOUNT + 25
In a multi user environment, let us say user2 is trying to do as follows
1 UPDATE MONTHLYBILL SET AMOUNT = AMOUNT +100 WHERE MEMBER_ID=1
2 INSERT INTO MONTHLYDETAIL LEDGER_ID=1 AMOUNT= AMOUNT +75
3 INSERT MONTHLYDETAIL LEDGER_ID=3 AMOUNT = AMOUNT + 25
So in case 1, line 3, it should actually update since LEDGER_ID =1 is already present.
In case 2 lie 2, it should update since user1 has aleady inserted LEDGER_ID=1 but line 3 should be inseted since LEDGER_ID=3 is not present.
When so many users are operating how do i ensure that this logic works correctly ?
TABLE MONTHLYBILL [ NO INSERT, ALWAYS UPDATE ONLY]
MONTHLYBILL_ID [PK]
MEMBER_ID
AMOUNT
TABLE MONTHLYBILLDETAIL [INSERT / UPDATE]
MONTHLYBILLDETAIL_ID [PK]
MONTHLYBILL_ID [FK]
LEDGER_ID
AMOUNT
In a multi user environment, let us say user1 is trying to do as follows
1 UPDATE MONTHLYBILL SET AMOUNT = AMOUNT +100 WHERE MEMBER_ID=1
2 INSERT INTO MONTHLYDETAIL LEDGER_ID=1 AMOUNT= AMOUNT +75
3 INSERT MONTHLYDETAIL LEDGER_ID=1 AMOUNT = AMOUNT + 25
In a multi user environment, let us say user2 is trying to do as follows
1 UPDATE MONTHLYBILL SET AMOUNT = AMOUNT +100 WHERE MEMBER_ID=1
2 INSERT INTO MONTHLYDETAIL LEDGER_ID=1 AMOUNT= AMOUNT +75
3 INSERT MONTHLYDETAIL LEDGER_ID=3 AMOUNT = AMOUNT + 25
So in case 1, line 3, it should actually update since LEDGER_ID =1 is already present.
In case 2 lie 2, it should update since user1 has aleady inserted LEDGER_ID=1 but line 3 should be inseted since LEDGER_ID=3 is not present.
When so many users are operating how do i ensure that this logic works correctly ?
ASKER
Yes Problem is understood correctly. It is winform application with C# framework 2.0 Mutlithreading does not arise and it this action is performed from one form
ASKER
But remember, checking LEDGER_ID when data is not commited
You should put all the DB logic into a Stored Procedure and also use Transactions (with locks) so that the in case of multi-user scenario no matter the order of their invocation the processing is always sequential.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi!
You might write a trigger on detail table to update AMOUNT in master table acording to value inserted in detail...
Something like:
create or replace trigger BLAH
before insert or update on MONTHLYBILLDETAIL
begin
update MONTHLYBILL set amount=amount+nvl(:new.amo
end;
But instead of inserting/updating/merging
use simple MERGE inside it, but in separate procedure, just to to handle tricky code on separate location...
Regards
Hrvoje
You might write a trigger on detail table to update AMOUNT in master table acording to value inserted in detail...
Something like:
create or replace trigger BLAH
before insert or update on MONTHLYBILLDETAIL
begin
update MONTHLYBILL set amount=amount+nvl(:new.amo
end;
But instead of inserting/updating/merging
use simple MERGE inside it, but in separate procedure, just to to handle tricky code on separate location...
Regards
Hrvoje
Also, in a multi user environment, using Multithreading approach from the Front end may be ideal using individual worker threads, and using thread synchronization for managing simultaneous DB operations.
-XM.