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

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,

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;

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:

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.
 

Thanks Tom.

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,

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 Tom, great help.
I think we just need to, reset picked_up=null, as well in the last update.

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. 

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. 

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,
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.

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)


 

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 ; <==

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?

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

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

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

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!

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

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

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

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?

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. 

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.

i need it plz

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.

Thanks tom so patient.

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?

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

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.

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.

/*
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;

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
 
 

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!

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?

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. 

Hi Tom,

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

Thanks

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

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

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

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;

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

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

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

  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; 

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

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.

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.

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.

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?

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

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

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,