ORACLE ERROR

GRChandrashekar
GRChandrashekar used Ask the Experts™
on
Hi
I am using Oracle 9i with C# windows application and .net Framework 2.0

My problem is I have a table MONTHLYBILL WITH COLUMN VALUE AND MEMBER_ID

I have multiple users updating VALUE WHERE MEMBER_ID=1

For this my C# code is UPDATE MONTHLYBILL SET VALUE = VALUE+[VARIABLE] WHERE MEMBER_ID=100.

Now say user1 updates with VALUE=100. At same time user2 is updating value with 200. over all DB will get 300.
I cant use readcommitted because both users may commit at same time so I am using ISOLATION LEVEL SERIALIZABLE

Now what happens in say user 1 has enterd update loop but yet to commit. same time user2 enters update loop i get an error ORA-08177


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
you will have to put a retry loop in your process.

The 8177 error is there to protect you because it can see user2 doesn't have good data to work with since user1 hasn't committed yet.

So, you can either keep retrying the update when you hit an 8177,  or your can use dbms_lock to force serialization with a delay, so user2  will wait at the beginning of the transaction on that table until user1 is done with it.

Author

Commented:
Well looks like a good solution

can you give me an example for both the solutions you suggested please
Most Valuable Expert 2011
Top Expert 2012
Commented:
sorry I got hung up on your change in ISOLATION_LEVEL and started pursuing that.

On second thought though...

You DO want to use READ COMMITTED

there is no such thing as "at the same time" in Oracle.  One of them will always be before the other.
And, the commit isn't what is important.  It's the order of locking that produces what you want.

With READ COMMITTED,  if user1  updates the table, then user2 updates the same table,  user2 will wait until user1 commits (or rollback) at that time,  user2 will do it's update using the commited value that user1 just set.

Most Valuable Expert 2011
Top Expert 2012

Commented:
If you pursue the isolation level =serializable method, both of the options I describe above are designed to simulate read committed.

Easier (and more efficient) is to use the built in functionality.

Author

Commented:
Well the problem you described "With READ COMMITTED,  if user1  updates the table, then user2 updates the same table,  user2 will wait until user1 commits (or rollback) at that time,  user2 will do it's update using the commited value that user1 just set." is exactly what I am trying to resolve. Let me try READ COMMITTED and revert back to forum in case it does not work the way you described as

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial