| update with no wait [message #406564] |
Thu, 04 June 2009 11:50  |
ykozhevnikov
Messages: 59
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 #406642 is a reply to message #406564] |
Fri, 05 June 2009 02:54  |
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 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #406811 is a reply to message #406796] |
Sat, 06 June 2009 01:01 |
|
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
