Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Gunjeet.

Asked: October 19, 2000 - 5:28 pm UTC

Last updated: May 09, 2011 - 11:27 am UTC

Version: v 8.1.6

Viewed 50K+ times! This question is

You Asked


Hi Tom,

I have a very basic question about the use of SELECT
FOR UPDATE cursors especially when used with DELETE
statements.

Could you please explain to me why someone would want
to get an an exclusive lock on the rows that are to be
deleted ? Do we care if someone tries to update them
at the same time ?

Also, if the result that was to be achieved with a
combination of SELECT FOR UPDATE and DELETE CURRENT OF
statement could be done in a single delete statement
(with a sub-query) can it be error-prone in a multi-user
env ? Won't a simple DELETE also lock the rows thus
achieving the same result ?

Is it absolutely essential to use SELECT FOR UPDATE for
doing updates in an environment where the same data can
be updated by multiple people ? What are the repercussions
if we don't use SELECT FOR UPDATE for updating/deleting
the data ?

Also, will the performance of stored procedures without
SELECT FOR UPDATEs be better than the ones with simple
update/delete statements as there is no additional
overhead of locking rows.

Thanks,

Gunjeet

and Tom said...

It depends on the logic.

Oracle forms for example will issue a SELECT for UPDATE on any row you attempt to update or delete from the GUI. It does this since you read the row out at time T1 and you delete it at time T2. It wants to make sure that in the time between T1 and T2 -- no one UPDATED that row. It wants to make sure you do not delete the row without having at least looked at the modified information (that would what is commonly known as a "lost update"). It also does this to ensure that when the time comes, the delete will proceed in a non-blocking fashion. The select for update forms puts on the row ensures

o the row was not changed
o the row will NOT be changed
o the row is locked by you and you will be able to delete it.

So, in forms the logic is something akin to :

1) read the data out with select t.*, rowid from T
2) let the user look at the data
3) when the user decides to delete the "5'th" row in the result set, forms will issue:

select t.*, rowid from T
where t.c1 = :block.c1
and t.c2 = :block.c2
and ....
and t.rowid = :block.rowid
FOR UPDATE NOWAIT;

if that returns ORA-54 (resource busy), you are notified someone else has that row locked.

if that returns 0 rows, you are told that the data has been modified -- you need to requery

if that returns 1 row, you have the row locked and will be able to delete it and no one else can touch it.



In a stored procedure - it will depend. If you do not need to inspect the data BEFORE doing the delete, you do not need this logic in general. If you need to inspect the data before the delete -- YOU NEED this logic (to ensure the data does not change under the covers). So, if you have coded:

declare
cursor c1 is select * from t where .... FOR UPDATE;
begin
for x in c1 loop
delete where current of c1;
end loop;
end;

that would be better written as

begin
delete from t where .....;
end;


OTOH, if you have coded:

declare
cursor c1 is select * from t where .... FOR UPDATE;
begin
for x in c1 loop
some procedural code to look at X
if ( some condition based on X ) then
delete where current of c1;
end if;
end loop;
end;


you really do need the select for update to ensure that no one else updates your data.


Select for update is a programming tool -- use it when you need to.

Rating

  (46 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SELECT FOR UPDATE cursors

Julie, February 28, 2002 - 7:56 am UTC

The examples are wonderful they help me understand things much quicker. Keep up the great work!

update for update no wait

nagaraju, March 04, 2002 - 6:13 pm UTC

i want to update a row with no wait.

can i use like update <tablename> set column1 = 'A' where <condition> for update no wait

or
i have to write like
select rowid from <tablename> where <condtion> for update no wait;

then
update statement..

cant i do with a single stmt

Tom Kyte
March 04, 2002 - 8:05 pm UTC

You do the select for update nowait and then the update.

Steve, March 05, 2002 - 4:42 am UTC

Tom,

why does a SELECT FOR UPDATE statement still lock a row in the table, even though the select returned no rows?


e.g.
COLUMN Object_Name FORMAT A10
create table t1 (
a int ,
b int ,
c int
)
/
insert into t1
values (1,1,1);
insert into t1
values (1,2,2);
insert into t1
values (5,1,3);

commit;

update t1
set a = 3
where c = 2
/
commit;

SELECT do.Object_Name,do.Object_Type,vlo.Locked_Mode
FROM SYS.V_$Locked_Object vlo,
SYS.DBA_Objects do
WHERE vlo.Object_id = do.Object_Id
AND do.Object_Name = 'T1'
AND vlo.Oracle_Username = 'SCOTT'
/

SELECT c
FROM t1
WHERE a = 1
AND b = 2
AND c = 2
FOR UPDATE NOWAIT;

SELECT do.Object_Name,do.Object_Type,vlo.Locked_Mode
FROM SYS.V_$Locked_Object vlo,
SYS.DBA_Objects do
WHERE vlo.Object_id = do.Object_Id
AND do.Object_Name = 'T1'
AND vlo.Oracle_Username = 'SCOTT'
/
COMMIT;

Tom Kyte
March 05, 2002 - 8:39 am UTC

It didn't lock a row in the table.  It locked the TABLE itself.  A more clear query might be:

ops$tkyte@ORA817DEV.US.ORACLE.COM> SELECT          c
  2  FROM       t1
  3  WHERE        a     =          1
  4  AND    b    =    2
  5  AND    c    =    2
  6  FOR UPDATE NOWAIT;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select (select object_name from user_objects where object_id = lock_id1) obj_name,
  2             lock_type, mode_held
  3    from dba_locks
  4   where session_id = (select sid from v$mystat where rownum=1)
  5  /

OBJ_NAME   LOCK_TYPE                  MODE_HELD
---------- -------------------------- ----------------------------------------
T1         DML                        Row-S (SS)

That shows the name of the mode -- going to the server concepts manual:

http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c23cnsis.htm#2989

we can see that locks the table (due to the SELECT FOR UPDATE just being issued) in Row share mode -- this prevents another session from locking that table in exclusive mode but nothing else.  There are no TX (transaction) locks -- hence no rows are locked in that table.
 

Select for Update

Steve, March 06, 2002 - 3:29 am UTC

Thanks Tom.

For update and Group By

A reader, July 27, 2002 - 7:12 am UTC

Dear Tom,
Please consider this requirement:
Field_Code Value Picked_up
10 10 No
20 15 No
10 20 No

All the records with "Picked_up=No" are to be selected and populated into another table with "group by" on Field_Code, i.e.

Field_Code Value
10 30
20 15

The Picked_up is to be updated to 'Yes'.
If we first do a Select for Update, we might have another record coming in when we group by subsequently. For Update and Group By cannot be used simultaneously. How do we do this then?
Thanks for your time.
Regards,

Tom Kyte
July 28, 2002 - 3:39 pm UTC

I might use a 3rd value to indicate "Hey, I'm working on these"

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set picked_up = 'XXX' where picked_up = 'NO';

3 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2
  2  select field_code, sum(value) from t where picked_up = 'XXX' group by field_code;

2 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set picked_up = 'YES' where picked_up = 'XXX';

3 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

 

Thanks

A reader, July 29, 2002 - 7:09 am UTC

Thanks Tom, great help.
I think we just need to, reset picked_up=null, as well in the last update.

what is the difference

dada, August 19, 2002 - 5:10 pm UTC

why do I get following error ???? what is the difference why I cannot use for update?
thank you in advance for your help

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  cursor c is
  3  select * from equipment_category_lookup for update nowait ;
  4  i number;
  5  begin
  6  for c1 in c loop
  7  update equipment_category_lookup
  8  set equipment_category_lookup_seq = main_seq.nextval
  9  where current of c;
 10  end loop;
 11* end;
SQL> /
where current of c;
                 *
ERROR at line 9:
ORA-06550: line 9, column 18:
PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored


SQL> ed
Wrote file afiedt.buf

  1  declare
  2  cursor c is
  3  select * from equipment_category_lookup for update ;
  4  i number;
  5  begin
  6  for c1 in c loop
  7  update equipment_category_lookup
  8  set equipment_category_lookup_seq = main_seq.nextval
  9  where current of c;
 10  end loop;
 11* end;
SQL> /

PL/SQL procedure successfully completed. 

Tom Kyte
August 19, 2002 - 7:16 pm UTC

Looks like it wanted an "of something" for the nowait option there.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2  cursor c is
  3  select * from emp for <b>update of sal</b> nowait ;
  4  i number;
  5  begin
  6  for c1 in c loop
  7  update emp
  8  set sal = main_seq.nextval
  9  where current of c;
 10  end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed. 

Select For Update with multiple processes

reader, December 18, 2002 - 4:02 pm UTC

Tom,
Just want to confirm the following :
I have 4 identical (same code) processes running. Assume, at a point in execution all of them issue the following :

select c1,c2,c3
from t1
where c2=nvl(rtrim(somevalue))
for update


My understanding is that the 'lucky' process will lock the whole table for the duration that it needs to do the update, while all other will wait. The reason being use of function in the where predicate, since it will do the full scan and hence lock the whole table.

Is this understanding correct?

Thanks.

Tom Kyte
December 18, 2002 - 4:34 pm UTC

not the whole table, just the rows that meet the criteria. regardless of the access path.

but in this case you have:

where DATABASE_COLUMN = constant


(forget the functions - the value is a constant). So, index range scan = OK.

It is when you have:

where f(DATABASE_COLUMN) = value

or

where DATABASE_COLUMN = variant(say another column in the table)


that the index range scan is not going to be used.

FOR UPDATE

reader, December 19, 2002 - 10:26 am UTC

Tom,
The function in the query act upon 'bind variables', i am assuming this is also treated as a constant.

In your explanation you refered to variant(database column), what do u mean by variant?

Thanks.

Tom Kyte
December 19, 2002 - 10:31 am UTC

meaning the value varies from row to row.


select * from t where column1 = column2;

there -- column2 is "variable" when compared to column1. The value for column2 must be evaluated for each row.

as opposed to


select * from t where column1 = 5;



for update nowait

G.V.N., April 09, 2003 - 8:35 am UTC

2 sessions are trying to update emp table as shown below

Session:1 - Time:T1
select empno, ename, job from emp where empno=7369 for update nowait;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK

Session:2 - Time:T2
SQL> update emp set ename='SMITH2' where empno=7369;
--nothing happens here since session 1 is holding the lock.

Session:1 - Time:T3
SQL> update emp set ename='SMITH1' where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

Session:2 - Time:T4

1 row updated.
As soon as we issue a commit in session:1 The update from 'Session:2 - Time:T2' gets done, after which 'Session:2 - Time:T4' issues a commit.

Now - either sessions would give the following result

SQL> select empno, ename, job from emp where empno=7369;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH2     CLERK


Isn't this a case of lost update ?(not every transcation was using a for update nowait)


 

Tom Kyte
April 09, 2003 - 10:27 am UTC

yes it is -- your application code is incorrect. One of the guys did it right -- they did a select for update, assuring them the row "is theirs". The other guy did it wrong, they blindly updated the data without checking to see if it was modified since they read it out.

Ok, but why nowait requires column?

ninoslav, February 06, 2004 - 2:09 pm UTC

Hi Tom, i still didn't understand why we have to put a column in case we wanna use nowait:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 cursor c is
3 select * from emp for update of sal nowait ; <==


Tom Kyte
February 07, 2004 - 1:44 pm UTC

because, without it, it was not working...

in 9i, it works either way. must have been an 8i issue that was resolved with the common sql parser in 9i

SELECT privilege without FOR UPDATE

Sami, March 02, 2004 - 9:42 pm UTC

Dear Tom,

We have given SELECT rights to many developers who are developing reports. Few of them uses SELECT ..FOR UPDATE in there code which is holding the lock for unknown reason.

Is there a way to give SELECT privilege with out FOR UPDATE capability?

Tom Kyte
March 03, 2004 - 9:10 am UTC

well, it is not "unknow" why it is holding the lock, they've ASKED for it.

They are not allowed to update, but that does not prevent them from explicitly locking data upon request and they requested it. In fact, they could even "lock table" if they like.

they have a bug in their code, they need to correct such a bug and understand when to actually use "for update". sorry, select allows you to lock.

Excellent!

Reader, March 03, 2004 - 12:08 pm UTC

Great information. I always wondered when one should use the select for udate.

How to unlock a table

Anand, March 23, 2004 - 3:08 pm UTC

Tom,

I issue a command :

LOCK TABLE a IN ROW SHARE MODE NOWAIT;

How do I unlock the table ? What is the command for that ? I searched for UNLOCK TABLE or RELEASE TABLE commands but to no avail.

Thanks for your help

Tom Kyte
March 24, 2004 - 8:03 am UTC

commit;

rollback;


thats the only way to release locks gained by SQL like that. you "grow" locks in your transaction and you give them all up when the transaction finishes.

select and update selected

A reader, October 14, 2004 - 2:30 pm UTC

Hi Tom
How can we select data from a table and then update status of those selected? It's oracle 9.2.
Thanks!


Tom Kyte
October 14, 2004 - 7:42 pm UTC

ummm,

select
followed by
update

???

A reader, October 14, 2004 - 2:39 pm UTC

Hi Tom,
the question was:
How can we return a result set and update status of those rows returned in a subset indicated they were processed?
Thanks!


Tom Kyte
October 14, 2004 - 7:44 pm UTC

yes, use "update"?

select out the rowid, select out the primary key, select out something that you can use the identify the rows. then update them using that key

Select for update and delete. ORA-1002.

Joan Vidal, November 11, 2004 - 10:18 am UTC

I Tom,

I Have this PL/SQL in ORACLE 10g (Red Had)....

DECLARE
INSERT_COUNT NUMBER := 1;
CURSOR c1 IS SELECT field1,field2 FROM TABLA WHERE field_PK in (SELECT field_PK FROM TRASPAS) FOR UPDATE;
BEGIN
FOR r1 IN c1 LOOP
INSERT /*+ APPEND */ INTO other table...
DELETE FROM TABLA WHERE CURRENT OF c1;

>>>>> WITH a counter I have commit every 1000 inserts to avoid large rollback segments.

END LOOP;
COMMIT;
END

When it is executed the COMMIT I obtain ORA-1002.
I don't understand because the cursor finishes.

Thanks,
Joan


Tom Kyte
November 11, 2004 - 10:39 am UTC

oh, cool -- 1555 is going to be your very very next issue!! neat. (you do understand you NEED that undo in order for your query to run to completion -- that stuff you are trying to minimize? you actually *need it*)


geez, just size your system appropriately, erase your code, and

insert /*+ append */ into other table select * from tablea;
delete from ....;
commit;

the for update did it -- you lost your locks.

but there isn't any reason on the planet this should be more than three lines of code.



Tom I have a question on a home work assigmen

Brenda Tinker, March 17, 2005 - 4:44 am UTC

I am trying to update them mm_movie table with the name STK_FLAG that will hold a value * if the sotck is $75 or more. Otherwise a value should be Null. I need to add the column and create a anonymous block that contains a CURSOR FOR LOOP to accomplish this task. could you help me please??
Here is my sql

DECLARE
CURSOR nmovie_qty IS
Select value, price
from mm_movie
FOR UPDATE NOWAIT;
lv_stock mm_movie.stk_flag%TYPE;
BEGIN
FOR rec_prod IN nmovie_qty LOOP
IF rec_prod.value >= 75 THEN lv_stock :=
rec.prod.price '*';
END IF;
IF rec_prod.vlaue < 75 THEN lv_stock :=
rec.prod.price null;
END IF;
UPDATE mm_movie
SET stk_flag = lv_stock
WHERE CURRENT OF >= 75 then '*';
END LOOP;
COMMIT;
END;
/
SHOW ERROR

Tom Kyte
March 17, 2005 - 8:43 am UTC

this should not under ANY circumstances be done in code (i hope your teacher is not making you do this procedurally, if so, they are doing a great disservice to everyone).

Actually, this update is somewhat flawed in concept, stk_flag sounds like it should be a derived column -- not a real column:

create view v
as
select x.*, case when value >= 75 then '*' end stk_flag
from mm_movie x;


but the update would be

update mm_movie
set stk_flag = case when value >= 75 then '*' end
/


the view is the right answer.

the update works but the data is immediately "questionable" since I could come along and upadte the value to 80 from 50 and the stk_flag isn't maintained

the procedural code is just wrong in its approach.

Need your Opinion for this...

Muhammad Riaz Shahid, March 20, 2005 - 5:27 am UTC

Hello Tom!

Developers within my team are using something like:

declare
cursor c1 is select null From t1 for update nowait
where a=:1
and b>=(select max(b) from t1 where a=:1);
c1_rec c1%rowtype;
begin
open c1;
fetch c1 into c1_Rec;
close c1;
delete t1
where a=:1
and b>=(select max(b) from t1 where a=:1);
end;

Shouldn't it be as simple as :

delete t1
where a=:1
and b>=(select max(b) from t1 where a=:1);
???

Thanks in Advance

Tom Kyte
March 20, 2005 - 7:44 am UTC

well, it looks like they may have been trying to do a non-blocking delete -- but there they should have used "delete from t1 where current of cursor"


Unless A,B is unique in T1, they might not be deleting just the row(s) they select.


So, some semantics missing here (is a,b unique -- did they purposely do this to have a non-blocking delete)....

but at the very least,

create or replace procedure p( p_a in number )
as
cursor c is
select *
from t
where a = p_a
and b = (select max(b) from t where a = p_a)
for update nowait;
l_data c%rowtype;
begin
open c;
fetch c into l_data;
if ( c%found )
then
delete from t where current of c;
end if;
close c;
end;
/


for unless they have c%isfound, they are deleting "nothing" or maybe something -- since they may or may not be deleting that which they fetched if A,B are not unique.


but in general, it would seem the single delete would suffice.

Confirmation

Muhmmad Riaz Shahid, March 21, 2005 - 12:09 am UTC

Thanks for your prompt reply. I couldn't understand
"Unless A,B is unique in T1, they might not be deleting just the row(s) they select"...

A,B is not unqiue in t1. May i request to give some test case to prove the above?

Tom Kyte
March 21, 2005 - 10:11 am UTC

insert into t (a,b) values (1,1);
commit;


Now, you start your delete, you lock that row, you get pre-empted and I issue:

insert into t (a,b) values (1,1);
commit;

Now you start again and you delete both rows.

(just think about it -- if (a,b) is not unique...... there can be more than one, they lock ONE of them and delete them ALL.

More info....

Muhammad Riaz Shahid, March 21, 2005 - 7:25 am UTC

In continuation to above comments:

In fact, the developers are making sure that no one can access the data during the operation. So can the something following be helpfull ?

SQL> declare 
  2  cursor c1 is select null From scott.emp where deptno=10 for update nowait;
  3  begin
  4  open c1;
  5  dbms_lock.sleep(15); --pause processing so that we can check the lock held from v$lock.
  6  update scott.emp
  7  set deptno=20
  8  where deptno=10;
  9  dbms_lock.sleep(15);
 10  close c1;
 11  end;
 12  /

PL/SQL procedure successfully completed. 

Tom Kyte
March 21, 2005 - 10:29 am UTC

sorry -- this does not make sense to me.

SELECT FOR UPDATE cursors

Greg Loring, March 31, 2005 - 10:00 pm UTC

I was planning on adding "optimistic" locking to a persistance framework, but needed to understand SELECT FOR UPDATE. Select row locking is considered evil at my workplace because so many people mess it up. I have learned all of my SQL on the fly and primarily from DBAs who didn't want me to know this, so I was utterly clueless. I feel anything but clueless now.

Tom Kyte
March 31, 2005 - 10:19 pm UTC

excellent, that is about the nicest thing anyone could say.

Thank you.

sarah-jane booth, April 06, 2005 - 6:30 pm UTC

i need it plz

ora-01002 in oracle 7.3.4

Mary, May 14, 2005 - 8:16 pm UTC


Yo estoy conectandome desde una aplicaciĆ³n .Net con C# a una base de datos Oracle 7.4.3 mediante el oledb provider que viene dentro del oracle Data provider for .Net 9.2...,

string conecction = Provider=oraoledb.oracle; usr id =xx; password=yy; datasource=zz; oledb.net=true;"

oledbconnection con= new oledbconnection()
con = conecction

oledbadapter command = new oledbAdapter
command = new oledbadapter("select * from producto", con)
command.fill(dataset)
......

esto es parte de la coneccion pero tengo el error ora-01002 fetch out of sequence

Por favor si alguien ha tenidoo un problema simular su ayuda.


very nice

flyer chen, June 23, 2005 - 2:09 am UTC

Thanks tom so patient.


Select for update vs. select for update nowait

Danny Chen, July 18, 2005 - 3:11 pm UTC

Tom, could you please explain the deference between "select for update" and "select for update nowait"? Could you use an example to show under what circumstance should "select for update nowait" should be used?

Tom Kyte
July 18, 2005 - 4:01 pm UTC

select for update will wait for the row if it is locked

select for update nowait will not.


nowait is generally used in interactive applications so the application can report back "sorry, row locked, try later"

Splendid Explanation

Vikram, July 19, 2005 - 4:46 am UTC

That was Excellent work Tom !!! very useful to me especially because all was explained in layman's language!!! very easy to understand !!!
Please keep up the good work...

Regards,
Vikram

Reference Cursor update

kham, August 15, 2005 - 7:46 pm UTC

I need to return a reference to a cursor from a stored procedure. I was using a REF CURSOR as an OUT parameter in my stored proc, which was working fine. I just "Open oCUR for Select... from ... WHERE" and all the rows would come back to my Java app perfectly.

But now I have to modify those selected rows in the stored proc before I send it back to the Java app. What's the best way to do that? Can I loop through the rows of a REF CURSOR the way I can through a normal cursor and update values in each row before sending it back as an OUT parameter? I'm such a newbie... Thanks in advance.

Tom Kyte
August 16, 2005 - 10:33 am UTC

tell me what you need to "do" to them.

And tell me why this modification cannot be done in SQL???

(eg, simple example -- "i need the column X in upper case" -- great, select upper(x) from ....)




Transactions not being deleted

Keith DuAime, September 28, 2005 - 5:14 pm UTC

I am using a global temporary table to store results of a complex query and then using a cursor for update with this table and other corresponding tables. I use a for ... in and after the loop issue COMMIT; but the rows still exists. I will create an example if need be but this is a production problem and would like to resolve it. thx ahead of time.

Tom Kyte
September 28, 2005 - 5:41 pm UTC

you'll need to create a small example

I don't know what you mean by "the rows still exist" unless you mean in the global temporary table - and that would mean you created it with

on commit PRESERVE rows

instead of

on commit DELETE rows.

Example of for update to delete doesn't work

Keith DuAime, September 29, 2005 - 11:02 am UTC

/*
Delete and report all values from a table that exist in b
*/
create table a(
f1 varchar2(10),
f2 varchar2(10)
);
insert into a values('val1', 'info1');
insert into a values('val2', 'info2');
insert into a values('val3', 'info3');
insert into a values('val4', 'info4');
create table b as select * from a;
delete from b where f1 = 'val3';
--Temporary for reporting purposes
create global temporary table gtt(
f1 varchar(10),
f2 varchar(10)
) on commit preserve rows;

create or replace package for_update_delete as
type rec is ref cursor return gtt%rowtype;
end;
/

create or replace procedure p_for_up_del(
c out for_update_delete.rec
)
as
cursor cDelete is
select a.* from a, gtt where a.f1 = gtt.f1 for update;
begin
delete from gtt;

insert into gtt select * from b;
for rDelete in cDelete
loop
--This is a delete of the a table!
delete a
where current of cDelete;
end loop;
commit;
open c for
select * from gtt;
end;
/

set serveroutput on;
declare
c for_update_delete.rec;
s1 varchar2(10);
s2 varchar2(10);
begin
p_for_up_del(c);
loop
fetch c into s1, s2;
exit when c%notfound;
dbms_output.put_line(s1);
end loop;
end;
/

commit;

-- values exist!
select * from a;


drop table a;
drop table b;
--Doesn't Drop! Have to log out then back in, in order to drop this table!
drop table gtt;
drop package for_update_delete;
drop procedure p_for_up_del;
--disconnect;
--connect system/manager@instance;
--drop table gtt;
--exit;


Tom Kyte
September 30, 2005 - 8:15 am UTC

it is ambigous what table should be "deleted" from here.  when using "where current of", I would recommend selecting from a single table only, that removes all ambiguity.

ops$tkyte@ORA10G> create or replace procedure p_for_up_del(
  2      c out for_update_delete.rec
  3  )
  4  as
  5      cursor cDelete is
  6          select a.* from a where f1 in ( select f1 from gtt ) for update;
  7  begin
  8      delete from gtt;
  9
 10      insert into gtt select * from b;
 11      for rDelete in cDelete
 12      loop
 13          --This is a delete of the a table!
 14          delete a
 15          where current of cDelete;
 16      end loop;
 17      commit;
 18      open c for
 19          select * from gtt;
 20  end;
 21  /
 
Procedure created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set serveroutput on;
ops$tkyte@ORA10G> declare
  2      c for_update_delete.rec;
  3      s1 varchar2(10);
  4      s2 varchar2(10);
  5  begin
  6      p_for_up_del(c);
  7      loop
  8          fetch c into s1, s2;
  9          exit when c%notfound;
 10          dbms_output.put_line( '-->> ' || s1);
 11      end loop;
 12  end;
 13  /
-->> val1
-->> val2
-->> val4
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> -- values exist!
ops$tkyte@ORA10G> select * from a;
 
F1         F2
---------- ----------
val3       info3
 
ops$tkyte@ORA10G> select * from gtt;
 
F1         F2
---------- ----------
val1       info1
val2       info2
val4       info4
 
 

Thank You

Keith DuAime, October 12, 2005 - 9:40 am UTC

I was also thinking of the in clause but was a little concerned about performance. I also thought that since I select a.* Oracle would have realized which table to delete from. Either way results are results. Thanks again!

User for Resource busy !!!!!!

Rai, August 09, 2006 - 6:12 am UTC

Hi,
Thanks for above discussion.
I have a further query related to above discussion :->
User1 has already locked the row(s) .
User2 is getting following error while he is trying to select the same row(s)
ORA-00054: resource busy and acquire with NOWAIT specified.

Could you please let me know how to find the name of user who is using the resource(here user1) when we are connected with user2?.i.e, i wanted to know if i am connected with user 2 name of user1 who is using the resource?

Tom Kyte
August 09, 2006 - 10:45 am UTC

you need a third session to figure that out.

All you can do is generate a list of possible users from v$lock.

In order to see who is blocking you specifically, you need to be blocked, once you are not blocked - that information isn't there anymore.




User for Resource Busy!!!

Rai, August 10, 2006 - 6:50 am UTC

Thanks for quick reply.
But I am unable to understand the which field is telling me about the user.

SQL> desc v$lock;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 ADDR                                               RAW(4)
 KADDR                                              RAW(4)
 SID                                                NUMBER
 TYPE                                               VARCHAR2(2)
 ID1                                                NUMBER
 ID2                                                NUMBER
 LMODE                                              NUMBER
 REQUEST                                            NUMBER
 CTIME                                              NUMBER
 BLOCK                                              NUMBER

Please clear it. 

Tom Kyte
August 10, 2006 - 9:31 am UTC

SID points to v$session

User for Busy Resource

Rai, August 10, 2006 - 6:59 am UTC

Hi Tom,

Got the correct name by which we can get the user's name :-
We can use V$LOCKED_OBJECT

Thanks

Need help on SELECT FOR UPDATE usage please

Asim Kumar, January 18, 2007 - 10:12 pm UTC

Hi Tom,

I was reading your answers in this thread,but still need some suggestion from you on my problem below.

I am currently facing one problem to decide which one should I use out of these below three options, I need to resolve this as soon
as possible -
Ok, let me tell you my requirement first, I have a requirement as below ,
- having a main proc which gets called for each record to insert into one table.And this same proc will be called for almost couple of millon records daily.

- in this main proc, I call another FUNCTION which gets one unique number called ID from a table(this table has three columns - ID, STATUS, ASSIGN_DT and will have around 2 million IDs created and loaded with status and assign_dt as null, ID has a primary key on this table).
So in this function my requirement is, to get a unique ID whose status is NOT "A"(Assigned) and once you get the ID, update that record with "STATUS" as "A" and "ASSIGN_DT" as sysdate, so that no other process can use this ID anymore.


Now these are three approaches I am trying but not able to really decide which one will be most efficient -


I am using PRAGMA AUTONOMOUS_TRANSACTION as I want to commit this transaction so that lock will be released
from the record caused by SELECT FOR UPDATE for 2 and 3.

1)

CREATE FUNCTION GETID
RETURN VARCHAR2

V_ID varchar2(16);

BEGIN
UPDATE ID_ASSIGN
SET STATUS ='A',
ASSIGN_DT = SYSDATE
WHERE STATUS IS NULL AND ROWNUM <2 RETURNING ID INTO V_ID;

RETURN V_ID;
END;


Do I need to put PRAGMA AUTONOMOUS_TRANSACTION here also????
Will it take care parallel processes like getting unique IDs from the table above? i.e, will there be a chance that two processes
will try to access the same ID?

2)

CREATE FUNCTION GETID
RETURN VARCHAR2

PRAGMA AUTONOMOUS_TRANSACTION;
V_ID varchar2(16);

BEGIN
SELECT ID INTO V_ID FROM ID_ASSIGN WHERE STATUS IS NULL AND ROWNUM <2;

UPDATE ID_ASSIGN
SET STATUS ='A',
ASSIGN_DT = SYSDATE
WHERE ID = V_ID;

COMMIT;

RETURN V_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;



3)
CREATE FUNCTION GETID
RETURN VARCHAR2

PRAGMA AUTONOMOUS_TRANSACTION;
V_ID varchar2(16);
CURSOR c1 IS SELECT ID FROM ID_ASSIGN WHERE STATUS IS NULL AND ROWNUM <2 FOR UPDATE;

BEGIN

OPEN c1;
FETCH c1 INTO V_ID;

IF c1%NOTFOUND IS NULL or C1%NOTFOUND THEN
CLOSE c1;
ROLLBACK;
RETURN NULL;
END IF;

UPDATE ID_ASSIGN
SET STATUS ='A',
ASSIGN_DT = SYSDATE
WHERE CURRENT OF c1;

COMMIT;

RETURN V_ID;
END;

I thought of this 3rd FUNCTION just to avoid the above error handling part in 2nd function.



Could you please tell me which one could be more efficient and WHY?
And will there be any suggestion as the main proc which will call this GETID function will be called almost
for couple of million records daily.

Multiple processes can run in parallel for the main proc.
So will there be any possibility in any of the approaches that there could be a lock for the same record between two processes.


Thank you in advance.

Asim


A reader, February 18, 2007 - 10:25 am UTC

Tom,
What does Oracle do when it is running a query that chooses a large number of rows with "Select .. for update" and finds one of the rows has it is interested in has been deleted and commited.

1) Does it do something like a "write consistency", that is re-run the whole query again, to get it consistent

2) It still locks the non existant row somehow?

Thanks

Ravi
Tom Kyte
February 18, 2007 - 12:12 pm UTC

1) absolutely. select for update is just like an update - use read consistency to find the rows of interest as of the point in time the query began, as it finds the rows - get them in current mode and make sure they are still of interest and lock them. If they were of interest but are not of interest anymore after getting them in current mode - then restart.

2) what non-existent row?

A reader, February 19, 2007 - 6:47 am UTC

From the previous posting,

Presumably, a normal "Select " would never do a restart, say

Scenario 1

"Cursor C1 is select * from million_row_output" could already be Fetching back to the client when it discovers the millionth row had been deleted and would still bring it back the deleted row from the undo blocks.

Scenario 2

But the "Cursor C1 is select * from million_row_output
for update of col1" would NOT be fetching to the client anything till it has locked ALL rows, which means it can restart the query if it finds the millionth row had been deleted (and in the restarted version brings 999999 rows, if it can lock them all (or restart if something had changed again)?).

So, Scenario 1 does NOT restart, scenario 2 restarts?

Thanks

Ravi
Tom Kyte
February 19, 2007 - 9:29 am UTC

restarts only happen during modifications/locking.

1 - no restart
2 - restart possible

Select Table and then update table

Javier, February 24, 2007 - 9:41 am UTC

My ETL process is taking too much time and I want to now if I need to use a FOR UPDATE or an aditional sentence

DECLARE
CURSOR c is SELECT DISTINCT(C1) FROM t1 ;
BEGIN
FOR r in c LOOP
SELECT t2.c2
INTO v
FROM t2
WHERE t2.c1= c.c1 ;

UPDATE t1
SET = v
WHERE t1.c1 = c.c1

COMMIT ; -- actually commiting each 1000 rows
END LOOP;
END;

Tom Kyte
February 26, 2007 - 1:06 pm UTC

how about a tiny bit of syntactically correct code?

it looks like you MIGHT be updating T1 from T2

if so,


update (select t2.c2 v, t1.something 
          from t1, t2
         where t2.c1 = t1.c1)
  set something = v;



is all the code in the world you need, want or desire.



UPDATE...RETURNING INTO....

Asim, July 02, 2007 - 11:00 am UTC

Hi Tom,
This is what we are facing a problem now -
We have a table - ID_ASSIGN. table structure is -

CREATE TABLE ID_ASSIGN
(
ID CHAR(16 BYTE),
STATUS CHAR(1 BYTE) DEFAULT 'A',
ASSIGN_DT TIMESTAMP(6)
);

This table is hash partitioned on ID column.
There is a primary key on ID column.

There is a B-Tree index on status column too.

Now things is , this is the bit of code which gets executed for each record to assign an ID -
=======================================
PROCEDURE CREATEID(P_ID OUT VARCHAR2, P_STATUS OUT VARCHAR2, P_ERROR_CODE OUT VARCHAR2,P_ERROR_MSG OUT VARCHAR2)
IS

PRAGMA AUTONOMOUS_TRANSACTION;

V_ID VARCHAR2(16);
V_ERROR_MSG VARCHAR2(300) := 'No ID is available for assignment';
XAPPERROR EXCEPTION;

BEGIN

UPDATE /*+ INDEX(ID_ASSIGN X1ID_ASSIGN)*/ ID_ASSIGN
SET STATUS = 'U',
ASSIGN_DT = CURRENT_TIMESTAMP
WHERE status = 'A'
AND ROWNUM = 1
RETURNING ID INTO V_ID;

IF SQL%ROWCOUNT = 0 OR V_ID IS NULL THEN
RAISE XAPPERROR;
END IF;

P_ID := V_ID;
P_STATUS := '0';
P_ERROR_CODE := NULL;
P_ERROR_MSG := NULL;

COMMIT;

EXCEPTION
WHEN XAPPERROR THEN
P_ID := NULL;
P_STATUS := '1';
P_ERROR_CODE := '-01403';
P_ERROR_MSG := V_ERROR_MSG ;
ROLLBACK;
WHEN OTHERS THEN
P_ID := NULL;
P_STATUS := '1';
DECLARE
V_SQLCODE NUMBER := SQLCODE;
V_SQL_MSG VARCHAR(512) := REPLACE(SQLERRM, CHR(10), ',');
BEGIN
P_ERROR_CODE := V_SQLCODE;
P_ERROR_MSG := V_SQL_MSG;
ROLLBACK;
END;
END;
=======================================

The table has 100 million IDs now - with 45 million available and 55 million unavailable.

What happened is , as long as the number of available ids were more than number of unavailable, the load was happening at a rate of 329 recs/second but when number of available ids went down compared to number of unavailable ids, the ID generation speed came down to 86 recs/second.

I am expecting it is due to less number of available ids compared to number of unavailable ids.

Why do you think this is happening?
Running database stats did not help here as we are already forcing index scan.


Thanks,
Asim








Tom Kyte
July 03, 2007 - 9:55 am UTC

oh man, this stuff makes me want to cry.

just makes me want to cry.

I'm surprised this super serial, super slow, really bad idea of a stored procedure runs that fast.

kills me to see people do this to a database. Just kills me.





Update....returning into

Asim, July 03, 2007 - 10:18 am UTC

Hi Tom,

I know you can't see this.
But sometime back as I explained you in this thread, my management wants it to be done like this as they don't want to generate IDs from oracle sequence. They are getting those IDs from third party system and keeping it in a table.
So don't know what exactly we can do better here.

That is the reason I had to come back to you.

Thanks,
Asim


Tom Kyte
July 03, 2007 - 10:41 am UTC

you are doomed to be slow, your management has made that choice for you.

you are a serial process.
you commit everytime (that is a big old wait).

the way you deal with error conditions frightens me (oh, to be able to get rid of when others - return codes are SO 1970's)

one thing that MIGHT help would be a global index on:

create index on table( case when status = 'A' then 'A' else null end );


and

  UPDATE ID_ASSIGN
  SET STATUS = 'U',
  ASSIGN_DT = CURRENT_TIMESTAMP
  WHERE (case when status = 'A' then 'A' else null end) = 'A'
  AND ROWNUM = 1
  RETURNING ID INTO V_ID; 


you'll have an index that is ONLY on the 'A' values (because we do not index entirely null index key entries, so when you update 'A' to 'U' it'll remove itself from the index).

that'll be about it, other than USE EXCEPTIONS, stop using when others. please

UPDATE .. returning into

Asim, July 03, 2007 - 10:59 am UTC

Hi Tom,
Thanks for your suggestion.
And also you don't want me to use the INDEX hint on the update query along with the other changes???

Regards,
Asim
Tom Kyte
July 03, 2007 - 11:10 am UTC

the hint would not be necessary. the optimizer will 'see it' and do the right thing.

A workaround for management imposed process

Nick Pierpoint, July 03, 2007 - 11:41 am UTC

Oh dear. Perhaps you should look at putting a layer between the externally generated IDs and the IDs used in your application:
create table internal_external_id_link (
    internal_id integer
    external_ID CHAR(16 BYTE)
); 

Pre-populate the table with the 100 million records kindly provided by your management then you'd be able to use a sequence to generate the internal_id during the load process and you wouldn't be stuck with a serial process.

How to lock rows fetched by inline view

Ram, November 20, 2008 - 8:25 am UTC

Hi Tom,
I want to lock rows fetched by inline view used in cursor. I am doing something like this -
SQL> SELECT        empno, ename, sal
  2  FROM          (SELECT   empno, ename, sal
  3                 FROM     emp
  4                 ORDER BY empno)
  5  WHERE         ROWNUM < 1000
  6  FOR UPDATE OF sal NOWAIT;
FROM          (SELECT   empno, ename, sal
              *
ERROR at line 2:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

But getting error ORA-02014.
I can do it like --
SQL> select empno,ename,sal
  2  from emp
  3  where empno in (SELECT        empno
  4  FROM          (SELECT   empno
  5                 FROM     emp
  6                 ORDER BY empno)
  7  WHERE         ROWNUM < 1000)
  8  order by empno
  9  for update of sal nowait;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

But If I want to lock rows getting selected in Inline view then what should i do ?
What should be the approch?
Please suggest.
Tom Kyte
November 24, 2008 - 1:07 pm UTC

you'll have to do it the way you are in the second case, but you might consider using rownum instead to avoid the index...

where rowid in ( select rid from (select rowid rid from emp...) where rownum<..)


queue of sessions with SELECT FOR UPDATE

Pedro Tablas Sanchez, October 23, 2010 - 7:09 pm UTC

Hi TOM

exchange for a (select for update)?

if I need to lock the table while completing a transaction.

my problem is that queue of sessions and concerns included the performance of the database, if I use (SELECT FOR UPDATE).


Greetings.

Tom Kyte
October 25, 2010 - 5:39 pm UTC

I'd need a bit more information - an example perhaps, a description of what you are doing and what you would like to do.

I don't know if a select for update could replace your lock table with the supplied information.

for update nowait

A reader, February 05, 2011 - 9:37 am UTC

Hi Tom,

I have couple of queries regarding locking a row after examining my table EMP:

1) I issued "select ename,sal from emp where empno=1116 for update nowait" from session 1. When I try to update the same row of empno=1116 ("update emp set sal=2000 where empno=1116")from another session 2, I get my statement hanged until I commit the first session.
I expected that since NOWAIT is specified then if the session 1 is not aquiring the lock then transaction of session 2 will commence. Can you please help me understand this?
2) If I execute the statement from both the sessions: "select emno,sal from emp where empno=1116 for update nowait", I get the transaction from each session hangs periodically until opposite session commits. Why is that so? Is "for update nowait" clause valid for the entire session?
3) I am not able to differentiate the "for update nowait" clause with "for update" clause for the above two cases. I know they are different but why I couldn't do?
Tom Kyte
February 06, 2011 - 12:19 pm UTC

1) nowait is for YOUR SELECT - not for anything else.

when you select for update nowait - you are saying "I would like this select to NOT WAIT". You are not saying "make it so no one else waits for this"

If you wanted the update to not wait you would have selected the read for update nowait in session 2 to make sure the update will work.

2) no you don't. give an example if you think you do. Tell us how to reproduce the findings

3) you did something wrong, post a FULL complete example and tell us what you did step by step

Just like I always do

Here, run this script:

set echo on

drop table t;
create table t as select * from scott.emp;
select empno, sal from t where empno = 7499 for update nowait;

set echo off
prompt in another session execute:
prompt select empno, sal from t where empno = 7499 for update nowait;;
prompt and it will not block
set echo on


I did and in session 2 I get:

ops$tkyte%ORA11GR2> select empno, sal from t where empno = 7499 for update nowait;
select empno, sal from t where empno = 7499 for update nowait
                       *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




immediately show it works.

Select for update

William, May 09, 2011 - 11:12 am UTC

Hi Sir,

I have the following issue in Oracle Version 10.2.0.3.0:

An application does this query before an insert:

SELECT ID_OBJECT
FROM M4RDC_AUTONUM_LOCK
WHERE 1 = 1 AND ID_OBJECT = :V001 AND ID_FIELD = :V002 FOR UPDATE;

The insert:

INSERT INTO M4SCO_APPLY_PERIOD (DT_LAST_UPDATE, EX_TYPE, ID_SECUSER, ID_APPROLE, ID_CURRENCY, ID_DMD_COMPONENT, ID_M4_TYPE, ID_ORGANIZATION, ID_PRIORITY, SCO_CHANGE_REASON, SCO_DT_ALLOCATION, SCO_DT_END_SLICE, SCO_DT_EXCHANGE, SCO_DT_PAYMENT, SCO_DT_START_SLICE, SCO_ID_HR, SOURCE_NODE, SCO_PAY_FREQ_ALLOC, SCO_PAY_FREQ_PAYM, SCO_VALUE, SCO_OR_HR_PERIOD) VALUES (:V001, :V002, :V003, :V004, :V005, :V006, :V007, :V008, :V009, :V010, :V011, :V012, :V013, :V014, :V015, :V016, :V017, :V018, :V019, :V020, :V021);

Insert execution plan:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 154 | 1 (0)|
--------------------------------------------------------------

All the queries are executed by a store procedure, it works for a 4000 records well, but suddenly start the select to lock each other and appears a sequencial data file read.

Sometimes the process works well.

Can you suggest us where to start look for?

If you need more information, just ask for.

Best regards,
Tom Kyte
May 09, 2011 - 11:27 am UTC

with bind names like that, those don't look like they are in a stored procedure.

But in any case, if the select for updates in multiple sessions start blocking eachother - the answer to "why" is pretty darn straightforward isn't it?

They are selecting for update the same data - application design issue, nothing we can do about it.

You'd need to outline the design here. The sql isn't relevant yet, there is a flaw in your approach.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library