Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: July 04, 2002 - 11:48 am UTC

Last updated: July 30, 2012 - 9:51 am UTC

Version: 7.3.4

Viewed 10K+ times! This question is

You Asked

In my PL/SQL code, when I select for update, if anybody else is working & has been locked what I want to update, I want my program to wait for n seconds for the row(s) to be released, if released then continue updating, if still not released after n seconds, detects the user who is locking the resource and informs me that person. I am writing pseudo code like this:

select xxx for update yyy
wait n seconds
message (the user USER is locking the record RECORD)

Thanks.


and we said...

You cannot tell WHO is locking your record

You can only tell WHO MIGHT BE (the entire set of people that might be).

Oracle does not maintain an expensive list of all row locks in the system anywhere. That would inhibit greatly

o scalability
o performance
o concurrency

(as it does in SQLServer and DB2 and others). Locks are an attribute of the data, not stored in a serialized data structure as they do. (if you are interested in the details, you can look at my book -- i go into this i a large amount of depth)


Now, your general logic can be implemented:

declare
resource_busy exception;
pragma exception_init( resource_busy, -54 );
success boolean := False;
begin

for i in 1 .. 3
loop
exit when (success);
begin
select xxx from yyy where .... for update NOWAIT;
success := true;
exception
when resource_busy then
dbms_lock.sleep(1);
end;
end loop;

if ( not success ) then
raise_application_error( -20001, 'row is locked by another session' );
end if;




Rating

  (27 ratings)

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

Comments

Learn a lot, but one question...

jimmy lee, July 05, 2002 - 1:13 am UTC

in above scripts, variable success always true. Because "select ... from ... where... for update nowait" never raise a error "resource busy",just return "0 rows selected";

BTW, "select xxx from yyy where .... for update NOWAIT;"
perhaps should add "execute immediate" as dynamic sql.

It's my pleasure.




Tom Kyte
July 05, 2002 - 10:34 am UTC

Your wrong.  select * from update nowait will raise an ORA-54, resource busy.

That is what NOWAIT does.  Try it and see.

Why use execute immediate?  It is static sql.  sigh....


In one session I execute:

ops$tkyte@ORA817DEV.US.ORACLE.COM> lock table emp in exclusive mode;

Table(s) Locked.

In another, I run:


ops$tkyte@ORA817DEV.US.ORACLE.COM> set echo on
ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      resource_busy   exception;
  3      pragma exception_init( resource_busy, -54 );
  4      success boolean := False;
  5      l_rec   emp%rowtype;
  6  begin
  7  
  8    for i in 1 .. 3
  9    loop
 10       exit when (success);
 11       begin
 12             select * into l_rec from emp where rownum=1 for update NOWAIT;
 13             success := true;
 14       exception
 15             when resource_busy then
 16                 dbms_lock.sleep(1);
 17       end;
 18     end loop;
 19  
 20     if ( not success ) then
 21           raise_application_error( -20001, 'row is locked by another session' );
 22     end if;
 23  end;
 24  /
declare
*
ERROR at line 1:
ORA-20001: row is locked by another session
ORA-06512: at line 21



works as advertised.

 

thanks a lot

jimmy lee, July 05, 2002 - 9:58 pm UTC

thank you for your good answer, I learn a lot from it and your great book "Expert One-On-One". I'm a newcomer for oracle, but I love it.
1.for exclusive lock of full table such as "lock table XXX in exclusive mode" , above scripts work well. another situation, for rows lock as "select XXX from YYY for update nowait", it just return "0 rows selected", no ora-00054 returned. I don't know why.
2. yes,static sql can work well, you say it. but original scritps shoud be "select XXX into ..." instead of "select XXX From YYY ...". now I guess perhaps "XXX" omited "into".
thank you again.

Tom Kyte
July 06, 2002 - 7:54 am UTC

1) no they don't, you are mistaken.  Again, in one session issue:

ops$tkyte@ORA817DEV.US.ORACLE.COM> update emp set ename=ename where empno=7369;     
1 row updated.

Now, goto another session and:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      resource_busy   exception;
  3      pragma exception_init( resource_busy, -54 );
  4      success boolean := False;
  5      l_rec   emp%rowtype;
  6  begin
  7  
  8    for i in 1 .. 3
  9    loop
 10       exit when (success);
 11       begin
 12             select * into l_rec from emp where empno=7369 for update NOWAIT;
 13             success := true;
 14       exception
 15             when resource_busy then
 16                 dbms_lock.sleep(1);
 17       end;
 18     end loop;
 19  
 20     if ( not success ) then
 21           raise_application_error( -20001, 'row is locked by another session' );
 22     end if;
 23  end;
 24  /
declare
*
ERROR at line 1:
ORA-20001: row is locked by another session
ORA-06512: at line 21


again works as advertised.  

It is IMPOSSIBLE for this block to say "0 rows selected".  I am using a select into without a check for NO_DATA_FOUND.  It would through an error at the very least for the failed select into (but it doesn't, it throws a resource busy -- trust me)

If you can prove otherwise, lay out the steps bit by bit and show us -- just like I do.


2) the original script was psuedo code 

Thank you very much and sorry for my mistake.

A reader, July 06, 2002 - 9:47 am UTC

Yes, You're right. I made a mistake. thank you for your patient response.

A way to reduce 'resource busy' aborts.

barbara spiecker, April 23, 2003 - 2:16 pm UTC

This logic will enable me to reduce the number of 'resource busy' aborts when I am re-loading a data mart while others are trying to use it. THanks!

Doug, June 27, 2003 - 1:07 pm UTC

Tom,

What if I have to replace all the code where I want to lock the rows using your above solution ? One way is I can go adding the code around the cursor/select stmt. But what if I have to make this change at 20-30 places ? Is there a generic function that I can write and simply call that ?

Thanks !

Tom Kyte
June 27, 2003 - 1:37 pm UTC

it needs to be done around the queries -- 20/30 is not very many.

Thanks !!!

Doug, June 27, 2003 - 1:42 pm UTC

Thanks again Tom for your help. 20/30 is not much, but could increase to 200/300 .. then what ? ;) So I thought better ask you and do it right the first time !!!

find the locked row

A reader, November 11, 2003 - 6:41 am UTC

Hi

Using Enterprise Manager, Lock Manager we can actually find the rowid of locked rows. However I did a small test now and to be honest the rowid shown in OEM hasnt got much meaning except it shows the file_id correct... Do you know how OEM accomplish this task showing us the locked row rowid?

If OEM can, we can too!?

Tom Kyte
November 11, 2003 - 7:56 am UTC

if you have three sessions (the blocker off somewhere in the world, the blockee, and someone curious) we can do it.   look in v$session... consider if you do this:

ops$tkyte@ORA920> select ename from emp where empno = 7369 for update;
 
ENAME
----------
SMITH
 
ops$tkyte@ORA920> !plus
 
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Nov 11 07:54:49 2003
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
 
ops$tkyte@ORA920> select * from emp where empno = 7369 for update;

<b>that is blocked -- now in yet another session</b>

ops$tkyte@ORA920> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where username = user;
 
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
           -1              0               0             0
        57054             13            1042             0
           -1              0               0             0
 

 
ops$tkyte@ORA920> l
  1  select empno from emp where rowid =
  2* dbms_rowid.rowid_create( 1, 57054, 13, 1042, 0 )
ops$tkyte@ORA920> /
 
     EMPNO
----------
      7369
 
ops$tkyte@ORA920>
 

Will this work for delete statements also?

A reader, April 22, 2004 - 12:17 pm UTC

Hi Tom,
We are using 9.2.0.3. We have a nightly purge job that deletes "old" (based on a criteria) records from several tables. Sometimes a delete statement hangs around, holding some locks. Is it possible to detect this hang and rollback the delete statement if it does NOT complete in x minutes?
Thanks

Tom Kyte
April 23, 2004 - 8:27 am UTC

umm, a delete statement won't "hang around". sure it could get blocked but now you have to ask yourself "why is it getting blocked".

It could certainly do a select for update (with nowait or with a timeout), bulk fetch 100 or so rows at a time and bulk delete -- but I'd be concerned as to why I'm getting blocked on a purge routine (it seems that records to be purged should not be "active", if they are -- something is seriously wrong)

ROW_WAIT_OBJ# in dbms_rowid.rowid_create

sergej, April 10, 2005 - 10:38 am UTC

Hallo Tom,

Object number in call of dbms_rowid.rowid_create is "DATA object number", not "object number". This is all_objects.data_object_id. v$session.ROW_WAIT_OBJ# is all_objects.object_id. If these numbers are not equal then "dbms_rowid.rowid_create" creates invalid rowid.

15:34:00 sys@QUEDEV01> select object_name, object_type, object_id, data_object_id
16:20:07 2 from dba_objects where object_id = 30377;

OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------ ---------- --------------
BIG_TABLE TABLE 30377 30742

We need to use 30742 in call of dbms_rowid.rowid_create.

Thanks!

Tom Kyte
April 10, 2005 - 10:48 am UTC

doh, 100% correct.  Lets see how it happens and how to correct it:

ops$tkyte@ORA9IR2> drop table emp;
Table dropped.
 
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
 
<b>table emp has object_id = data_object_id right now... but:</b>

ops$tkyte@ORA9IR2> truncate table emp;
Table truncated.

<b>the act of truncating/moving the segment will keep the object id but assign a new DATA OBJECT ID</b>
 
ops$tkyte@ORA9IR2> insert into emp select * from scott.emp;
14 rows created.
 
ops$tkyte@ORA9IR2> commit; 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select ename from emp where empno = 7369 for update;
 
ENAME
----------
SMITH
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !echo 'select ename from emp where empno = 7369 for update;' | sqlplus /
 
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Apr 10 09:45:19 2005
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA9IR2>

<b>that second session is now blocked.  In another session we:

ops$tkyte@ORA9IR2> select row_wait_obj#, row_wait_file#, row_wait_block#,
  2  row_wait_row# from v$session where username = user;
 
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
           -1              0               0             0
        40021              6             126             0
           -1              0               0             0
 
ops$tkyte@ORA9IR2> select empno from emp
  2  where rowid = dbms_rowid.rowid_create( 1, 40021, 6, 126, 0 );
select empno from emp
                  *
ERROR at line 1:
ORA-01410: invalid ROWID

<b>that is because 40021 is the wrong number, it would be:</b>

ops$tkyte@ORA9IR2> select empno from emp   2  where rowid = dbms_rowid.rowid_create( 1, (select data_object_id from dba_objects where object_id=40021), 6, 126, 0 );
 
     EMPNO
----------
      7369

<b>insted, thanks!</b>

 

Figure out the table_name?

Andrew, May 24, 2005 - 7:29 pm UTC

Can you figure out the name of the table from the rowid once you get it? If I'm running a big transaction across multiple tables, I'm not sure which table is locked.... how do I find out? Thanks!!!!

RE: Figure out the table_name?

Andrew, May 24, 2005 - 7:40 pm UTC

Doh! The answer was right in front of me. Of course, you have the object_id so just look up the object_name in dba_objects.

Simple way to check who is locking my row

Jean-Marc Desvaux, June 14, 2006 - 3:35 pm UTC

Hi Tom,

Just a small contrib though prev reviews are 3 yrs old.
I just hope there is no similar entry I did not see.
BTW thx a lot Tom for your massive contribution to the Oracle world.

Statement from SYSTEM a/c:

select T1.username||' ( SID='|| T1.sid ||' )' user_blocking_my_rows
from v$lock L1, v$session T1, v$lock L2, v$session T2
where T1.sid=L1.sid and T2.sid=L2.sid
and L1.BLOCK=1 and L2.request > 0
and L1.id1 = L2.id1
and L1.id2 = L2.id2
and T2.username= USER;

SYSTEM can create a read only view + grants and synonyms for any user to execute.

Jean-Marc.

A reader, December 29, 2006 - 5:47 am UTC

Tom,

When I do queries on v$session, I find there are several sessions with row_wait_obj# <> -1 but have positive values, they could be waiting on SYS objects etc, the query I is attached below.
Now could the row_wait_block# ever be 0 if a session is blocked by another transaction? Like does Oracle always number a Block above 0 and could this be used as a definitive parameter (row_wait_block# > 0) to decide if a session is blocked, using v$session alone?



SELECT usr user_waiting, machine, terminal, object_owner, object_name
, CASE
WHEN object_owner IS NOT NULL
AND object_name IS NOT NULL
AND block_number > 0 THEN DBMS_ROWID.rowid_create
( 1
, data_object_number
, relative_fno
, block_number
, ROW_NUMBER )
ELSE NULL
END row_id_waited_on, row_wait_obj#
FROM ( SELECT machine, terminal, usr
, ( SELECT b.owner
FROM dba_objects b
WHERE data_object_number = b.object_id ) object_owner
, ( SELECT b.object_name
FROM dba_objects b
WHERE data_object_number = b.object_id ) object_name
, relative_fno, ROW_NUMBER, block_number, data_object_number
, row_wait_obj#
FROM ( SELECT UPPER ( username ) usr
, ( SELECT a.data_object_id
FROM dba_objects a
WHERE a.object_id =
row_wait_obj# )
data_object_number
, username, program, row_wait_obj#
, row_wait_file# relative_fno
, row_wait_block# block_number
, row_wait_row# ROW_NUMBER, machine, terminal, program
FROM v$session
WHERE row_wait_obj# > 0
AND row_wait_obj# <> -1
AND username =
CASE
WHEN NVL ( UPPER ( '&&input_userid' ), '#' ) =
'#' THEN username
ELSE UPPER ( '&&input_userid' )
END ))
Tom Kyte
December 29, 2006 - 9:49 am UTC

v$lock gives you everything you need to find blocked sessions.

A reader, December 29, 2006 - 9:59 am UTC

Tom
Our DBA's wont give access to v$lock, but to v$session.
And hence checking if V$session could be used.
Thanks
Tom Kyte
December 29, 2006 - 10:06 am UTC

tell them "if you don't let us at v$lock, we'll write really nasty queries against v$session"

look for active sessions waiting for stuff. inactive sessions cannot be blocked.

A reader, December 29, 2006 - 11:17 am UTC

thanks for that, I've used status ='ACTIVE' in my query, now another question, the output of the query brings in some rowid's that bring the 'Invalid Rowid' error.

I've checked the rowid output with
select * from dba_objects where data_object_id =
dbms_rowid.rowid_object('AAAAvpAAXAAAAAAAAA')

And it does say it belongs to the correct object as output by the query, but still brings up Invalid rowid.

Is that a row that has just been deleted and does not exist for querying?

My modified query with stauts ='ACTIVE':

/* Formatted on 29/12/2006 16:08 (Formatter Plus v4.8.7) */
SELECT usr user_waiting, machine, terminal
, object_owner owner_of_object_waited_on
, object_name name_of_object_waited_on
, CASE
WHEN object_owner IS NOT NULL
AND object_name IS NOT NULL
-- AND block_number > 0
THEN DBMS_ROWID.rowid_create
( 1, data_object_number, relative_fno, block_number
, ROW_NUMBER ) -- Get the actual object_name from the
-- rowid using a procedure in dbms_rowid
ELSE NULL
END row_id_waited_on
, row_wait_obj#
FROM ( SELECT machine, terminal, usr
, ( SELECT b.owner
FROM dba_objects b
WHERE data_object_number = b.object_id ) object_owner
, ( SELECT b.object_name
FROM dba_objects b
WHERE data_object_number = b.object_id ) object_name
, relative_fno, ROW_NUMBER, block_number, data_object_number
, row_wait_obj#
FROM ( SELECT UPPER ( username ) usr
, ( SELECT a.data_object_id --- dba_objects.data_object_id and NOT object_id
FROM dba_objects a
WHERE a.object_id =
row_wait_obj# )
data_object_number
, username, program, row_wait_obj#
, row_wait_file# relative_fno --- file number where rowid resides
, row_wait_block# block_number -- block number where rowid resides
, row_wait_row# ROW_NUMBER, machine, terminal, program
FROM v$session
WHERE row_wait_obj# >
0 --- key field will be -1 if session not blocked
AND status =
'ACTIVE' --- only active transactions could be blocked
AND username =
CASE
WHEN NVL ( UPPER ( '&&input_userid' ), '#' ) =
'#' THEN username
ELSE UPPER ( '&&input_userid' )
END ))

Tom Kyte
December 30, 2006 - 8:51 am UTC

it would not be a row that was deleted, no. that would still be a valid rowid.

ops$tkyte%ORA10GR2> column rowid new_val r
ops$tkyte%ORA10GR2> select rowid from t;

ROWID
------------------
AAAPG3AAEAAAANdAAA

ops$tkyte%ORA10GR2> select * from t where rowid = '&R';
old   1: select * from t where rowid = '&R'
new   1: select * from t where rowid = 'AAAPG3AAEAAAANdAAA'

         X
----------
         1

ops$tkyte%ORA10GR2> delete from t;

1 row deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from t where rowid = '&R';
old   1: select * from t where rowid = '&R'
new   1: select * from t where rowid = 'AAAPG3AAEAAAANdAAA'

no rows selected



write a plsql function so you can handle exceptions - send it the information, and return the object name or the rowid that is viwed as "invalid" so you can debug this.

Turning off bold type for the rest of this thread

Stew, December 29, 2006 - 1:23 pm UTC


Now, did that work?
Could there be a formatting "feature" in the new Ask Tom?

If you are using 10g V$SESSION may be enough for this need :)

Tonguc, January 02, 2007 - 11:29 am UTC


More reasons to love V$SESSION after 10g;
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#sthref3986
SELECT sid, blocking_session, blocking_session_status block_status,
username, event, seconds_in_wait siw
FROM v$session
WHERE sid = 154;


SID BLOCKING_SESSION BLOCK_STATUS USERNAME EVENT SIW
154 157 VALID TSUTTON enq: TX - row lock contention 318

Similarly V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY can be used for historical information of sessions.

Prior 10g we needed a join with v$session_wait to get the wait parameters of a session, after 10g they are also located in v$session :)

SELECT sid, state, event, seconds_in_wait siw,
sql_address, sql_hash_value hash_value, p1, p2, p3
FROM v$session
WHERE sid = 154;


I hope this helps,
Best regards.
Tonguc

Find locked row in IOT

David, April 17, 2007 - 12:11 pm UTC

Tom,

In one of your follow-ups above (April 10, 2005) you showed how to access the row data being locked. This works fine for heap tables.

Q. Is there any way to do the same (or similar) if the lock has been taken out on an IOT ?

The ROW_WAIT_OBJ# value in V$SESSION is always -1, which infers that you cannot use the FILE#/BLOCK#/ROW# values to generate a ROWID. Is there another way to get to the row data (PK value) that is locked ?

Thanks in advance,
D.
Tom Kyte
April 18, 2007 - 10:46 am UTC

one approach might be to use showsql, to see what sql they are executing (the table should be obvious from that)

search for showsql

RE: Find locked row in IOT

David, April 18, 2007 - 11:47 am UTC

I know what table is being locked from the v$LOCK view, but I cant tell what row is. The SQL they are running has bind variables, so I cant tell what PK value is being used.

The only way I could think of doing this was to visit each row in the table/IOT and try to SELECT..FOR UPDATE NOWAIT and find which row fails. This would be OK if the table was small, but what if it's massive ?

I'm assuming this is just a side-effect of Oracle's locking strategy in that it doesn't maintain a list of rows being locked - instead flags the block itself as being locked. Is there a quicker way of interrogating the blocks maybe to see what Transaction ID is flagged on them? Would this be quicker than trying to lock each row manaully and see which one fails ?

Any ideas appreciated,
D.

resource busy

Ravi, December 03, 2009 - 4:24 pm UTC

Hi Tom,

My case is little bit different. We have a transaction table which logs web usage logs from all the users in the enterprise. Mostly there are huge number if inserts happening at any given second. We have some BI applications which query this table as well. Having said that, it is getting extremely difficult to create an index on this table as it gives the error ORA-00054: resource busy and acquire with NOWAIT specified.

Could you please let me know the best possible way to create an index in such scenarios.

Thanks,
Ravi
Tom Kyte
December 04, 2009 - 4:32 pm UTC

before 11g, you would either

a) take an outage - the online index create requires a lock before the create gets started - but not during. It is getting that lock initially that can be hard.

b) use the resource manager to enable database quiesce, this will allow all open transactions to finish and commit - but not enable any new ones to start. when all open transactions do commit - you'll be able to do your DDL (everyone else will appear 'paused' right now) and then get out of there (freeing them up)



In 11g, the locking of an online create index is changed so that - well - there is no lock, it is entirely "online"

ONLINE index

Ravi, December 14, 2009 - 5:34 pm UTC

Hi Tom,

Is this an option? What is the downside of this?

CREATE INDEX <index_name> ON <table_name> (<col_list>) ONLINE;

thanks!

Tom Kyte
December 15, 2009 - 8:36 am UTC

read (a) right above??

well, actually - just re-read everything I wrote to you last time. I don't know how else I could have said it.

Sorry my bad :(

Ravi, December 16, 2009 - 1:49 am UTC


(dirty) workaround for the first question

Ranjith, February 25, 2010 - 8:47 am UTC

declare
function who_is_locking
(
p_tab in varchar2,
p_rowid in rowid
)
return varchar2
is
n number;
a varchar2(100);
pragma autonomous_transaction;
begin
begin
execute immediate 'select 1 from '||p_tab||' where rowid = '''||p_rowid||''' for update nowait' into n;
exception
when no_data_found
then
null;
when others
then
if sqlcode=-54
then
a := dbms_random.string('A', 9);
dbms_job.submit(n,'declare n number;
begin
dbms_application_info.set_module('''||a||''',null);
select 1 into n from '||p_tab||' where rowid ='''||p_rowid||''' for update wait 50;
exception when others then null;--purposely!!
end;');
commit;
n := null;
for i in 1..50
loop
begin
select sid into n from v$session where trim(module) = trim(a);
exit;
exception
when no_data_found
then
dbms_lock.sleep(.2);
end;
end loop;
a := null;
if n is not null
then
select l1.sid||'['||username||'/'||module||']'
into a
from v$lock l1,
v$lock l2,
v$session s
where l1.block = 1
and l2.request > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2
and l1.sid = s.sid
and l2.sid = n;
dbms_output.put_line('Got! '||a);
end if;
else
raise;
end if;
end;
rollback;
return a;
end;
begin
dbms_output.put_line('blocker = '||who_is_locking('sttm_cust_account','AADjRJAAKAAB3j6AAA'));
end;
/

Tom Kyte
March 01, 2010 - 10:55 am UTC

I would not even consider that in real life, I though about it for a nano-second way back when and just said "no". There are too many things wrong with the concept, resource utilization for starters (do a cost benefit here, the cost of getting this is huge)


finding who is blocking

A reader, September 24, 2011 - 3:47 pm UTC

Starting from 10g , can't we get who is blocking whom and the row for which the TX lock is being waited on from v$session (or GV$session, if RAC)?
Tom Kyte
September 25, 2011 - 11:39 am UTC

in order to do that you must be blocked - therefore, YOU cannot tell who is blocking you.

That was the question.

periodically print out before whole transaction finished

A reader, March 14, 2012 - 2:12 am UTC

Hi Tom,

Per your method on how to wait 3 times, i am just thinking a endless wait until the other session commit/rollback.

If works well, but my question is about the line 'dbms_output.put_line('sleeping');', it can not be print out until the end.

I know the reason is they are one transaction, anything can take effect after the whole transaction finished.

Then i though about the autonomous transaction which simply do 'dbms_output.put_line('sleeping');', but still the same behaviour
that print all the 'sleeping' at the end of either blocking session commit or rollback.

So my question is whether oracle has methodology to flush out console output periodicaly like other programming language(like perl
we can modify $|, which will print without reach the new line indicator).


declare
v_get_lock number:=0;
v_id int;
begin
while(true) loop
if v_get_lock = 1 then
exit;
end if;
begin
select id into v_id from test where id=1 for update nowait;
v_get_lock:=1;
exception
when others then

print_sleep();
dbms_lock.sleep(1);
end;
end loop;
end;
/

create or replace procedure leo as
pragma autonomous_transaction;
begin
dbms_output.put_line('sleeping');
commit;
end;
Tom Kyte
March 14, 2012 - 6:55 am UTC

I know the reason is they are one transaction, anything can take effect after
the whole transaction finished.


that is not the reason. The reason is that dbms_output just prints to an in memory array, that the client (sqlplus) retrieves after the STATEMENT (not transaction) completes.

you cannot see dbms_output stuff until the statement submitted to the database returns to the client - so the client can then request the array of stuff to print from the database.

    exception
        when others then
            


I *hate* your code, where did you see me do that? Please do not use when others in that inappropriate - totally wrong - you have a bug in your code - fashion. Do it the right way as I demonstrated initially at the top of this page.


why wouldn't you just "select * for update WAIT" - you appear to want to wait forever. You *KNOW* you are sleeping if it doesn't return, you *KNOW* it will be sleeping until you get the lock. You don't need to communicate at all - you *KNOW* it is sleeping.

ORA-00054 Resource Busy

Dev, July 27, 2012 - 5:53 am UTC

Hi Tom,
I am facing error ORA-OOO54,I created a real table,and Inserting data into that table In 4 to 5 times based on some conditions in a single package,and after that I am fetching data into a object type and am returning,after that i am truncating the table using execute immediate.
This package is called in a interval of every second(approx..),so may be that is the reason for getting that Issue or any other reason.
Please can u provide any solution for solving that issue.
I tried maximum in explaining my issue,If you couldnt get, please execuse me for posting this request.

Thanks
Tom Kyte
July 30, 2012 - 9:51 am UTC

do not truncate a table that has concurrent access, it doesn't even begin to make sense.

use a global temporary table instead - it sounds like what you really want.

or resign yourself to using delete.

A reader, November 18, 2014 - 12:26 pm UTC


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