Home » SQL & PL/SQL » SQL & PL/SQL » Concurrency problem in cursor
Concurrency problem in cursor [message #321966] Wed, 21 May 2008 23:41 Go to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Dear All,

In the following code,
declare
cursor c1
is
select *
from test_concurrency
where a1 = 'a';

v_counter number(5) := 0;
begin
for c in c1 loop
v_counter := v_counter + 1;
update test_concurrency
set a1 = 'b';
dbms_output.put_line('counter'|| v_counter);
end loop;
end;

what I am expecting is that the loop runs just once -- because all the rows having a1 = 'a' are updated inside the loop.

But, the counter shows that the loop is running exactly the number of times equal to the number of rows having a1='a'.

How can we solve this problem?

Thanks in advance.

Sarwagya.

[Updated on: Wed, 21 May 2008 23:42]

Report message to a moderator

Re: Concurrency problem in cursor [message #321968 is a reply to message #321966] Wed, 21 May 2008 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>How can we solve this problem?
You need to consider Oracle's read consistency.

also since your never COMMIT the results may not be what you expect.

[Updated on: Wed, 21 May 2008 23:47] by Moderator

Report message to a moderator

Re: Concurrency problem in cursor [message #321973 is a reply to message #321968] Thu, 22 May 2008 00:02 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Thank you anacedent for your quick response.

declare
      cursor c1
      is
      select *
      from test_concurrency
      where a1 = 'a';

      v_counter number(5) := 0;
begin
      for c in c1 loop
                v_counter := v_counter + 1;
		
                update test_concurrency
                set a1 = 'b';
                dbms_output.put_line('counter'|| v_counter);
                commit;
      end loop;
end;


Even when I put a commit at the end of the cursor loop, there was no change in the output.

Output:
counter1
counter2
counter3
counter4
counter5

Re: Concurrency problem in cursor [message #321974 is a reply to message #321966] Thu, 22 May 2008 00:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> what I am expecting is that the loop runs just once -- because all the rows having a1 = 'a' are updated inside the loop.
When using cursors and loops, you shall read about them in the fine documentation, found eg. online on http://tahiti.oracle.com/.

But, as you want to achieve it in one step, why do you bother with loop and do not issue a single UPDATE statement? Is it a homework on cursors and loops?

By the way, as the UPDATE statement inside the loop has no WHERE condidition, it always updates all rows.
Re: Concurrency problem in cursor [message #321976 is a reply to message #321974] Thu, 22 May 2008 00:17 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
The code block I presented is just a simple example.
The actual requirement doesn't do exactly the same.

I mean to say that not all the rows are updated at once.
The update statement is quite different.

I didn't present the actual code because it would be rather confusing.
Re: Concurrency problem in cursor [message #321978 is a reply to message #321966] Thu, 22 May 2008 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what I am expecting is that the loop runs just once -- because all the rows having a1 = 'a' are updated inside the loop.

A query see the database as it is when it starts, whatever happens after this time, so what you put in your loop does not affect the result of the query.

Regards
Michel
Re: Concurrency problem in cursor [message #321982 is a reply to message #321966] Thu, 22 May 2008 00:45 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Then, I will just confirm that this is the correct behaviour when using loops.
If you would just report the rows, there is no problem with consistency, as it is enforced by Oracle.
When you are updating the rows, you shall lock them by SELECT FOR UPDATE, as also pictured in the documentation: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1505

But, you shall do this even when one row would be fetched.

[Edit: Added last paragraph]

[Updated on: Thu, 22 May 2008 00:47]

Report message to a moderator

Previous Topic: Determining size of database/tables
Next Topic: Dynamic sql
Goto Forum:
  


Current Time: Sun Dec 04 00:38:08 CST 2016

Total time taken to generate the page: 0.04580 seconds