Tom,

As to his final question:

"...who is locking and who is waiting for that locks."...

taking a look at V$LOCKED_OBJECT is the answer.

Thanks !

Tom:

I am getting this on a web page when I try to run a procedure that is doing multiple inserts to tables.

Tue, 1 Oct 2002 15:34:57 GMT

ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at "ADMIN.SAVE_NEW_PO", line 264
ORA-06512: at "ADMIN.FOO_PO", line 62
ORA-06512: at line 7

Do you know what is cause by.

oracle documentation says the following:

ORA-02049 timeout: distributed transaction waiting for lock

Cause: Exceeded INIT.ORA DISTRIBUTED_LOCK_TIMEOUT seconds waiting for lock.

Action: Treat as a deadlock.

what do i need to do.

Thanks,

Tom:

1. What is a distributed transaction and how many types there are? Is it when you go to another database via link means distributed?

2. When do you usually get a lock? Is it when you do any DML statement? Is it in milliseconds?

3. How would I figure out if I am deadlocking with someone else using a web application?

Thank you,

Tom, could you please explain this.

select sid, serial#,lockwait,status,row_wait_file#,row_wait_block#,row_wait_row#,sql_address,sql_hash_value
from v$session where row_wait_file# >0;

SID SERIAL# LOCKWAIT STATUS ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# SQL_ADDR SQL_HASH_VALUE
------ ---------- -------- -------- -------------- --------------- ------------- -------- --------------
45 2 INACTIVE 6 33468 8 29FF20B0 3625054966
49 2 INACTIVE 6 56648 0 2A44D744 1529370122
51 2 INACTIVE 6 33468 7 2A51C4FC 2023698356
59 2 INACTIVE 6 33468 7 2A420BDC 2189312113
61 2 INACTIVE 6 33468 12 25EC6268 2165471036
63 2 INACTIVE 6 33468 5 2A44A0B8 1817734235
93 602 INACTIVE 6 57168 0 29FF20B0 3625054966
94 34 INACTIVE 6 216794 2 2A064EC8 1969928661
97 10 INACTIVE 6 216746 3 2A064EC8 1969928661
99 10 INACTIVE 6 216753 2 29FF20B0 3625054966
110 27 INACTIVE 6 216782 1 2A05990C 4250919705
select * from v$transaction and v$locked_object does not
yield any rows.

1. What are the values in the sql_address field and row_wait fields above. Is anything being locked or waited on or not ?

2. Why is the status of those sessions inactive that is if there is any active transaction.

Thanks in advance.

Dear Sir,
I have some questions for you and they are as follows.
1)How oracle minimizes lock contention?Does it use multiversioning to achieve the same or it escalates a lock
when the locks reach a threshold?
2)Is there any other way for console output other than using
dbms_output.put_line()
Thanks

Following your frequent advice, I am making a point of reading the Concepts Guide from cover to cover.

In the section on Serializable Isolation in chapter 20 there is a Note: "Transactions containing DML statements with subqueries should use serializable isolation to guarantee consistent read."

I can't quite figure out why it says that. Earlier it says "A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of". So why should we use serializable?

It has to do with the set of rows the DML statement will see.  

If you are updating "blindly" -- eg, you did not select the row out, your update "query" part will see data as of the time the statement was submitted -- not as of the time the row is actually updated.  so in some multi-user situations, your logic may not work as you anticipated since the read is seeing data as of the beginning of the statement -- not as of right now.

serializable won't let it "work" -- it'll simply raise the cannot serialize access.

consider the following example.  Our goal is to maintain a rolled up value in the DEPT table that is the sum of salary from emp.  In order to accomplish that, we'll update DEPT after every update of EMP (could be a trigger even)


ops$tkyte@ORA920> create table dept
  2  ( deptno        int primary key,
  3    sum_of_salary number
  4  );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table emp
  2  ( empno      int primary key,
  3    deptno     references dept,
  4    salary     number
  5  );

Table created.


ops$tkyte@ORA920> insert into dept ( deptno ) values ( 1 );
1 row created.

ops$tkyte@ORA920> insert into dept ( deptno ) values ( 2 );
1 row created.

ops$tkyte@ORA920> insert into emp ( empno, deptno, salary ) values ( 100, 1, 55 );
1 row created.

ops$tkyte@ORA920> insert into emp ( empno, deptno, salary ) values ( 101, 1, 50 );
1 row created.

ops$tkyte@ORA920> update dept
  2     set sum_of_salary =
  3     ( select sum(salary)
  4         from emp
  5            where emp.deptno = dept.deptno )
  6   where dept.deptno = 1;
1 row updated.

ops$tkyte@ORA920> commit;
Commit complete.

<b>so there is our data, it starts out "ok", the dept table has that aggregate.  Now we execute:</b>


ops$tkyte@ORA920> insert into emp ( empno, deptno, salary )
  2  values ( 102, 2, 60 );
1 row created.

ops$tkyte@ORA920> set echo off
in another session run this and then come back here and hit enter
update emp
set deptno = 2
where empno = 100;
update dept
set sum_of_salary = ( select sum(salary)
from emp
where emp.deptno = dept.deptno )
where dept.deptno in ( 1, 2 );
ops$tkyte@ORA920> pause

<b>so the insert takes place in session 1, the two updates take place in session 2 -- another window.  So far -- so good really.  the second session can see its modifications so the data is OK in dept from its perspective.  Now we come back to this session and execute:</b>

ops$tkyte@ORA920> set echo off
this will get blocked, when it does, goto the other session and
commit
ops$tkyte@ORA920>
ops$tkyte@ORA920> update dept
  2     set sum_of_salary = (select sum(salary)
  3                            from emp
  4                           where emp.deptno = dept.deptno)
  5   where dept.deptno = 2;

<b>this statement blocked.  Its "read" component however is frozen already at the point in time the statment began.  the sum(salary) from emp is already known -- 
but does NOT include the modified rows from session 2.  remember, session 2 has NOT YET committed -- hence the salary session 2 added to deptno 2 via the update is not visible to us

we committed in the other session and immediately session 1 returns with:</b>

1 row updated.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> select * from dept;

    DEPTNO SUM_OF_SALARY
---------- -------------
         1            50
         2            60

ops$tkyte@ORA920> select deptno, sum(salary) from emp group by deptno;

    DEPTNO SUM(SALARY)
---------- -----------
         1          50
         2         115

<b>as you can see -- the rollup in dept is "wrong" because the update did not see the data after session 2's commit but before.

In my book coming out this month, I used this same exact example -- but I did not solve the problem using serializable.  I did it the way I prefer using read committed.  Here, we'll rerun the simulation using serializable to see what would happen though.  picking up right after the setup:</b>

ops$tkyte@ORA920> set transaction isolation level serializable;
Transaction set.

<b>employ serializable for this transaction...</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into emp ( empno, deptno, salary )
  2  values ( 102, 2, 60 );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off
in another session run this and then come back here and hit enter
<b>set transaction isolation level serializable;</b>
update emp
set deptno = 2
where empno = 100;
update dept
set sum_of_salary = ( select sum(salary)
from emp
where emp.deptno = dept.deptno )
where dept.deptno in ( 1, 2 );
ops$tkyte@ORA920> pause

ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off
this will get blocked, when it does, goto the other session and
commit
ops$tkyte@ORA920>
ops$tkyte@ORA920> update dept
  2     set sum_of_salary = (select sum(salary)
  3                            from emp
  4                           where emp.deptno = dept.deptno)
  5   where dept.deptno = 2;

<b>this got blocked again.  so far, no different then before...  However, when we commit in the other session -- this immediately pops out:</b>


update dept
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

<b>Now, the result set was set as of the TIME THE TRANSACTION began.  We tried to update some rows that changed since our transaction began and hence it must fail.  We won't have the data coming from differing points in time.  

My solution involves using a select for update to serialize access, this alternative does effectively the same thing</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> rollback;

Rollback complete.

ops$tkyte@ORA920> select * from dept;

    DEPTNO SUM_OF_SALARY
---------- -------------
         1            50
         2            55

ops$tkyte@ORA920> select deptno, sum(salary) from emp group by deptno;

    DEPTNO SUM(SALARY)
---------- -----------
         1          50
         2          55


<b>Now the data is "the same"</b>
 

Thanks, that makes it all clear. I am more familiar with the SELECT FOR UPDATE approach also; needless to say I already your new book on order.

I bet 99% of databases where stored summaries are used as in your example use neither approach. I don't think I've ever seen such a database where all the summary values matched up with the underlying data!

Hi Tom, if possible could you show us how can we create a view to see which table, rowids are locked and which users are locking them.
Thanks.

Dear Tom,

I'll explan my scanario.
I've a third party C executable (which i cannot modify) which after running in "select" mode inserts a table(CEU) with n number of rows..these are nothing but the rowids in a MAIN table(MAIN_CEU).(It may take 10-15 mins for this to complete). After this we run the same exe in "delete" mode so it picks up these rowids from CEU table & deletes the MAIN table(MAIN_CEU) with these rowids.
Now the problem is that if some other user at the same time runs this process in "select" mode the CEU table gets populated again but that user might not want to delete these rows so he wont run the exe in "delete" mode. But as i'm running the "delete" mode simultaneously, these rows will be getting deleted unwantedly.
So i want to lock this table as soon as i start my "select" mode & want to unlock once i finish the "delete" mode so no other user can insert this table during that time.

Please advise.

can you use a trigger?

ops$tkyte@ORA9IR2> create table t as select object_id, object_name from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(object_id);
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create trigger t_trigger before DELETE on t
  2  begin
  3          lock table t in exclusive mode;
  4  end;
  5  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t where object_id = 55;
 
1 row deleted.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2     pragma autonomous_transaction;
  3  begin
  4          insert into t values ( -1, 'xxxx' );
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 

Why didn't i think of that.
Thanx Tom.

Hello Tom,

regarding the ORA-02049: timeout: distributed transaction waiting for lock error, how is it possible to get this error if a transaction is not locking an object in a remote database?

Our application executes MERGE statement by doing a SELECT from a remote table via DB link, and merging into the *local* table, and then it gets ORA-02049. It doesn't really modify or lock anything in the remote database (apart from brakeable share locks for parsing), so, it seems to me that only the local locks will be needed. Does ORA-02049 mean that a lock in the remote database cannot be obtained, and, if so, what lock would that be?

Dear Tom,
Is it possible to have a "Exclusive Read Exclusive Write"
scenario in Oracle provided we have an Oracle environment
with users granted sufficient privileges to read and write
to data objects?
And also I am confused with "Shared Row Exclusive".What does this lock type do?

1st: can one use dbms_rowid.rowid_create to calculate a rowid from the rowid's components retrieved from v$session (at least it seems to work in my environment)?

2nd: how can v$session.lockwait be NULL although all the abovementioned conditions are met (status = 'ACTIVE', row_wait_obj# != 1, lock really exists)?

3nd: why does v$session report the lock although the attempt to modify the row immediately failed (session doesn't wait anymore)?

as for 2 and 3: i used quest software's sql navigator.

first i selected one row for update in the first session:

select * from <my_table> where <my_condition> for update;

after that i opened another session (new navigator) and tried to update the previously locked row using navigator's updateable grid. this update attempt immediately failed (returned ORA-00054) and after that v$session contained one entry with 'lockwait' = NULL, 'row_wait_obj#' != -1 and 'status' = 'ACTIVE' for that session (although it did *NOT* get blocked at all):

select * from v$session where audsid = userenv('sessionid') and row_wait_obj# != -1 and status = 'ACTIVE';

i guess navigator didn't use *THAT* certain session for its updateable-grid-update and used the nowait option.

can you reproduce this (the empty 'lockwait' column)?

From the Documents

"
Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the rollback segments. To re-release the segment, you must issue a COMMIT or ROLLBACK statement
"


hi tom, I have a strored package consist of 10 stored procedures.

each of the stored procedures
1.) queries the remote database
2.) gets the "Valid/required" data
3.) update the LOCAL table.

-- I issue commit AFTER the package is executed.(ie. all 10 procs. are execute and then the transation processing stmt. comes.)

I think (from the above note form the Oracle documents)
that (even if it is select only to the remote db) it will
place tx lock on it ? so

mypackage.proc1
queries remote.table 1 -- updates the local.table_xyz
no commit is issued to it is still tx locked ?
mypackage.proc2
tries to query that same table for different data --
but can not update local.table_xyz2 because the previous lock is not releaed ?

-- so the question is
DO I HAVE TO ISSUE COMMIT AFTER EACH PROCEDURE IS COMPLETE ? (involving same remote table.)

Please let me know.

regards,

I have an LOCAL.employee table which gets data form remote table by selecting form employee@remote

it has emp_id and emp_mgr_id fields which represents the
tree. with the (pk-fk) constraints (in both tables).

when I select from the employee@remote table for new/updated data and try to insert one by one in for cursor loop, I get the error distributed transaction waiting for lock. and this is development env. NOBODY else is querying or locking any of the tables. and I get ora-20116 (distributed transaction waiting for lock) error ..

Can you please guide me in this case ?

show us the "code" and give us the "real error message number"

for you see, an ora-20116 would be from YOU, we don't raise that error number:

ops$tkyte@ORA9IR2> exec raise_application_error( -20116, 'user defined error, not from oracle' );
BEGIN raise_application_error( -20116, 'user defined error, not from oracle' ); END;
 
*
ERROR at line 1:
ORA-20116: user defined error, not from oracle
ORA-06512: at line 1
 
 

hi tom,

I am trying to reproduce that error ,but I can not get it again. I am sure I mis-typed the number it was not over 20k.

But thank you for taking time to look in to this,

i can not reproduce it but the error # was...

ORA-02049: timeout: distributed transaction waiting for lock error

for select over the db link

the above error is because the
local table is locked or it is because the remote table is locked ?

Tom,
    Hi !. We have two Oracle 9i (rel 2) databases one on Sun and the other on HP. We have a db link from the Sun box to HP. Sometimes, the database on HP goes down(which is an issue by itself) but the listener doesnt ...and when we try to query data from Sun->HP (via kron job)it just sits there and hangs ...w/o returning an error for a long time. I have exeception handling in my code if the select fails ....it is just that it takes a long time b4 it returns the error message.

SQL> l
  1  select name, value from v$parameter
  2* where name like 'dist%'
SQL> /

NAME                      VALUE
------------------------- ---------------
distributed_lock_timeout  60

Is there another parameter that I can set somewhere that will take care of this issue. 

hi tom,

nice example for an lost update. i am thinking about if there is a new 100% pure sql way - that means using _NO_ pl/sql things - using row level locking i don't know (as you spoke about using "select for update").

thanks,
markus

hi tom,

sorry, i simply forgot that "select for update" is also available OUTSIDE of pl/sql blocks as i am used to use this within pl/sql blocks. my question would be answered by this:

SQL> create table sumrec (pkey varchar2(10), psum number);

Tabelle wurde angelegt.

SQL> create table detrec (pkey varchar2(10), psum number);

Tabelle wurde angelegt.

SQL> insert into detrec values ('A', 1);

1 Zeile wurde erstellt.

SQL> insert into detrec values ('A', 1);

1 Zeile wurde erstellt.

SQL> insert into detrec values ('B', 1);

1 Zeile wurde erstellt.

SQL> commit;

SQL> insert into sumrec (select pkey, sum (psum) from detrec group by pkey);

2 Zeilen wurden erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from sumrec;

PKEY             PSUM
---------- ----------
A                   2
B                   1

SQL> insert into detrec values ('A', 1);

1 Zeile wurde erstellt.

SQL> select * from sumrec where pkey = 'A' for update;

PKEY             PSUM
---------- ----------
A                   2

SQL>  select * from detrec where pkey = 'A' for update;

PKEY             PSUM
---------- ----------
A                   1
A                   1
A                   1

>>
>> NO LOST UPDATES POSSIBLE ANY MORE; OTHERS WILL HAVE TO WAIT
>>

SQL> update sumrec set psum = (select sum (psum) from detrec where detrec.pkey = sumrec.pkey) where pkey = 'A';

1 Zeile wurde aktualisiert.

SQL> select * from sumrec;

PKEY             PSUM
---------- ----------
A                   3
B                   1

SQL> commit;
  

Tom,

I have a confusion about the meaning of the column STATUS in V$SESSION.

In one of your discussion threads related to tracking the execution of SQL and PL/SQL, you mentioned that we can run the showsql.sql session and see the data. The query in showsql.sql selects only those sessions that have a status of ACTIVE. So my questions are :

1. If a session is waiting for a lock, what will be the value in the STATUS column for the holding and locking session. What will be the value of STATUS column for the "waiting/blocked" session and the blocking session.
2. If the STATUS is INACTIVE and there is a huge value for LAST_CALL_ET, how do you identify a session that is idle doing nothing vs a session that has been waiting for a resource such as a lock. Is there any other columns in V$SESSION that will indicate it
3. What are the best ways to identify sessions on a development environment. Suppose I execute an application that conects to the Oracle db, how can I find the corresponding sessions. Currently, I am having difficulty in identifying the sessions that I started off because there are many sessions running and the TOAD Trace session screen is very confusing as it refreshes often. Is there any query I could run.

Your help is highly appreciated

Hi Tom,

Thanks much for your wonderful explanation. From that the following is my understanding.

1. All blocked/waiting sessions will always have a STATUS of ACTIVE
2. Also, the ROW_WAIT_ROW#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# will be populated with a value != -1 for a blocked session
3. In order to determine if a session is blocked waiting for a lock, we can also first query the V$SESSION for all ACTIVE sessions with (ROW_WAIT_OBJECT# != -1 and ROW_WAIT_OBJECT# > 0). Then use V$LOCK to identify the blocking session using the query you have given above.

Please confirm

2) if what they are waiting on is a row.

sid 10 -> update emp set ename=ename where empno=7788;
sid 11 -> did the same, blocked by 10
sid 15 -> lock table emp in exclusive mode, blocked by 11


ops$tkyte@ORA9IR2> select sid, username, status, row_wait_obj#
  2  from v$session where username = user;
 
       SID USERNAME                       STATUS   ROW_WAIT_OBJ#
---------- ------------------------------ -------- -------------
        10 OPS$TKYTE                      ACTIVE              -1
        11 OPS$TKYTE                      ACTIVE           36275
        15 OPS$TKYTE                      ACTIVE              -1
 
ops$tkyte@ORA9IR2> select sid,type, block
  2  from v$lock where sid in (10,11,15);
 
       SID TY      BLOCK
---------- -- ----------
        10 TX          1
        10 TM          1
        11 TM          1
        11 TX          0
        15 TM          0


3) nope, as shown above, 15 is blocked, but not on a row. 

Hi Tom,
What is the major difference between different types of Locks? I am not clear with this even after reading your book

Hi Tom,

an example follows:

ssh@oracle9> set sqlprompt session1

session1>create table t (id number not null, constraint pk_t primary key(id));

Table created.

session1>insert into t values(1);

1 row created.

Now let's start another sqlplus session:

ssh@oracle9> set sqlprompt 'session2>'
session2>select * from t;

no rows selected

After that perform alter index:

session1>alter index pk_t rebuild;

Index altered.

session2>select * from t;

ID
----------
1

Note that no cimmit has been issued in session1. But session2 behaves as if it had.

Regards,
Sergei

Hi again,

Prologue is the same as before:
session1>create table t (id number not null, constraint pk_t primary key(id));

Table created.

session1>insert into t values(1);

1 row created.

Then:

session2>insert into t values(2);

1 row created.

session1>alter index pk_t rebuild;
alter index pk_t rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

session2>alter index pk_t rebuild;

Index altered.

session1>select * from t;

ID
----------
2
1

Why the session2 was able to perform alter index while session1 was not?

There is then a question in this regard...

Is there any means of creating "intertransactional" locks, i.e. locks which live across transaction boundaries?

Hi Tom,
I am on RAC, and I have not been able to get a script which can tell me how to find objects being locked from different instances. As of now, I am going to each of the instances and finding out blocking sessions, which sometimes take lot of time. Can you share with me such a script, if you have one?
Thanks and Regards

gv$ tables are available for cross instance information like that.


SQL> select sid, id1, id2 from v$lock where type in ( 'TX', 'TM' );
 
no rows selected
 
  1* select inst_id, sid, id1, id2 from gv$lock where type in ( 'TX', 'TM' )
SQL> /
 
   INST_ID        SID        ID1        ID2
---------- ---------- ---------- ----------
         2        135      57258          0
         2        135    1245192       6629


SQL> select instance_number, instance_name from v$instance;
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 O10G1
 
SQL> c/v$/gv$/
  1* select instance_number, instance_name from gv$instance
SQL> /
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              2 O10G2
              1 O10G1
 

I had a lock on the second instance, the GV view lets you see them all. 

Tom,

Locks are queueing up on this table during insert into. 
REQUEST_ID column is Primary key on this table (not a surrogate key).

Can you please help me to understand why it is locking for insert statements.

Details are below. 

desc asoc_request.
ORDER_NUMBER                              NOT NULL VARCHAR2(10)
 REQUEST_ID                                NOT NULL VARCHAR2(10)
 ASOC_RESP_XML                                      CLOB

1* select * from v$locked_object
SQL> /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME    PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- --------------- --------------- ------------ -----------
       252         10         27      25567         58 IOM             webm                                   3
SQL> select * from v$lock where type='TX' and lmode>0; 

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000003B1A598A8 00000003B1A59A20         58 TX   16515082         27          6          0        418          0

SQL> select sid, serial#, sql_address, sql_hash_value from v$session where sid=58 ;

       SID    SERIAL# SQL_ADDRESS      SQL_HASH_VALUE
---------- ---------- ---------------- --------------
        58      62126 00000003C2697D40     3948874802
SQL> select sql_text, address, hash_value from v$sql where hash_value=3948874802 ;

SQL_TEXT                                                ADDRESS          HASH_VALUE
------------------------------------------------------- ---------------- ----------
insert into asoc_request (request_id, order_number)     00000003C2697D40 3948874802
values (:1, :2)

  1* select * from v$session_wait where sid=58
SQL> /

       SID       SEQ# EVENT                          P1TEXT
---------- ---------- ------------------------------ -------------------------
        P1 P1RAW            P2TEXT                            P2
---------- ---------------- ------------------------- ----------
P2RAW            P3TEXT                            P3 P3RAW
---------------- ------------------------- ---------- ----------------
 WAIT_TIME SECONDS_IN_WAIT STATE
---------- --------------- -------------------
        58        483 SQL*Net message from client    driver id
1952673792 0000000074637000 #bytes                             1
0000000000000001                                    0 00
0    1051 WAITING
 

Tom.
Thanks.
Sorry i did not give you all the locking sessions.

Here it is. All those sessions are waiting on INSERTS and hung-up for ever (locks never go away). No sessions are blocked by another. These are coming from application through JDBC think client (Connection pool)

Here is the sql i see, they are all executing,

insert into asoc_request (request_id, order_number)     values (:1, :2)

SQL> SQL> select * from v$lock where type='TX' and lmode>0; 

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000003B1A598A8 00000003B1A59A20         58 TX   16515082         27          6          0       3975          0
00000003AFC67698 00000003AFC67810        102 TX    3342374        415          6          0       3218          0
00000003B1A49668 00000003B1A497E0        143 TX   17956896         35          6          0       1323          0
00000003B1A48FF8 00000003B1A49170        174 TX   20578320         33          6          0       2549          0
00000003B0ACFAD8 00000003B0ACFC50        505 TX   14483483         19          6          0       1523          0

SQL> select * from v$locked_object;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME                   PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------ -----------
        51         38        415      25567        102 IOM        webm                                                  3
       314         16         33      25567        174 IOM        webm                                                  3
       274         32         35      25567        143 IOM        webm                                                  3
       252         10         27      25567         58 IOM        webm                                                  3
       221         27         19      25567        505 IOM        webm                                                  3

  1* select * from v$session_wait where sid in (select sid from v$lock where type='TX' and lmode>0)
SQL> /


       SID       SEQ# EVENT                                                            P1TEXT                                                                   P1 P1RAW
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW             WAIT_TIME
---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ----------
SECONDS_IN_WAIT STATE
--------------- -------------------
        58        483 SQL*Net message from client                                      driver id                                                        1952673792 0000000074637000
#bytes                                                                    1 0000000000000001                                                                           0 00                        0
           4116 WAITING

       102        483 SQL*Net message from client                                      driver id                                                        1952673792 0000000074637000
#bytes                                                                    1 0000000000000001                                                                           0 00                        0
           3359 WAITING

       143        485 SQL*Net message from client                                      driver id                                                        1952673792 0000000074637000
#bytes                                                                    1 0000000000000001                                                                           0 00                        0
           1464 WAITING

       174        483 SQL*Net message from client                                      driver id                                                        1952673792 0000000074637000
#bytes                                                                    1 0000000000000001                                                                           0 00                        0
           2690 WAITING

       505        482 SQL*Net message from client                                      driver id                                                        1952673792 0000000074637000
#bytes                                                                    1 0000000000000001                                                                           0 00                        0
           1667 WAITING

 

Tom,

The question is, How do i know/confirm whether those sessions completed INSERTS in the first place ? It is clear that they are not blocked by any other sesssion.

Is it safe to say, are they just waiting for COMMIT from webserver session (v$session_wait was showing SQLNET MESSAGE waiting for client) ?

Yesterday, i saw one strange locking behaviour with row locks on our database. All sesssions were blocked due to row locks. So i releasesd all the application connections via 'alter system kill session ..' and ensured that no locks/transaction by querying v$lock/v$transaction.
Locked resources were released.

But again , when application tries to do database update/insert, they were still blocked like before and the locks were accumulating and v$lock entries were growing higher.

My only guess was, for some reason iplanet webserver might have been overloaded and ran out of JVM resources likely issues and could not issue COMMIT for the transaction and that could have caused database row level exclusive locks persists on the database side.

Is there any way to justify this from DB perspective ?

This APP is using Java/JDBC connecting pooling uisng iPlanet webserver.

Do i need to restart webserver whenever i released any DB connections ?


Thanks for your time.

Thanks tom for your inputs.

This particular app is using only web server (no app server yet).

If the java code does not issue commit, then this locking problem should arise all of the time but it happens once in a while. My guess with "webserver JVM resource problem" might be the cause was, after we restart the webserver (ofcourse, after released the blocking/waiting sessions from DB), the same database transaction (tried earlier) is being successful with out any locks.

I have been seeing this behaviour very consistently already thrice.

But as you mentioned, java code need to be checked to ensure the commit/rollback is being invoked at the end of the db call.

Appreciate if you have any more suggestions/inputs on this.

Tom,

Thank you for your inputs. We will do the code review.

Tom,

Here is the Blocking/waiting locks chart.

I have ROWIDs (shown by OEM lock monitor) which are : 00000000.0000.000A
00029E84.003C.000A


How to interpret this and find the actual ROWID of the row being blocked ?

Blocking/Waiting Locks Chart

(Chart form was tabular, see data table below)
Username Session Id Session Serial Number Process Id Lock Type Mode Held Mode Requested Object Name ROWID Object Owner Object Type Resource ID1 Resource ID2
IOM 216 48414 10079 TX EXCLUSIVE NONE IOM_ORDER_INFO 00000000.0000.000A IOMDBO TABLE 12845099 58
IOM 241 31516 19209 TX NONE EXCLUSIVE PACKAGE 00029E84.003C.000A IOMDBO TABLE 12845099 58

Tom,

Sorry for missing the DB version.

Oracle 9.2.0.5

I want to lock other users to insert in the table in
which I am inserting. So before doing INSERT
I lock the complete table. But the problem is that
if other users have locked some single row of that table,
I cannot get full table lock.

Is there something like INSERT ONLY LOCK, i.e. a lock
which allow other users to UPDATE, DELETE, LOCK rows of
a table but disallows INSERT.

Thanks

I want to lock other users to insert in the table in
which I am inserting. So before doing INSERT
I lock the complete table. But the problem is that
if other users have locked some single row of that table,
I cannot get full table lock.

Is there something like INSERT ONLY LOCK, i.e. a lock
which allow other users to UPDATE, DELETE, LOCK rows of
a table but disallows INSERT.

Thanks

WE have a pkg called
pkg_ins_upd_product(prodno, rtncode);

now we want run this the pkg parallel using our job schedule .

at 10:30 pkg_ins_upd_product(1000, rtncode);
at 10:30 pkg_ins_upd_product(2000, rtncode);
.
.
.
.
.
at 10:30 pkg_ins_upd_product(30000, rtncode);

like that we have 30 product# ? some them comeplete in take 1-5 min . Because of this we are experiencing contention issues with parallel runs and database locks and rtncode :2049 how do you solve this problem.

Thansk for the help

WE have a pkg called
pkg_ins_upd_product(prodno, rtncode);

now we want run this the pkg parallel using our job schedule .

at 10:30 pkg_ins_upd_product(1000, rtncode);
at 10:30 pkg_ins_upd_product(2000, rtncode);
.
.
.
.
.
at 10:30 pkg_ins_upd_product(30000, rtncode);

like that we have 30 product# ? some them comeplete in take 1-5 min . Because of
this we are experiencing contention issues with parallel runs and database locks
and rtncode :2049 how do you solve this problem.

we have more than more than one database is involved. We have two databases 1 database1 2 database2
Based on the values some times we update or delete or inser the rows.
For instance the calling procedure is in database1
had table called table1. We select the rows from the table1. one of the column specifes what action should be performed.
if it says delete we delete the row in table1 of database2 and as well as delete and update some of the rows from table2 and table3 of database1.
In same manner if it says update we may update or insert the rows to table1 of database2 based on various conditions.



Could you please help us
Thank you

Hi Tom,

In your example in the middle of this thread:


sid 10 -> update emp set ename=ename where empno=7788;
sid 11 -> did the same, blocked by 10
sid 15 -> lock table emp in exclusive mode, blocked by 11


ops$tkyte@ORA9IR2> select sid, username, status, row_wait_obj#
2 from v$session where username = user;

SID USERNAME STATUS ROW_WAIT_OBJ#
---------- ------------------------------ -------- -------------
10 OPS$TKYTE ACTIVE -1
11 OPS$TKYTE ACTIVE 36275
15 OPS$TKYTE ACTIVE -1

ops$tkyte@ORA9IR2> select sid,type, block
2 from v$lock where sid in (10,11,15);

SID TY BLOCK
---------- -- ----------
10 TX 1
10 TM 1
11 TM 1
11 TX 0
15 TM 0



My question is how to find session 11 is waiting for session 10 by looking at v$lock or other views.

Users always ask me whether their sessions are waiting for other sessions. I can find this information easily by looking at instance/session/lock on 9i OEM. But I need a query with similar function.

Thanks so much for your help.

Sean

We have a 9.2.0.7 database that has full of dictionary managed tablespace.

In the last two days a simple DDL and even DML just hangs in the database.

After digging in the database, it was found out that the wait event was "enqueue". If the job is running in parallel, the parallel slaves will have "enqueue" waits if not the only SID will have that.

Using the value of P1 in v$session_wait table, the lock was of type ST (space transaction) and the mode was Exclusive.


I checked in alert log files and it was generating ORA-01575 error continuously.

I made sure temporary tablespace is really temporary not PERMANENT.
The database was using very small sort_area and hash_area size but not pga_aggregate_target. I changed those and made the database to use pga_aggregate_target.
Few tablespaces (permanent) had pct_increase either 0 or 50. I changed all of them to have 1.

I didn't get that many information from google either.

Normally what should we do when we have this problem.

Thanks,

actually after setting the pga_aggregate_target to 750M and setting workarea_policy_size to AUTO, the problem is still there with the same ST lock.

Interestingly the ORA-01575 error stopped happening after setting the pga_aggregate_target. But the enqueue wait with ST lock is still happening.

I don't understand what to do now. Converting to LMT's is not a easy solution in our company.

forgot to ask you a question:

do you think it is good to increase the size of hash and sort area size parameters as well? but I don't think oracle use those spaces if we have already set the pga_aggreagete_target, but I want your expert opinion.

another question:
does dropping and recreating temp space makes sense??

Yes I agree about the order regarding temp tablespace.
But does it make sense to do that?

Well 750MB is something which is sort of an average size across all our databases.

The hash and sort area sizes are 4MB and 2MB respectively.
Do you know any minimum value that I can bump up these?
Shall I retain the workarea_size_policy to AUTO?

Thanks a lot,

RAM: 4 GB
SGA: 1.1 GB
PGA_AGGREGATE_TARGET: 750MB

nothing else in the box other than oracle instance.

Good question. I don't know why the primary DBA has kept SGA so low.

I haven't found out the optimal size of the pga target yet. but in this case I think I cannot bump up the pga target bigger than SGA right?

When we rebuild indexes in order to move it another tablespace, if we specify a smaller initial extent, will we avoid getting ST locks?

Anything to do with SMON here?

Going back to our privious discussions, bumping up the hash and sort area size did not seem to solved our issue.

The alter index rebuild still goes for ever and the size of our indexes are around 1 GB.

I think you are right.  It may not be due to temporary tablespace.  In fact, I created a new one, made that as default temporary tablespace and dropped the old one. Even after doing that, the enqueue wait with the same P1 parameter continued.

About your questions, the database is on a Windows server with 4 cpu on it.  The parallel_max_servers is 60.

I got the extents information from dba_extents.

SQL> select min(bytes),max(bytes),segment_type from dba_extents
  2  group by segment_type;

MIN(BYTES) MAX(BYTES) SEGMENT_TYPE
---------- ---------- ------------------
     16384      16384 CACHE
     16384    7782400 CLUSTER
     16384   33587200 INDEX
     16384    8437760 INDEX PARTITION
     16384     163840 LOBINDEX
     16384   26214400 LOBSEGMENT
     65536      65536 ROLLBACK
     16384   67108864 TABLE
     16384  268451840 TABLE PARTITION
     49152    8388608 TYPE2 UNDO

The particular index paratition which I was trying to rebuild has min(bytes) 16kb and max(bytes) 4MB.  The size of the index partition is 140MB.

Thanks,

 

No we are not. At most there will be around 12-16 parallel sessions active in the database.

We tried one other way by bumping up the UNDO_RETENTION parameter to a big number (20k earlier it was 3600). It seemed our jobs progressed normally.

Also, we set the max_extents to some big number instead of putting it as UNLIMITED.

I don't know what would have caused the statements to work, but it is a good news for us.

What do you think?

well, you are right.  From the last 1.5 hours the alter index rebuild is still going on.  The same enqueue event and the same ST lock is holding it.

So I guess the UNDO RETENTION and MAX_EXTENTS might not be the issue.

As you mentioned about the contention, I don't think that is the reason too.  Now I am the only user running in the database.  

One weird thing:  I am running alter index with parallel degree 4 and when I check px_session table there are 8 slaves associated to that parent SID (sid=70)

SQL> select sid,qcsid from v$px_session where qcsid=70;

       SID      QCSID
---------- ----------
        70         70
        32         70
        52         70
        26         70
        67         70
        39         70
        65         70
        31         70
        20         70

In the v$sql table 4 of them say CREATE INDEX... and other 4 has v$session.command value 0.  The parent sid says "alter index".

SQL>  select command,sid from v$session where sid in (select sid from v$px_session where qcsid=70);

   COMMAND        SID
---------- ----------
         0         20
         9         26
         0         31
         9         32
         0         39
         9         52
         0         65
         9         67
         9         70



Do you why I got 8 slaves and why the extra 4 has command value 0?
 

I have the wait events for couple of SID's that belong to parent sid 70.

SID EVENT TIME_WAITED TOTAL_WAITS TOTAL_TIMEOUTS
---------- ------------------------------ ----------- ----------- --------------
20 latch free 11 31 26
20 direct path read 748 633 0
20 PX qref latch 23 23 23
20 PX Deq Credit: send blkd 35198 127775 24
20 PX Deq: Table Q Get Keys 4 2 0
20 PX Deq: Execution Msg 777372 3910 3887
20 PX Deq Credit: need buffer 747 4267 1
20 library cache pin 1 5 0
20 buffer busy waits 43 44 0
20 db file scattered read 24 9 0
26 latch free 0 29 1
26 PX Deq: Table Q Normal 26208 9115 90
26 PX Deq: Execution Msg 140 3 0
26 PX Deq: Msg Fragment 0 1 0
26 direct path write 0 4 0
26 direct path read 1164 1057 0
26 db file sequential read 10 8 0
26 log file switch completion 62 4 0
26 buffer busy waits 0 1 0
26 enqueue 571511 13328 0
26 local write wait 270 147 0


The value of command for sid 20 is 0 and for 26 it is 9.

Hi Tom,

1.How to findout how long the sessions are holding the lock as well as how long the sessions are waiting?

2.How to findout how long the session being Inactive?

3.Is the Inactive sessions o/s process id is the same which we can see in ps -ef | grep LOCAL=NO?

Thanks in Advance.

I am atempting to get a process set up to move a database quickly and in bulk from one machine to another, and not having datapump in V9. I'm having a 2049 problem in the following case:

1.) Remote database has no active users, data was loaded there as a production copy to test what will later be the use of the actual production DB.
2.) Large Partitioned table, 30+ GB, currently has 6 partitions.
3.) Partition key is on a date column, breaking up data by month.
4.) Set up multiple scripts to transfer data from copy database to new target database.
5.) Each script has the following statement in it, with the understanding that I vary the month defined in each one to get all the months:

INSERT /*+ APPEND */ into T2 ( select /*+ PARALLEL( t, 2 ) */ * from T1@db_link t where t.date_col >= to_date( '20060701', 'YYYYMMDD' ) AND t.txn_process_date < to_date( '20060801', 'YYYYMMDD' ) ) ;

When I ran this, I received an ORA-02049 on the scripts which started after the first one. As there is no locking going on in the remote database, I suspect it is related to the local table. But due to each one working in a separate partition, I thought (obviously incorrectly) that I was safe.

Is it the APPEND hint? Is the only solution to get rid of that or is there something else going on that I'm not aware of.

TIA,
Paul

My interpretation of the q is that Paul is trying something like this:

CONN1> INSERT /*+ APPEND */ into T2 ( select ... from T1@db_link t
where t.date_col >= to_date( '20060701', 'YYYYMMDD' )
AND t.txn_process_date < to_date( '20060801', 'YYYYMMDD' ) ) ;
CONN2> INSERT /*+ APPEND */ into T2 ( select ... from T1@db_link t
where t.date_col >= to_date( '20060801', 'YYYYMMDD' )
AND t.txn_process_date < to_date( '20060901', 'YYYYMMDD' ) ) ;

problem is that Paul knows that the data will be inserted in separate partitions, but not the runtime engine, so CONN1 will lock all partitions on start, and CONN2 will try the same and so fail.

Solution: insert directly into the partitions:

CONN1> INSERT /*+ APPEND */ into T2 partition (P20060701) ...
CONN1> INSERT /*+ APPEND */ into T2 partition (P20060801) ...

I've checked using the below test case, just to be sure that things work as I described :)

-------------------------

CONN1> create table t (x int)
2 partition by range (x) (
3 partition p1 values less than(2),
4 partition p2 values less than(3)
5 );

CONN1> insert /*+ append */ into t (x) select 1 from dual@loopback connect by level <= 10;

10 rows created.

CONN2> select type, (select object_type||' '||object_name||' '||subobject_name from dba_objects where object_id = id1) name,
2 lmode, request from v$lock where sid in (select sid from v$session where username='DELLERA') and type='TM';

TYPE NAME LMODE REQUEST
------ ------------------------------ ---------- ----------
TM TABLE PARTITION T P2 6 0
TM TABLE PARTITION T P1 6 0
TM TABLE T 3 0

CONN1> rollback;

Rollback complete.

CONN1> insert /*+ append */ into t PARTITION (P1) (x) select 1 from dual@loopback connect by level <= 10;

10 rows created.

CONN2> select type, (select object_type||' '||object_name||' '||subobject_name from dba_objects where object_id = id1) name,
2 lmode, request from v$lock where sid in (select sid from v$session where username='DELLERA') and type='TM';

TYPE NAME LMODE REQUEST
------ ------------------------------ ---------- ----------
TM TABLE PARTITION T P1 6 0
TM TABLE T 3 0

CONN2> insert /*+ append */ into t PARTITION (P2) (x) select 2 from dual@loopback connect by level <= 10;

10 rows created.

CONN1> rollback;

Rollback complete.

CONN2> rollback;

Rollback complete.

CONN1> insert /*+ append */ into t (x) select 1 from dual@loopback connect by level <= 10;

10 rows created.

CONN2> insert /*+ append */ into t (x) select 2 from dual@loopback connect by level <= 10;
(Statement hangs, then fail after 60 seconds with:

ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock)

CONN1> show parameter distributed_lock_timeout

NAME TYPE VALUE
------------------------------------ --------------------------------- ------
distributed_lock_timeout integer 60

To Toms comment "parallel isn't going to happen over that dblink." I'm hoping and thinking I just need to enable PDML for the session, at least that is what is implied in another article which I just fouund where the user was also trying to move a database quickly, on this site; Tom, you said you got 4 remote and 4 local sessions going once that was done.

And, Alberto, your solution is elegant and simple, which is usually the case with one and the other. I had not thought about operating at the partition level, I am always focused on the entire table and letting Oracle manage the details, which in this case ended up bitinng me. Gracie and thanks a lot!

Hi,
Recentlly while tracing Production Db Server Slowness, I found One Session as Inactive and Holding Lock . The real prblem that I found few session (Active) waiting for this session to finish . At that time I Kill that Inactive sesion and the problem is solved but I still dont know that how could this takes place. As that session was in-active and holding the X -Lock .
Can you guide me that in which situations this can take place. I think that this is due to some where in Application's problem but yet not clear that which type of application error code cause such problem in Oralce .

Regards
MJani

go into sqlplus.

issue:

SQL> lock table emp in exclusive mode;


Now, walk away.  there you go, that inactive session (it is not doing anything right now) has a lock on the emp table.

That is what your application did, a user used it, locked some data, and left... 

Another way to get X mode on a TM enqueue, is to do a direct load against the table. A simple insert /*+ append */ select * from .... will take the TM enqueue in X mode. That will be held till the transaction commits or rolls back.

-Mark

Hi Tom,

We have a procedure which does the aggregation stuff at the end of the given day. We also log the metadata details in the Metadata_log table for this procedure.

However, this table is being used by all the application modules to register their metadata enties with their own process codes.

Now when we were executing this PL/SQL procedure the Metadata_log table gets locked and no other module is able to write their metadata
enties into this table as it holds database locks.

Questions: Does Oracle holds exclusive lock while inserting the rows in a table. This is quite strange behaviour as observed.
Never experienced this behaviour before.

Does /*+ noappend parallel */ cause locks to hold till the TX completes.

Please help us understand this concept.

Thanks


dwuser@ADMW>select
2 (select username from v$session where sid=a.sid) blocker,
3 a.sid,
4 ' is blocking ',
5 (select username from v$session where sid=b.sid) blockee,
6 b.sid
7 from v$lock a, v$lock b
8 where a.block = 1
9 and b.request > 0
10 and a.id1 = b.id1
11 and a.id2 = b.id2
12 /


BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------------------------------ ---------- ------------- ------------------------------ ----------
DWUSER 982 is blocking ETLDR_USER 979
DWUSER 982 is blocking ETLDR_USER 998
DWUSER 982 is blocking ETLDR_USER 1054


dwuser>Exec Aggregation(to_date('20-NOV-2006 07:59:59','DD-MON-YYYY hh24:mi:ss');

and ETLdr_USER is loading the data in the tables and tries to register its metadata entry in the metadata_log table.


Just getting the code short and not complicated for you to understand!:)

CREATE OR REPLACE PROCEDURE Aggregation (p_get_date DATE) AS
BEGIN
DECLARE
-- Some Variables declared
BEGIN
BEGIN
-- some initializations
end;

-- Populating the results in temporary table for the given day
BEGIN
Select Sysdate into p_start_date from dual;
p_new_time := dbms_utility.get_time;
BEGIN
-- Some Insert Statement using /*+ noappend parallel */ hint
EXCEPTION
When Others then
rollback;
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
return;
END;

BEGIN
-- Populating another table for that day
-- Some Insert Statement using /*+ noappend parallel */ hint
EXCEPTION
When Others then
rollback;
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
END;

Begin
Insert Into metadata_log Values(3,Log_Process_Seq.nextval,p_start_date,Num_Rows,Sysdate,p_get_date,p_get_date + 1);
EXCEPTION
When Others then
rollback;
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
END;

END;

BEGIN
Select Sysdate into p_start_date from dual;
p_new_time := dbms_utility.get_time;
BEGIN
-- Some Insert Statement using /*+ noappend parallel */ hint
num_rows := SQL%ROWCOUNT;
End;

Begin
Insert Into metadata_log Values(5,Log_Process_Seq.nextval,p_start_date,Num_Rows,Sysdate,p_get_date,p_get_date + 1);
EXCEPTION
When Others then
rollback;
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
END;
END;
commit;
utl_file.fclose(output_file);
END;
END;
/

As above. Sorry to put in my e-mail address, otherwise you may or mayn't answer my question.

Thanks in advance.

Hi tom,

Listed the code after correcting the Exception blocks.


CREATE OR REPLACE PROCEDURE Aggregation (p_get_date DATE) AS
BEGIN
DECLARE
-- Some Variables declared
BEGIN
BEGIN
-- some initializations
end;

-- Populating the results in temporary table for the given day
BEGIN
Select Sysdate into p_start_date from dual;
p_new_time := dbms_utility.get_time;
BEGIN
-- Some Insert Statement using /*+ noappend parallel */ hint into global Temporary table T
EXCEPTION
When Others then
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
return;
END;

BEGIN
-- Populating another table for that day
-- Some Insert Statement using /*+ noappend parallel */ hint into table T1 selecting the data from TEMP table T
EXCEPTION
When Others then
rollback;
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
return;
END;

Begin
-- logging the metadata information
Insert Into metadata_log Values(3,Log_Process_Seq.nextval,p_start_date,Num_Rows,Sysdate,p_get_date,p_get_date + 1);
EXCEPTION
When Others then
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
return;
END;

END;

BEGIN
Select Sysdate into p_start_date from dual;
p_new_time := dbms_utility.get_time;
BEGIN
-- Some Insert Statement using /*+ noappend parallel */ hint into table T2
num_rows := SQL%ROWCOUNT;
End;
/* When this SQL statement is getting executed then SQL*LDR sessions are trying to insert the metadata information in the Metadata_log
table which causes lock to happen.
*/
Begin
-- Logging the metadata details
Insert Into metadata_log Values(5,Log_Process_Seq.nextval,p_start_date,Num_Rows,Sysdate,p_get_date,p_get_date + 1);
EXCEPTION
When Others then
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || err_msg);
utl_file.fclose(output_file);
return;
END;
END;
commit;
utl_file.fclose(output_file);
END;
END;
/

> wish PLSQL did not have commit nor rollback

Perhaps commit -- yes, but rollback to savepoint is very useful, don't you think?

DDL for the below example can be found here:

</code> http://dbatoolz.orapros.com/tp/1251.can_you_remove_savepoint_from_a_master_detail_insert_api_.html <code>

--
-- the call below has intentional "bad data" that will raise
-- bad_data exception ( simulated dup_val_on_index )
-- but since there's no savepoint the order header and
-- one of the order lines will be saved anyway ...
--

DBATOOLZ> begin
2 order_ui.order_form(p_cust_id => 1,
3 p_ship_to => 1,
4 p_bill_to => 1,
5 p_line_one_id => 1,
6 p_line_one_qty => 10,
7 p_line_two_id => 1,
8 p_line_two_qty => 20,
9 p_what => 'save');
10 commit;
11 end;
12 /

PL/SQL procedure successfully completed.



--
-- so now we have incomplete order that
-- wasn't even supposed to be saved
--
DBATOOLZ>
DBATOOLZ>
DBATOOLZ> select * from order_header;

OH_ID SHIP_TO_ID BILL_TO_ID
---------- ---------- ----------
1 1 1

1 row selected.

DBATOOLZ> select * from order_line;

OL_ID OH_ID OL_PROD_ID OL_PROD_QTY OL_DESC
---------- ---------- ---------- ----------- ------------------------------
1 1 1 10 order_api line 1

1 row selected.

DBATOOLZ>
DBATOOLZ> delete from order_line;

1 row deleted.

DBATOOLZ> delete from order_header;

1 row deleted.

DBATOOLZ> commit;

Commit complete.


--
-- now we make the same call with intentional "bad data" that will raise
-- bad_data exception ( simulated dup_val_on_index )
-- but this time there IS savepoint so the order header and
-- one of the order lines will NOT be saved (correct behavior)
--
DBATOOLZ>
DBATOOLZ> begin
2 order_ui.order_form(p_cust_id => 1,
3 p_ship_to => 1,
4 p_bill_to => 1,
5 p_line_one_id => 1,
6 p_line_one_qty => 10,
7 p_line_two_id => 1,
8 p_line_two_qty => 20,
9 p_what => 'save_savepoint');
10 commit;
11 end;
12 /

PL/SQL procedure successfully completed.

DBATOOLZ>
DBATOOLZ>
DBATOOLZ> select * from order_header;

no rows selected

DBATOOLZ> select * from order_line;

no rows selected

DBATOOLZ>


How would you handle this scenario without using a savepoint?

Regards,
- Vitaliy

you only "need" a savepoint if you CATCH and HANDLE an error.

Instead of hundreds of lines of code, let us do this small tiny example:

ops$tkyte%ORA9IR2> create table t1( x int primary key );

Table created.

ops$tkyte%ORA9IR2> create table t2( x references t1, y int check(y>0) );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure p( p_handle_error in boolean )
  2  as
  3      check_cons exception;
  4      pragma exception_init(check_cons,-2290);
  5  begin
  6      insert into t1 values(1);
  7      insert into t2 values(1,1);
  8      insert into t2 values(1,-1);
  9  exception
 10      when check_cons
 11      then
 12          if ( p_handle_error )
 13          then
 14              dbms_output.put_line( 'we dealt with it' );
 15          else
 16              raise;
 17          end if;
 18  end;
 19  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec p(false);
BEGIN p(false); END;

*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C002846) violated
ORA-06512: at "OPS$TKYTE.P", line 16
ORA-06512: at line 1


ops$tkyte%ORA9IR2> select * from t1;

no rows selected

ops$tkyte%ORA9IR2> select * from t2;

no rows selected

ops$tkyte%ORA9IR2> exec p(true);
we dealt with it

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select * from t1;

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

ops$tkyte%ORA9IR2> select * from t2;

         X          Y
---------- ----------
         1          1


<b>when you allow the exception to propagate - done for you.  when you catch and "ignore" (handle, fix, deal with) the error - you change the transaction semantics and the work is preserved (because apparently the error was NOT an error but an expected situation)</b>


So no, I don't see the use of savepoints here (how can part of a transaction "go bad", to me it has always been "all or nothing" - eg: rollback by the application would solve the problem upon encountering the error in all cases) 

I was with you right up to this point:

> So no, I don't see the use of savepoints here (how can part of a transaction
> "go bad", to me it has always been "all or nothing" - eg: rollback by the
> application would solve the problem upon encountering the error in all cases)

Are you saying that in UI->API call there's absolutely no place (or rather no "need") for a savepoint?

> I've not met one - not if you do what I call "proper exception handling" -
> meaning you CATCH on exceptions you EXPECT (like no_data_found for example)
> and can handle (meaning they are not really an error).

In my original example where you need to insert MASTER before the DETAIL and you EXPECT some exceptions during DETAIL insert and these exceptions cannot be validated before hand is the savepoint called for?

> All other exceptions are not caught - or if they are - you catch it, log it
> and RE-RAISE IT

Absolutely!

> please give me a tiny example that represents your issue -
> hundreds of lines of code, cannot deal with it.
>

Here's one simple example:

-------------------------------------------------------

drop table detail;
drop table master;
drop table logs;

create table master
( c number not null primary key );

create table detail
( t varchar2(1) not null,
c number not null,
foreign key (c) references master,
unique (t) );

create table logs
( who varchar2(100) );


create or replace package web_service as
--
-- call() is a webservice exposed externally via mod_plsql
-- it must return OK for success or FAIL for failure
-- it also must record every call to the logs table
--
procedure call(p_what in varchar2);
end web_service;
/

create or replace package body web_service as
procedure call(p_what in varchar2)
is
begin
insert into logs values (p_what);

savepoint start_point;

-- create master first for the FK to work
--
insert into master values (1);

-- now these detail records would be passed here via an array
-- and they could have dups, so we simulate a dup right here
--
insert into detail values ('1',1);

if ( p_what = 'fail' )
then
insert into detail values ('1',1);
end if;

-- this would be an htp.p call ...
dbms_output.put_line('OK');
exception
when dup_val_on_index then
rollback to start_point;
-- this would be an htp.p call ...
dbms_output.put_line('FAIL');
end call;

end web_service;
/

set serveroutput on

-- simulate mod_plsql
begin
web_service.call('fail');
commit;
end;
/

select * from logs;
select * from master;
select * from detail;

delete logs;
delete detail;
delete master;

-- simulate mod_plsql
begin
web_service.call('ok');
commit;
end;
/

select * from logs;
select * from master;
select * from detail;

-------------------------------------------------------

Is savepoint justified?

Regards,
- Vitaliy

> ... NO ONE IS THE WISER
>

I don't know where you got the idea that this is a wise game -- it all started when you stated this:

> wish PLSQL did not have commit nor rollback

I was just surprised by your statement and wanted to know if there was anything constructive that could be done to do without commit or rollback. Obviously there isn't.

> why do you need a savepoint, JUST ROLLBACK would do it.
>

Sure ROLLBACK would do -- that was the point of this example -- we do need rollback, commit or a rollback to savepoint for certain types of processing.

> it is your requirement to "return something upon failure"
> that is the issue here.
>

It's not an "issue" it's a representation of thousands of examples in the real world.


> and actually, in your example, if the insert into log fails, you
> silently do not log it - return failure - therefore - that insert
> into log is not necessary because sometimes it works and sometimes
> it doesn't and NO ONE IS THE WISER.
>

It's an example - tiny, stripped down example per your request.

well, thank you for your time ... and the next time you plug ROLLBACK or COMMIT in your code I am sure you'll remember this thread where you said:

> wish PLSQL did not have commit nor rollback

> You understand why I say that right....
>

I do ... and thank you for being honest ...


> Because 9999 times out of 10,000 is it used incorrectly - blatantly wrong.
> And for the 1 time out of 10,000 it is used correctly, well, maybe it is
> just not worth it.
>
> I feel the same way about triggers.
> And autonomous transactions
> and the dreaded WHEN OTHERS then NULL;

If what you just said (above) were your original response to my initial question that would have been the end of it -- I agree with you 100%.

Again ... thank you for being honest.

Regards,
- Vitaliy

Hi Tom,

What does "TO" lock type means, in which scenario it can co-exist with TX and TM locks from the same session?

Thanks

temporary object lock

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> create global temporary table gtt ( x int );

Table created.

ops$tkyte%ORA10GR2> insert into gtt values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> select object_id from user_objects where object_name = 'GTT';

 OBJECT_ID
----------
     60770

ops$tkyte%ORA10GR2> select type, id1, (select object_name from user_objects where object_id = ID1) oname
  2    from v$lock where sid = (select sid from v$mystat where rownum=1);

TY        ID1 ONAME
-- ---------- -----
TO      60770 GTT
TX     458763

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> select type, id1, (select object_name from user_objects where object_id = ID1) oname
  2    from v$lock where sid = (select sid from v$mystat where rownum=1);

TY        ID1 ONAME
-- ---------- -----
TO      60770 GTT
TM      60769 T
TX     458763



you'll have a TX because you are a transaction, you'll have TM's on other tables you modify:

 

Great example ... Thanks Tom !!!

Tom,
Please can you let me know if "insert /*+ append */ select * from .... " will put an exclusive lock on that table. I have a couple of process running parallel and it happens that two "insert /*+ append */ select * from .... " might run at a same time.
Thanks in advance

Tom,
I was reading section Examples of Concurrency under Explicit Locking in the manual http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#3112

In this it is mentioned a time point 18 Transaction 1 issues LOCK TABLE scott.dept IN SHARE MODE and at time point 23 Transaction 2 issues SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc; and it is mentioned as row is returned however I think if T1 has acquired share mode lock then by definition of this type of Lock T2 should be blocked and should wait.

Have I understood something wrong here?

Session 1

SCOTT@test>select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.2.0 - Production
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


SCOTT@test>lock table dept in share mode;

Table(s) Locked.

SCOTT@test>


Now I try this in Session 2

SCOTT@test>select loc from dept where deptno=20 for update of loc
  2  /

-- this blocked till I rollback or commit in session 1

It looks to me select ... for update is a Row Exclusive type of Lock and not Row share type of Lock.

Your insights will help me to understand my mistakes.

Can I then say that from Oracle 9iR2 onwards Select ... for update takes an row exclusive lock and not row share lock?

ops$tkyte%ORA9IR2> select * from v$lock where sid = (select sid from v$mystat where rownum=1);

no rows selected

ops$tkyte%ORA9IR2> select * from emp for update;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

ops$tkyte%ORA9IR2> select * from v$lock where sid = (select sid from v$mystat where rownum=1);

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
59ADC60C 59ADC718          8 TX     327722      41160          6          0          3          0
59AAE544 59AAE558          8 TM      32325          0          3          0          3          0

ops$tkyte%ORA9IR2> select object_id from user_objects where object_name = 'EMP';

 OBJECT_ID
----------
     32325

Tom,

Oracle 9.2.0.7

We have jobs that insert into a 3rd party table.
These jobs have to run concurrently.
Sometimes they try to insert the same PK into the table, so the 2nd session waits until the 1st session commits, then the 2nd session gets an ORA-00001 unique constraint error. Sometimes 2nd session may wait for an hour or so before getting the ORA-00001 error.

Is there a way for 2nd session to know that 1st session has already inserted PK so 2nd session can avoid inserting same record?

What would be nice is an INSERT...NOWAIT, then 2nd session would get immediate response back.

Any ideas on this?

Thanks,

Robert.

Hallo Tom
In one of our databases several sessions are performing inserts and updates on the same table. Although I'm working with sequences, I'm getting ORA-02049 sometimes. No session will ever try to update rows that are manipulated by another session. I tried to force this error in a sample.

create table tb_test(tid number, text varchar2(20));

insert into tb_test values(1,'A');
insert into tb_test values(2,'B');
commit;

CREATE OR REPLACE procedure sp_A as
i number := 0;
v_old number;
v_new number;
begin
while i < 10000 loop
select tid
into v_old
from tb_test
where text = 'A';

if v_old = 1 then
v_new := 100;
else
v_new := 1;
end if;

update tb_test
set tid = v_new
where tid = v_old;

i := i + 1;
end loop;
exception
when others
then dbms_output.put_line('Error A');
raise;
end;
/


CREATE OR REPLACE procedure sp_B as
i number := 0;
v_old number;
v_new number;
begin
while i < 10000 loop
select tid
into v_old
from tb_test
where text = 'B';

if v_old = 2 then
v_new := 200;
else
v_new := 2;
end if;

update tb_test
set tid = v_new
where tid = v_old;

i := i + 1;
end loop;
exception
when others
then dbms_output.put_line('Error B');
raise;
end;
/

SESSION A:
SQL> begin sp_a; end;
/

SESSION B:
SQL> begin sp_b; end;
/

The test:
Procedure sp_a in session A will be started a little
bit more early than procedure sp_b in session B.

The result:
sp_a in session A will be completed in a few seconds.
sp_b in session B will take a very long time to be
completed.
If a commit is performed in session A, session B
will be completed in some seconds as well.

My question:
Why does session B wait for a commit in session A ?
Without a commit in session A, session B will be completed
as well, but much slower.
Session A only manipulates the row with the text 'A'
and session B only the row with the text 'B'.
In my opinion there's no need to wait.
What's happening here ? I'm surprised.

Thanks for your help.
Robert

SELECT TID
FROM
 TB_TEST WHERE TEXT = 'A'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute  10000      0.34       0.35          0          0          0           0
Fetch    10000      0.51       0.50          0     102937          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.86       0.86          0     102937          0       10000

SELECT TID
FROM
 TB_TEST WHERE TEXT = 'B'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.37       0.34          0          0          0           0
Fetch    10000      5.86       7.45          0    2923250          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      6.24       7.79          0    2923250          0       10000

drop table t;
create table t ( x int, y int ) tablespace manual;
insert into t values (1,1);
commit;
exec dbms_stats.gather_table_stats( user, 'T' );
select * from t;
variable a refcursor
variable b refcursor
variable c refcursor
alter session set events '10046 trace name context forever, level 12';
begin
    open :a for select * from t a;
    open :b for select * from t b;
    open :c for select * from t c;
end;
/
print a
begin
    for i in 1 .. 10000
    loop
        update t set x = x+1;
        commit;
    end loop;
end;
/
print b
print c
exit

Thank you Tom !
Now I have to find a workaround.
In my productive system several sessions are performing inserts and updates on the same table like I did in the
sample. Not a lot of updates but perhaps at the same time. Sometimes I get ORA-02049. All this happens in a trigger.

So the only way to avoid this errors is perhaps to
increase the parameter DISTRIBUTED_LOCK_TIMEOUT or
to retry the last DML operation. Is that right ?
At the moment DISTRIBUTED_LOCK_TIMEOUT is set to
the value 60.

Here's a scratch from my trigger, that's used to
register loaded files in my system. The trigger can
be fired from several sessions.
I did it in this way to avoid leaks in the order of id's
when an INSERT fails.
Sometimes ORA-02049 raises an exception here.

...
BEGIN
SELECT SF.SOURCEFILEID
INTO v_SourcefileId
FROM TB_SOURCEFILE SF
WHERE SF.SOURCEFILE_NAME = :NEW.SOURCEFILE_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- create a new entry...
BEGIN
-- Get a Dummy-Id and mark it a negative...
SELECT (-1)*SEQ_DUMMY_ID.NEXTVAL
INTO v_DummyId
FROM dual;

-- Try to insert a row into TBT_PROTOCOL...
INSERT INTO TB_SOURCEFILE
(
SOURCEFILEID,
SOURCEFILE_NAME,
...
)
VALUES
(
v_DummyId,
:NEW.SOURCEFILE_NAME,
:NEW.SOURCEFILE_PREFIX,
...
);

-- INSERT is done, so we can get the final ID...
SELECT SEQ_SOURCEFILE_ID.NEXTVAL
INTO v_SourcefileId
FROM dual;


-- replace the dummy-id
UPDATE TB_SOURCEFILE
SET SOURCEFILEID = v_SourcefileId
WHERE SOURCEFILEID = v_DummyId;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,'Registering Data-File' || :NEW.SOURCEFILE_NAME || ' in TB_SOURCEFILE failed !');
END;
END;
...

Greetings
Robert

Tom,

We have this Java application gone in Production recently, running on Oracle RAC 10.2.0.1 on linux. The middle-tier is Jboss application server using a connection pool to the database. I frequently see blocking sessions and I am working with the Developers to address the issue. I noticed that many times, the sql pertaining to the waiting sessions are INSERT statements. I think the PK index on the table is what is causing the issue. I sometimes have multiple sessions with INSERT commands waiting on other sessions (show up in dba_blockers/dba_waiters as well as TX lock entries in v$lock tables). I can give you more details if you want, but the general question is what are the conditions under which an INSERT statement would be blocked? I am not sure if this is an RAC related issue also.

thanks,
RN

Hi Tom,

Many Thanks for helping the entire Oracle community across the world.

I am intrested to find out the SQL issued by "Blocking User".

I have simulated the scneario as follows:

Session 1:

SQL> select user from dual;

USER
------------------------------
MAN

SQL> drop table t1;

Table dropped.

SQL> create table t1 (x number, y number);

Table created.

SQL> insert into t1 values(1,2);

1 row created.

SQL> insert into t1 values(3,4);

1 row created.

SQL> commit;

Commit complete.

SQL>  update t1 set x=10 where y=2;

1 row updated.
SQL>


Now I run the following in session 2:

SQL> select user from dual;

USER
------------------------------
SCOTT

SQL> update man.t1 set y=20 where x=1;
-- session hangs... 

I use the following query to find the blocking user :
SELECT /*+ CHOOSE */
 bs.username "Blocking User", 
 bs.username "DB User", 
 ws.username "Waiting User", 
 bs.sid "SID", bs.serial# "Serial#", 
 ws.sid "WSID", 
 bs.sql_address "address", 
 bs.sql_hash_value "Sql hash", 
 bs.program "Blocking App", 
 ws.program "Waiting App", 
 bs.machine "Blocking Machine", 
 ws.machine "Waiting Machine", 
 bs.osuser "Blocking OS User", 
 ws.osuser "Waiting OS User", 
  DECODE(wk.TYPE, 
 'MR', 'Media Recovery',   'RT', 'Redo Thread',  'UN', 'USER Name', 
 'TX', 'Transaction',  'TM', 'DML', 'UL', 'PL/SQL USER LOCK', 
 'DX', 'Distributed Xaction', 'CF', 'Control FILE', 'IS', 'Instance State', 
 'FS', 'FILE SET', 'IR', 'Instance Recovery', 'ST', 'Disk SPACE Transaction', 
 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 
 'LS', 'LOG START OR Switch', 'RW', 'ROW Wait','SQ', 'Sequence Number', 
 'TE', 'Extend TABLE', 'TT', 'Temp TABLE', wk.TYPE) lock_type, 
 DECODE(hk.lmode, 0, 'None', 1, 'NULL',  2, 'ROW-S (SS)',  3, 'ROW-X (SX)', 
  4, 'SHARE', 5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE', TO_CHAR(hk.lmode)) mode_held, 
 DECODE(wk.request, 0, 'None', 1, 'NULL',  2, 'ROW-S (SS)',  3, 'ROW-X (SX)', 
  4, 'SHARE', 5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE',  TO_CHAR(wk.request)) mode_requested, 
 TO_CHAR(hk.id1) lock_id1, 
 TO_CHAR(hk.id2) lock_id2 
FROM 
   v$lock hk,  v$session bs, 
   v$lock wk,  v$session ws 
WHERE 
     hk.block   = 1 
AND  hk.lmode  != 0 
AND  hk.lmode  != 1 
AND  wk.request  != 0 
AND  wk.TYPE (+) = hk.TYPE 
AND  wk.id1  (+) = hk.id1 
AND  wk.id2  (+) = hk.id2 
AND  hk.sid    = bs.sid(+) 
AND  wk.sid    = ws.sid(+)
ORDER BY 1



Questions: 

Now session 1 is blocking the session 2 . Here 'man' is the blocking user and 'scott' blocked user.

Please help me in writing the SQL to find out 

a) Blocking User, SID of Blocking Person, Serial# of Blocking Person & SQL (update command) executed by Blocking person i.e. update t1 set x=10 where y=2;

It is to be noted here that user 'MAN' can executed any SQL after blocking the user 'SCOTT' , But i amm interested in only the SQL which has caused the lock.

b) Blocked User , SID of Blocked Person, Serial# of Blocked Person & SQL (update command) executed by Blocked person i.e. update man.t1 set y=20 where x=1;

c)Is the query which i have used to find out the locks is optimised? If yes, Please modify the same query to get the SQL ran as asked in a) and b) above, if possible.

Regards.

Sir,

We are developing a web based accounting system accessed by several users at office hours. In the system, one bank account is to be accessed by several users for payment (issuing cheque) or for receipt. As a result the balance will be modified at every transaction. Database is Oracle 10g. Since one account is being accessed by several users, all are waiting for commit by previous users because of LOCKING the balance amount of that bank account. As a result the system gets slower and sometimes end users feel link failure. Please advice me how to handle this untoward situation. Modification is utterly required at every transaction because cheque issue depends on balance amount. I am waiting for your reply. I am quite stuck in it.

Dear Tom,
Is there any relation between utl_http tranfer timeout default value(60 seconds) and distributed_transaction_timeout. When we asked other system to increase the tranfer timeout default value for utl_http they said they can't change it because it needs to change the distributed_transaction_timeout and it will affect other remote systems they are dealing with.
They are a big company so we can't comment on it without concreate details. Oracle docs doesn't state anything like this. Do they really can't use utl_http.set_transfer_timeout and has to use distributed_transaction_timeout?
I would be really gratefult to you, Sirji.

Thanks in advance

Dear Tom,
Yes I meant distributed_lock_timeout. Thanks a lot for your reply.

Tom:

I am encountering the same issues described earlier in this thread. I am highlighting it below for your quick refernce.

<<
Tom,
Please can you let me know if "insert /*+ append */ select * from .... " will put an exclusive lock on that table. I have a couple of process running parallel and it happens that two "insert /*+ append */ select * from .... " might run at a same time.
Thanks in advance


Followup March 21, 2007 - 7pm Central time zone:

direct path inserts lock the table (segment if you are using a partition clause in the insert)

you would want to use a parallel direct path insert if you need more than one process inserting at the same time.

>>

In our case, I have a partitioned table. More than one ETL scripts can run at the same loading into different partitions (subpartitions). But i am observing that as sson as second process (does loading into different partitions) is started, it goes to BLOCKING mode and it gets blocked until the first ETL script is done.
I also see that "Blocking session is taking Exclusive lock on the table level".

Developer says they are loading into different partitions (for different months of data). so there is no chance of likely hitting the same partitions by two process.

Questions:
=========
a) Is it the way oracle works that no two process can do
concurrent inserts into the same partition table ?
If yes, any workaround available ?

b) If DEFAULT parallelism is enabled at table level and APPEND hint is not used in SQL, Is the effect equivalent to "INSERT /*+ APPEND */ meaning DIRECT PATH INSERT ?
How oracle interprets this when executing ?

Backgound about the table and session settings:
==========================
1. we have default parallelism enabled at the table level.
2. we have set DOP as 32 at session level for PARALLEL DML/PARALLEL query

script is here:

INSERT
INTO ETL.ENDG_RSRVS_MCH
SELECT /* parallel (a,4) (d,4) (c,4) */
a.gl_effective_dt,
MIN(a.acctng_perd_beg_dt),
MAX(a.acctng_perd_end_dt),
a.document_status_cd,
a.document_type_cd,
'H' ORPH_RJCT_IND,
'R' rndrd_unrd_ind,
a.premium_loss_cd,
a.NM_CONTRACT_NO,
'H' orphan_status_cd,
a.incomplt_treaty_in,
a.mach_nm_orig_cd,
a.MIS013_COUNTRY_CD,
a.kind_cd,
a.HOLD_IN,
a.rein_proc_ctgry_cd,
a.rein_direction_cd,
a.rein_rndrg_freq_cd,
a.rein_no,
a.rein_uw_yr_no,
a.rein_efctv_dt,
a.rein_key_cd,
a.fac_certificate_no,
a.xtrnl_rein_ref_no,
a.captive_in,
a.asco_mis012_cur_cd,
a.nm_adjustment_cd,
a.source_system_id,
a.mis780_asco_code,
a.POLICY_XPIRTN_DT,
a.INSURED_NM,
a.policy_no,
a.policy_efctv_dt,
a.mis780_comp_code,
a.mach_nm,
a.rein_no_layer_no,
a.rein_no_section_no,
a.rein_no_foreign_dom_cd,
' ' reltnshp_typ_cd,
' ' src_rein_no,
'01-jan-1800' src_rein_efctvdt,
'0' src_rein_uw_yrno,
' ' src_rein_key_cd,
' ' src_rein_no_layer_no,
' ' src_rein_no_section_no,
' ' src_rein_no_foreign_dom_cd,
a.puc_dsp_div,
a.puc_dsp_puc,
a.puc_dsp_sec,
a.LOSS_DT,
a.DEP_DDSP_DEPT,
a.term_condition_cd,
a.matrix_cd,
a.mis404_tax_st_cd,
a.credited_branch_no,
a.working_branch_no,
a.mis020_catas_cd,
a.mis020_catas_yr_no,
a.claim_branch_no,
a.claim_case_no,
a.key_branch_no,
a.key_case_no,
a.cla_id,
NVL(c.rdps_maj_class_no, d.rdps_maj_class_no) mis028_maj_class,
c.MIS028_STMT_LINE,
NVL(d.transaction_typ_cd, c.transaction_typ_cd) transaction_typ_cd,
NVL(d.trans_sub_typ_cd, c.trans_sub_typ_cd) trans_sub_typ_cd,
c.gen_ledger_acct_no,
NVL(d.reinsurer_no, a.reinsurer_no) reinsurer_no,
NVL(d.reinsurer_brnch_cd, a.reinsurer_brnch_cd) reinsurer_brnch_cd,
NVL(d.intermediary_no, a.intermediary_no) intermediary_no,
NVL(d.intmdry_brnch_cd, a.intmdry_brnch_cd) intmdry_brnch_cd,
CASE
WHEN a.incomplt_treaty_in IN ('Y')
THEN
' '
ELSE
NVL(d.participant_typ_cd, ' ')
END participant_typ_cd,
c.BALSHT_GL_ACCT_NO,
MAX(a.mach_nm_orig_ts),
COUNT(*) count_keys,
SUM(NVL(d.asco_financial_am, c.asco_financial_am)) asco_financial_am
FROM (
SELECT /* parallel (a,4) (d,4)*/
d.sub_header_no,
DECODE(a.mach_nm_orig_cd, 'M', DECODE(document_type_cd, 'MCH', 'MN', 'MO'
), 'N', DECODE(document_type_cd, 'NMC', 'NN', 'NO')) mach_nm,
a.document_no,
a.mach_nm_orig_cd,
a.gl_effective_dt,
a.acctng_perd_beg_dt,
a.acctng_perd_end_dt,
a.document_status_cd,
a.document_type_cd,
a.premium_loss_cd,
a.NM_CONTRACT_NO,
'H' orphan_status_cd,
a.incomplt_treaty_in,
a.MIS013_COUNTRY_CD,
a.kind_cd,
a.HOLD_IN,
a.rein_proc_ctgry_cd,
a.rein_direction_cd,
a.rein_rndrg_freq_cd,
a.rein_no,
a.POLICY_XPIRTN_DT,
a.INSURED_NM,
a.rein_uw_yr_no,
a.rein_efctv_dt,
a.rein_key_cd,
a.fac_certificate_no,
a.xtrnl_rein_ref_no,
a.captive_in,
a.asco_mis012_cur_cd,
a.NM_ADJUSTMENT_CD,
a.source_system_id,
a.mis780_asco_code,
a.policy_no,
a.policy_efctv_dt,
a.mis780_comp_code,
a.mach_nm_orig_ts,
a.rein_no_layer_no,
a.rein_no_section_no,
a.rein_no_foreign_dom_cd,
a.reinsurer_no,
a.reinsurer_brnch_cd,
a.intmdry_brnch_cd,
a.intermediary_no,
d.puc_dsp_div,
d.puc_dsp_puc,
d.puc_dsp_sec,
d.LOSS_DT,
d.DEP_DDSP_DEPT,
d.term_condition_cd,
d.matrix_cd,
d.mis404_tax_st_cd,
d.credited_branch_no,
d.working_branch_no,
d.mis020_catas_cd,
d.mis020_catas_yr_no,
CASE
WHEN a.mach_nm_orig_cd = 'M'
THEN
d.claim_branch_no
WHEN a.mach_nm_orig_cd = 'N'
THEN
a.nm_claim_branch_no
ELSE
0
END claim_branch_no,
CASE
WHEN a.mach_nm_orig_cd = 'M'
THEN
d.claim_case_no
WHEN a.mach_nm_orig_cd = 'N'
THEN
a.nm_claim_case_no
ELSE
0
END claim_case_no,
CASE
WHEN a.mach_nm_orig_cd = 'M'
THEN
d.key_branch_no
WHEN a.mach_nm_orig_cd = 'N'
THEN
a.nm_key_branch_no
ELSE
' '
END key_branch_no,
CASE
WHEN a.mach_nm_orig_cd = 'M'
THEN
d.key_case_no
WHEN a.mach_nm_orig_cd = 'N'
THEN
a.nm_key_case_no
ELSE
' '
END key_case_no,
d.cla_id
FROM (
SELECT /* parallel (a,4)*/
*
FROM src.theader a
WHERE gl_effective_dt = TO_DATE('01-JUL-09', 'dd-mon-yy')
AND document_status_cd = 'A'
AND mach_nm_orig_cd = 'M'
AND document_no NOT IN (
SELECT document_no
FROM etl.theader_reject)
AND rein_direction_cd = SUBSTR('CED1', 1, 3)
AND rein_direction_cd = SUBSTR('CED1', 1, 3)
AND ((SUBSTR('CED1', 4, 1) IS NULL)
OR (source_system_id IN (
SELECT source_system_id
FROM etl.proc_ref
WHERE rein_direction_cd = 'CED1')))) a, src.tsub_header d
WHERE a.document_no = d.document_no) a, (
SELECT /* parallel (y,4)*/
document_no,
sub_header_no,
rdps_maj_class_no,
transaction_typ_cd,
trans_sub_typ_cd,
reinsurer_no,
reinsurer_brnch_cd,
intermediary_no,
intmdry_brnch_cd,
NVL(TRIM(participant_typ_cd), 'XXX') participant_typ_cd,
rdps_bus_type_cd,
asco_financial_am
FROM src.tpartcpnt_explsn y
WHERE transaction_typ_cd IN (1, 4, 5, 6, 8, 9, 10, 13, 18, 22, 29, 30, 36,
37, 44, 45)
AND trans_sub_typ_cd IN (11, 41, 51, 61, 81, 91, 101, 131, 181, 221, 291,
301, 302, 361, 371, 441, 451)) d, (
SELECT /* parallel (b,4)*/
*
FROM src.tsub_header_fncl b
WHERE transaction_typ_cd IN (1, 4, 5, 6, 8, 9, 10, 13, 18, 22, 29, 30, 36,
37, 44, 45)
AND trans_sub_typ_cd IN (11, 41, 51, 61, 81, 91, 101, 131, 181, 221, 291,
301, 302, 361, 371, 441, 451)) c
WHERE a.document_no = c.document_no
AND a.sub_header_no = c.sub_header_no
AND c.document_no = d.document_no(+)
AND c.sub_header_no = d.sub_header_no(+)
AND c.rdps_maj_class_no = d.rdps_maj_class_no(+)
AND c.rdps_bus_type_cd = d.rdps_bus_type_cd(+)
AND c.transaction_typ_cd = d.transaction_typ_cd(+)
AND c.trans_sub_typ_cd = d.trans_sub_typ_cd(+)
GROUP BY a.gl_effective_dt, a.document_status_cd, a.document_type_cd, a.premium_loss_cd
, a.NM_CONTRACT_NO, a.incomplt_treaty_in, a.mach_nm_orig_cd, a.MIS013_COUNTRY_CD
, a.kind_cd, a.HOLD_IN, a.rein_proc_ctgry_cd, a.rein_direction_cd, a.rein_rndrg_freq_cd
, a.rein_no, a.rein_uw_yr_no, a.rein_efctv_dt, a.rein_key_cd, a.fac_certificate_no
, a.xtrnl_rein_ref_no, a.captive_in, a.asco_mis012_cur_cd, a.nm_adjustment_cd,
a.source_system_id, a.mis780_asco_code, a.POLICY_XPIRTN_DT, a.INSURED_NM, a.policy_no
, a.policy_efctv_dt, a.mis780_comp_code, a.mach_nm, a.rein_no_layer_no, a.rein_no_section_no
, a.rein_no_foreign_dom_cd, a.puc_dsp_div, a.puc_dsp_puc, a.puc_dsp_sec, a.LOSS_DT
, a.DEP_DDSP_DEPT, a.term_condition_cd, a.matrix_cd, a.mis404_tax_st_cd, a.credited_branch_no
, a.working_branch_no, a.mis020_catas_cd, a.mis020_catas_yr_no, a.claim_branch_no
, a.claim_case_no, a.key_branch_no, a.key_case_no, a.cla_id, NVL(c.rdps_maj_class_no
, d.rdps_maj_class_no), c.MIS028_STMT_LINE, NVL(d.transaction_typ_cd, c.transaction_typ_cd
), NVL(d.trans_sub_typ_cd, c.trans_sub_typ_cd), c.gen_ledger_acct_no, NVL(d.reinsurer_no
, a.reinsurer_no), NVL(d.reinsurer_brnch_cd, a.reinsurer_brnch_cd), NVL(d.intermediary_no
, a.intermediary_no), NVL(d.intmdry_brnch_cd, a.intmdry_brnch_cd), CASE
WHEN a.incomplt_treaty_in IN ('Y')
THEN
' '
ELSE
NVL(d.participant_typ_cd, ' ')
END, c.BALSHT_GL_ACCT_NO

<< The developers got it half right - they used parallel query.
Then they tried to "do it yourself parallelize" things. Why are they running more than one thread there? Just do ONE thread, let us parallel it.
>>

I prefer second choice which has more control.

In the first choice, just to clarify:

we are already enforcing limited parallelism at session level (DOP on tables and indexes are set to DEFAULT):

alter session force parallel query parallel 32;
alter session force parallel dml parallel 32 ;


Is this good enough to have oracle does parallelism on its own ?

Will oracle still block the second session if we start more than one scripts of 1st choice (ofcourse, data does not go into the same partition) using parallel way ?

The issue earlier was, when two scripts (of first choice) kicked off, second one goes into blocking mode.

Hi Tom,
Oracle version: 10gR2
we have the db architecture as:
DB1:
Table A

DB2:
Table B
and there is a dblink from DB1 -> DB2 and DB2 -> Db1.
The requirement was to keep the two tables in sync.And the way it was coded was to use triggers on both these tables(a BEFORE UPDATE ROW LEVEL TRIGGER)
Now, my question if I update a record in DB1 which casues the trigger on DB1 to fire and tries to update the record in DB2 and which in turn fires the trigger and using a dblink tries to update record in DB1.
My question is: is the session ID in DB1 and the session that is spawned via a dblink to update the record in DB1 are different?
Because, when I've seen the code for the trigger there is an exception handler for "resource busy".I'm assuming that the cascading effect of triggers is resolved since the first update at DB1 has locked the records and when a session is spawned from DB2, it cannot see the changes and sees that the row has been locked.
can you explain how the sessions are spawned when using db links??

Hi Tom,
I understand your concern about the design of the DB.
As a developer I move forward by accepting the DB configuration and system design.
I do have concerns but it is not often very well addressed.
And I think the database design will become complex as long as there is something called business layer(Business and policy) and we called the systems who implement the application.Quite often the Business team do not have good knowledge about the database and even a slight misunderstanding might result to bad designs
I think especially in DB centric applications the business team has to be a part of systems team thereby developing efficient applications

PS: can you point me to the documentation or to metalink where there is an explanation on spawned sessions (like how long the spawned session would last and is there a relationship between spawned session and parent session)by using dblinks.I tried to peek through the documentation but that was just basic

Tom,

Can you explain why a session identified as holding a blocking lock in the original query (as corrected) would not show up as holding a lock on any object in v$locked_objects?

Most of the time I have seen it the session is inactive and seems to be waiting on a commit. However, this does not seem to always be the case.

Thanks for you help

The original query from above was:

select 'Warning: Check this concurrent request for any lock: req_id: '||fcr.request_id ||
' Sess_id: '||vs.sid||' Blocker: '||lb.sid||' Locked Object: '||do.object_name
from
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_processes fcp,
v$process vp,
v$session vs,
v$lock lw,
v$lock lb , v$locked_object lo , dba_objects do
where fcr.actual_start_date < (sysdate-0.0)
and fcr.controlling_manager = fcp.concurrent_process_id
and vp.pid = fcp.oracle_process_id
and vp.addr = vs.paddr
and vs.sid = lw.sid
and lw.id1 = lb.id1
and lw.sid = lo.session_id
and lo.object_id = do.object_id
and fcr.concurrent_program_id = '36888'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
--and sw.lockwait is not null
--and sb.lockwait is null
and lb.block =1
;


On December 3, 2001 you mentioned finding related information in v$locked_object.

I think I have found my answer though. The lock is a DX (distributed transaction lock) thus it is not related to a particular object. The session it is blocking shows a current sql that is fully contained in the local database but has another cursor that is apparently trying to query across the database link.

Steve

Sorry about that, somehow my attempt at cutting and pasting did not work. The original sql was:

1 select S1.username as " Waiting User",
2 S1.osuser as "OS User",
3 W.session_id as "SID",
4 P1.spid as "PID",
5 S2.username as "Holding User",
6 S2.osuser as "OS User",
7 H.session_id as "SID",
8 P2.spid as "PID"
9 from v$process P1,
10 v$process P2,
11 v$session S1,
12 v$session S2,
13 sys.dba_locks W,
14 sys.dba_locks H
15 where H.mode_held = 'Blocking'
16 and H.mode_held = 'Null'
17 and W.mode_requested != 'None'
18 and W.lock_type (+)= H.lock_type
19 and W.lock_id1 (+)= H.lock_id1
20 and W.lock_id2 (+)= H.lock_id2
21 and W.session_id = S1.sid (+)
22 and H.session_id = S2.sid (+)
23 and S1.paddr = P1.addr (+)
24 and S2.paddr = P2.addr (+);

Hi Tom
In my statspack report I see "enq: TX - row lock contention" on indexes (actually all top 5 are indexes). I'm trying to imagine how this can happen. The obvious scenario is when two sessions are inserting the same value in primary key or another unique constraint. Another scenario I imagine is when someone is deleting or updating an primary key in an foreign key relation, and someone is trying to reference the original value. Is this correct, and is there other scenarios that generate lock wait on indexes?

There are no bitmap indexes in use.

select index_type,count(*) from dba_indexes where table_owner='ES' group by index_type;

INDEX_TYPE                  COUNT(*)               
--------------------------- ---------------------- 
FUNCTION-BASED NORMAL       1                      
IOT - TOP                   2                      
LOB                         14                     
NORMAL                      785                    

Tom

I have a case where i need to lock multiple records in desired order using select for update, but not sure whether Oracle provides mechanism to achieve it (We need support Oracle 10 and 11).

e.g.

create table big_table as select * from dba_objects;

select * from big_table where object_id in (2,5,9) for update ;

Q1:
If i run the above select for update in two concurrent transactions, my understanding is it's possible that since the order of update is not guaranteed, the two transactions may deadlock each other. Is it correct? And just because of my sql is 'in (2,5,9)' does not mean that oracle will select and update the records in that order right?

Q2:
A variant
select * from big_table where object_id in (2,5,9) for update select * from big_table where object_id in (2,5,9) order by object_id for update

Will the order by work? the trace shows

sql_id=1mbmyfwjjt2qt.
Current SQL statement for this session:
select * from big_table where object_id in (2,5,9) order by object_id for update

============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation            | Name     | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |          |       |       |    31 |           |
| 1   |  FOR UPDATE          |          |       |       |       |           |
| 2   |   SORT ORDER BY      |          |     2 |   354 |    31 |  00:00:01 |
| 3   |    TABLE ACCESS FULL | BIG_TABLE|     2 |   354 |    30 |  00:00:01 |
----------------------------------------+-----------------------------------+

So it seems oracle sorts first then do the update?

Q3:
If order by does not work, can I setup an index on object_id (e.g. create index idx_big_table on big_table(object_id)) to 'force' the access path of the SQL to select and update in a deterministic order?

Thanks a lot

Tom

Thanks a lot for the response, that helps a lot and I'd like to get a bit further clarification.

I understand the part that if two concurrent transactions are running the same SQL, and the same plan, then generally they should hit the data in the same order. My case is however if
transaction T1 runs
select * from big_table where object_id in (2,5,9)
and T2 runs
select * from big_table where object_id in (2,7,9)

In that case they are not the same plan, can I still assume that rows with object id 2 and 9 will be accessed/updated in the same order? (Note I do not need Oracle to ensure that rows will be accessed in the specific order of 2, 5, 9 or 2, 7, 9. I just need the same set of rows to be accessed/updated in a same order to avoid deadlock)

When i introduced 'order by', I noticed that the the execution plan (as shown above) shows that for update runs after the sort. If that is indeed the case, I assume I can use order by and then Oracle will ensure the order? That shall give me comfort that I won't run into deadlock in a production system.

By the way, the reason I don't want to lock records one by one is because I will have a large number of records to lock (say 1000 records out of a 10-million-records table) so performance will suffer.

Thanks a lot.

Tom

Thanks a lot for the response. I do not mind being blocked, but I do want to avoid deadlock as much as possible. If i create an index on the object_id, and if we can ensure that the query will use the index, then the two transactions will not deadlock each other right?

Hellow Tom.
There is the following situation. For targets of construction of test environment I try to emulte remote DB in my test DB by creating single schema. I direct DBLink from my test DB to same DB as to remote DB and further I try select data through the DBLink. When I do it in main transaction it work well. When I select data by DBLink in autonomous transation I have DX lock (dead).

SQL>select * from v$lock
 SID TYPE  ID1  ID2  LMODE  REQUEST  CTIME  BLOCK
---- ---- ---- ---- ------ -------- ------ ------
  39 DX     22    0      4        0    108      1
 126 DX     22    0      0        6    108      0

I ascertain that using circular DBLink is bad practice but I want understand why happen this lock and how it come round.

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 2 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          for x in (select * from t@ora11gr2@loopback )
  5          loop
  6                  dbms_output.put_line( '==> ' || x.x );
  7          end loop;
  8          commit;
  9  end;
 10  /
==> 1

PL/SQL procedure successfully completed.

Tom,

If a select is run on a table in the remote database using dblink, do the records get locked?

Tom,

does Oracle 10g or 11g have possibility to explain the reason of ORA-02049. We need: table name, rowid and blocking transaction's global ID that prevent my transaction to make the lock. Can we get table name, rowid and global ID in SERVERERROR trigger or in other place?

Thanks & Regards,

Dmitry

Hi Tom,

In TOAD, in database--> Monitor--> session Browser--> Locks

it shows the users using different databases with their current status of locks whether their login is blocking others or not....
i want to know,where does those data come from.
Any help would be useful
Thank in advance.

Hi Tom,

How can track back the objects which locked the DB in recent past. And also at what exact time it locked the database. Is there a way?

Thanks,
Mihir