ORACLE ERROR
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
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
ASKER
Well looks like a good solution
can you give me an example for both the solutions you suggested please
can you give me an example for both the solutions you suggested please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Easier (and more efficient) is to use the built in functionality.
ASKER
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
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.