Home » SQL & PL/SQL » SQL & PL/SQL » Update same record simultaneously from two calls to same procedure (merged)
Update same record simultaneously from two calls to same procedure (merged) [message #384923] Thu, 05 February 2009 14:51 Go to next message
tech_inquisitor
Messages: 2
Registered: February 2009
Junior Member
Hi,
I have a scenario for which any response would be greatly appreciated. I would like to write a procedure which upon being called simultaneously should update the same record in a table without either of the call to the procedure not to error out.

Suppose Table A is as below:

Cust_Name varchar2(40)| Salary number(10)

Dan | 2000
Jim | 4000

I want to update the salary of Dan from two calls to same procedure from different sessions but neither one should fail because of any locks.

Session 1

update A set salary=salary+40
where cust_name = 'Dan';

select salary from A
where cust_name = 'Dan'

expected result: 2040

Session 2

update A set salary=salary+20
where cust_name = 'Dan';

select salary from A
where cust_name = 'Dan'

expected result: 2060

Assuming Session1 obtained lock for the table A first.

My question is will the procedure call from session 2 error out saying Table A is locked.

How to achieve the above result. Any input is greatly appreciated.

Re: Update same record simultaneously from two calls to same procedure [message #384927 is a reply to message #384923] Thu, 05 February 2009 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I want to update the salary of Dan from two calls to same procedure from different sessions but neither one should fail because of any locks.
The use an RDBMS other than Oracle.
Data integrity is of highest importance to Oracle & it will refuse to do what you desire to be done.
Re: Update same record simultaneously from two calls to same procedure (merged) [message #384928 is a reply to message #384923] Thu, 05 February 2009 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dirty reads are not allowed nor possible in Oracle.
No writer blocks a reader.
No reader blocks a writer (or another reader).

Regards
Michel
Re: Update same record simultaneously from two calls to same procedure (merged) [message #384930 is a reply to message #384923] Thu, 05 February 2009 15:09 Go to previous messageGo to next message
tech_inquisitor
Messages: 2
Registered: February 2009
Junior Member
If I include the following statement before I call the update statement in the procedure would resolve the issue

select salary
from A
where cust_name = 'Dan'
for update of salary.

Please do reply as I wanted to address this issue. I have to use the same procedure to update the salary from two different calls and the second one reading the updated salary from the first one.

Is there any way possible to do this.
Re: Update same record simultaneously from two calls to same procedure (merged) [message #384932 is a reply to message #384923] Thu, 05 February 2009 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way possible to do this.
which part of "NO" do you not understand?

>If I include the following statement before I call the update statement in the procedure would resolve the issue
It will return 2000!

[Updated on: Thu, 05 February 2009 15:22]

Report message to a moderator

Re: Update same record simultaneously from two calls to same procedure (merged) [message #385028 is a reply to message #384930] Fri, 06 February 2009 04:23 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could change your statements like this:
Session 1
 
 update A set salary=salary+40
 where cust_name = 'Dan';
 
 commit;

 select salary from A
 where cust_name = 'Dan'
 
 expected result: 2040
 
 Session 2
 
 update A set salary=salary+20
 where cust_name = 'Dan';

 commit;
 
 select salary from A
 where cust_name = 'Dan'


Even without the Commits; session 2 will never return an incorrect answer - the update statement will wait until Session 1 has committed or rolled back it's changes, and will then update the table.
Previous Topic: Need help in date functions
Next Topic: length of long data type
Goto Forum:
  


Current Time: Sat Dec 03 07:46:35 CST 2016

Total time taken to generate the page: 0.23591 seconds