Home » SQL & PL/SQL » SQL & PL/SQL » update with no wait (oracle 10 unix)
update with no wait [message #406564] Thu, 04 June 2009 11:50 Go to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
Hello
my question
I want to try implement update with no wait. I mean in the loop ,in ccase when record is locked I 'd like to rise exception and keep going for the next record.
I found on internet something like this

ACCEPTED SOLUTION
02/02/01 08:10 AM, ID: 5806472
jahse:

The UPDATE command doesn't have the NOWAIT option.
We can use a PL/SQL block (as below) to achieve this.
DECLARE
  x CHAR(1);
BEGIN
  SELECT 'x' INTO x
  FROM tablea
  WHERE -- your update condition
  FOR UPDATE OF cola NOWAIT;

  UPDATE tablea
  SET cola = value
  WHERE -- your update condition
EXCEPTION
  WHEN OTHERS THEN
  NULL; -- handle the exception
END;


my question is :
between select and update could another session lock the record ?
thanks

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 05 June 2009 03:03] by Moderator

Report message to a moderator

Re: update with no wait [message #406567 is a reply to message #406564] Thu, 04 June 2009 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it can't (but someone can insert a new record that satisfies your where condition if it is not a unique one) and so can stop you.
Use a cursor loop then you are sure to own the rows you want to update.

REMOVE "when others" clause, every time, every where.

Regards
Michel
Re: update with no wait [message #406570 is a reply to message #406567] Thu, 04 June 2009 11:59 Go to previous messageGo to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
Thank you Michel
Re: update with no wait [message #406642 is a reply to message #406564] Fri, 05 June 2009 02:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NO - once you've performed a SELECT ... FOR UPDATE you have got the row locked, and no-one else can lock it.

@Michel - even with a cursor loop, someone could insert and commit a record that matched the required conditions.

Is what you're pointing out that if you use a cursor for loop and an Update Where Current of then you can be sure that you've only updated the rows that you locked initially.
Re: update with no wait [message #406645 is a reply to message #406642] Fri, 05 June 2009 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is what you're pointing out that if you use a cursor for loop and an Update Where Current of then you can be sure that you've only updated the rows that you locked initially.

Yes, this is what I was thinking about.

Regards
Michel

[Updated on: Fri, 05 June 2009 03:02]

Report message to a moderator

Re: update with no wait [message #406796 is a reply to message #406564] Fri, 05 June 2009 23:17 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
There is an feature in Oracle Database, the clause FOR UPDATE SKIP LOCKED, which can be used to lock rows that are available for locking and skip the rows that have been locked by other sessions. This statement returns the control back without throwing an exception, even if all the rows are locked by another session.


May be this helps you....
Re: update with no wait [message #406811 is a reply to message #406796] Sat, 06 June 2009 01:01 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This feature does not exist in 10g, I mean for us as it is not documented and you don't know what it actually does or assumes in this version.

Regards
Michel
Previous Topic: Problem with nested types (merged)
Next Topic: Problem while getting a line
Goto Forum:
  


Current Time: Wed Dec 07 22:20:19 CST 2016

Total time taken to generate the page: 0.05389 seconds