Solved

Read Data before Commit ?

Posted on 2010-09-06
6
650 Views
Last Modified: 2013-12-19
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 ?



0
Comment
Question by:GRChandrashekar
6 Comments
 
LVL 1

Expert Comment

by:XMarshall10
ID: 33610207
If I have understood ur issue correctly, Irrespective of the fact that you are directly programming in the back end (using TSQL) or in the Front end (using C#), you should check the existing value in the LEDGER_ID field, and then decide whether to INSERT or UPDATE.

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

Author Comment

by:GRChandrashekar
ID: 33610252
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
0
 

Author Comment

by:GRChandrashekar
ID: 33610299
But remember, checking LEDGER_ID when data is not commited
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33610437
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.
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 500 total points
ID: 33610783
Hi,

You can use the MERGE statement instead of choosing INSERT or UPDATE.
If not, you will have to SELECT FOR UPDATE to check if the row exists, then choose to UPDATE or INSERT.
But as a concurrent insert can happen at the same time, you must catch the primary key constraint violation error when you INSERT, and then you will UPDATE.

Regards,
Franck.
0
 
LVL 3

Expert Comment

by:htonkov
ID: 33613525
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.amount,0)-nvl(:old.amount,0);
end;

But instead of inserting/updating/merging into detail table I'd personally use a procedure to handle that,
use simple MERGE inside it, but in separate procedure, just to to handle tricky code on separate location...

Regards
Hrvoje
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
unable to get sorting resultset 15 47
Data Saving(2) 2 25
fomat Json objects 6 17
Iterate two lists and remove inactive 11 28
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now