Thanks for your help. I will create indexes on foreign keys.

Reader,

Notice Tom said to index THE foreign key, not to index all foreign keys. Your reply seems to imply you will index all FKs which might not be necessary if your application doesn't delete or update all parent tables.

Deadlock trace file copied below


ORACLE_HOME = /opt/oracle/server/10.2.0
System name: SunOS
Release: 5.10
Version: Generic_141445-09
Machine: i86pc

Redo thread mounted by this instance: 1
Oracle process number: 334


*** 2011-10-07 09:38:24.798
*** ACTION NAME:() 2011-10-07 09:38:24.697
*** MODULE NAME:(JDBC Thin Client) 2011-10-07 09:38:24.697
*** SERVICE NAME:(SYS$USERS) 2011-10-07 09:38:24.697
*** SESSION ID:(297.9717) 2011-10-07 09:38:24.697
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00ad001d-0001c355 334 297 X 148 1040 S
TX-00840046-000296b8 148 1040 X 334 297 S
session 297: DID 0001-014E-000000FA session 1040: DID 0001-0094-00000173
session 1040: DID 0001-0094-00000173 session 297: DID 0001-014E-000000FA
Rows waited on:
Session 1040: obj - rowid = 00064D4D - AAGsZqABaAAHAvVAAA
(dictionary objn - 413005, file - 90, block - 1838037, slot - 0)
Session 297: obj - rowid = 00064D4D - AAGsZqABRAAGSJ1AAA
(dictionary objn - 413005, file - 81, block - 1647221, slot - 0)
Information on the OTHER waiting sessions:
Session 1040:
pid=148 serial=12927 audsid=133451942 user: 48/anad
O/S info: user: jboss, term: unknown, ospid: 1234, machine: app1-live-arch1
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:

insert into ARC_OUT_MESSAGE (MESSAGE, PRIORITY, PAYLOAD, SENDER, STATUS_ID, APPLICATION_INSTANCE_ID, CONSUMER_ID, PARTNER_ID, ROUTE_ID, DESTINATION, MESSAGE_TYPE_ID, TARIFF_ID, CREATED_TIME, SENT_TIME, OUT_MESSAGE_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
insert into ARC_OUT_MESSAGE (MESSAGE, PRIORITY, PAYLOAD, SENDER, STATUS_ID, APPLICATION_INSTANCE_ID, CONSUMER_ID, PARTNER_ID, ROUTE_ID, DESTINATION, MESSAGE_TYPE_ID, TARIFF_ID, CREATED_TIME, SENT_TIME, OUT_MESSAGE_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)
===================================================
PROCESS STATE
-------------
Process global information:
process: 16d70290b0, call: 16ddb84cb0, xact: 16dd4a37b8, curses: 16d7245590, usrses: 16d7245590
----------------------------------------
SO: 16d70290b0, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=334, calls cur/top: 16ddb84cb0/16ddb84cb0, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
----
Tom - Any idea what is causing the deadlock here during insert. Object 00064D4D is a non-unique btree index on the table. There is no bitmap index on the table, but there is a unique index on this table. Table is a partitioned table.

thanks
Jack

set echo on
drop table t;
create table t ( x int primary key, y int );
create index t_idx on t(y);
insert into t values ( 1, 1 );
set echo off
prompt in another session issue:
prompt insert into t values ( 2, 2 );;
pause
prompt in another session issue:
prompt insert into t values ( 1, 1 );;
insert into t values ( 2, 2 );

Object 00064D4D is a non-unique
btree index on the table. Why is this Bree index shown on the deadlock graph istead of the unique primary key ?

It is happening with inserts like you mentioned but the deadlock graph is misleading.

Hi Tom, I'm encountering the following error:

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00230010-0003bc15 192 780 X 152 765 X
TX-002b0011-00012d60 152 765 X 192 780 X

session 780: DID 0001-00C0-0000003B session 765: DID 0001-0098-000001B8
session 765: DID 0001-0098-000001B8 session 780: DID 0001-00C0-0000003B

Rows waited on:
Session 780: obj - rowid = 0000389F - AAADifAF1AAAPixAAK
(dictionary objn - 14495, file - 373, block - 63665, slot - 10)
Session 765: obj - rowid = 0000389F - AAADifAF1AAAPixAAP
(dictionary objn - 14495, file - 373, block - 63665, slot - 15)

----- Information for the OTHER waiting sessions -----
Session 765:
sid: 765 ser: 33 audsid: 58369041 user: 58/APPS flags: 0x8000041
pid: 152 O/S info: user: oracle, term: UNKNOWN, ospid: 9395
image: oracle@odprdabc
client details:
O/S info: user: applmgr, term: , ospid: 15708
machine: oaprdabc program: rwrun@oaprdabc (TNS V1-V3)
client info: 541
application name: APXIIMPT, hash value=2529902159
action name: Concurrent Request, hash value=1021472160
current SQL:

UPDATE PO_LINE_LOCATIONS_ALL PLL SET QUANTITY_BILLED = DECODE(:B11 , NULL, QUANTITY_BILLED , NVL(QUANTITY_BILLED, 0) + :B11 ), AMOUNT_BILLED = DECODE(:B10 , NULL, AMOUNT_BILLED , NVL(AMOUNT_BILLED, 0) + :B10 ), QUANTITY_FINANCED = DECODE(:B9 , NULL, QUANTITY_FINANCED , NVL(QUANTITY_FINANCED, 0) + :B9 ), AMOUNT_FINANCED = DECODE(:B8 , NULL, AMOUNT_FINANCED , NVL(AMOUNT_FINANCED, 0) + :B8 ), QUANTITY_RECOUPED = DECODE(:B7 , NULL, QUANTITY_RECOUPED , NVL(QUANTITY_RECOUPED, 0) + :B7 ), AMOUNT_RECOUPED = DECODE(:B6 , NULL, AMOUNT_RECOUPED , NVL(AMOUNT_RECOUPED, 0) + :B6 ), RETAINAGE_WITHHELD_AMOUNT = DECODE(:B5 , NULL, RETAINAGE_WITHHELD_AMOUNT , NVL(RETAINAGE_WITHHELD_AMOUNT, 0) + :B5 ), RETAINAGE_RELEASED_AMOUNT = DECODE(:B4 , NULL, RETAINAGE_RELEASED_AMOUNT , NVL(RETAINAGE_RELEASED_AMOUNT,

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=b3ccn206ys2w1) -----
UPDATE PO_LINE_LOCATIONS_ALL PLL SET QUANTITY_BILLED = DECODE(:B11 , NULL, QUANTITY_BILLED , NVL(QUANTITY_BILLED, 0) + :B11 ), AMOUNT_BILLED = DECODE(:B10 , NULL, AMOUNT_BILLED , NVL(AMOUNT_BILLED, 0) + :B10 ), QUANTITY_FINANCED = DECODE(:B9 , NULL, QUANTITY_FINANCED , NVL(QUANTITY_FINANCED, 0) + :B9 ), AMOUNT_FINANCED = DECODE(:B8 , NULL, AMOUNT_FINANCED , NVL(AMOUNT_FINANCED, 0) + :B8 ), QUANTITY_RECOUPED = DECODE(:B7 , NULL, QUANTITY_RECOUPED , NVL(QUANTITY_RECOUPED, 0) + :B7 ), AMOUNT_RECOUPED = DECODE(:B6 , NULL, AMOUNT_RECOUPED , NVL(AMOUNT_RECOUPED, 0) + :B6 ), RETAINAGE_WITHHELD_AMOUNT = DECODE(:B5 , NULL, RETAINAGE_WITHHELD_AMOUNT , NVL(RETAINAGE_WITHHELD_AMOUNT, 0) + :B5 ), RETAINAGE_RELEASED_AMOUNT = DECODE(:B4 , NULL, RETAINAGE_RELEASED_AMOUNT , NVL(RETAINAGE_RELEASED_AMOUNT, 0) + :B4 ), LAST_UPDATE_LOGIN = NVL(:B3 , LAST_UPDATE_LOGIN), REQUEST_ID = NVL(:B2 , REQUEST_ID) WHERE PLL.LINE_LOCATION_ID = :B1 OR (PLL.SHIPMENT_TYPE = 'PLANNED' AND PLL.LINE_LOCATION_ID = (SELECT PLL2.SOURCE_SHIPMENT_ID FROM PO_LINE_LOCATIONS PLL2 WHERE PLL2.SHIPMENT_TYPE = 'SCHEDULED' AND PLL2.LINE_LOCATION_ID = :B1 ) )

The error occured when i run a specific custom report and it never happened before also we run the report while gather schema are running.
does gather schema might causing a deadlock ??
if it isn't because of gather schema
what should i do to troubleshoot it ??

Thx

Hendra

Hello Tom,

We are using

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


We are getting deadlock error very often in production due to below design.

TableB is child table of TableA
When insert/update/delete happens on TableB we need to sum the amount(amt) and then update it in TableA.total_amt column. Please use below script to replicate the deadlock scenario

drop table tableA;


drop table tableB;


drop package global_pkg;


create table tableA (pk_id number primary key, total_amt number);


create table tableB (pk_id number primary key, fk_id number references tableA(pk_id) not null, amt number);


CREATE OR REPLACE PACKAGE global_pkg
IS
fk_id tableA.pk_id%TYPE;
END global_pkg;


CREATE OR REPLACE TRIGGER tableB_ROW_TRG
BEFORE INSERT OR UPDATE OR DELETE
ON tableB
FOR EACH ROW
BEGIN
IF INSERTING OR UPDATING
THEN
global_pkg.fk_id := :new.fk_id;
ELSE
global_pkg.fk_id := :old.fk_id;
END IF;
END tableB_ROW_TRG;


CREATE OR REPLACE TRIGGER tableB_ST_trg
AFTER INSERT OR UPDATE OR DELETE
ON tableB
BEGIN
IF UPDATING OR INSERTING
THEN
UPDATE tableA
SET total_amt =
(SELECT SUM (amt)
FROM tableB
WHERE fk_id = global_pkg.fk_id)
WHERE pk_id = global_pkg.fk_id;
ELSE
UPDATE tableA
SET total_amt =
(SELECT SUM (amt)
FROM tableB
WHERE fk_id = global_pkg.fk_id)
WHERE pk_id = global_pkg.fk_id;
END IF;
END tableB_ST_trg;


insert into tableA values (1, 0);

insert into tableA values (2, 0);


insert into tableB values (123, 1, 100);

insert into tableB values (456,1, 200);

insert into tableB values (789, 1, 100);


insert into tableB values (1011, 2, 50);

insert into tableB values (1213,2, 150);

insert into tableB values (1415, 2, 50);


Commit;


select * from tableA;


pk_id total_amt
------ ------------
1 400
2 250


delete tableB where pk_id = 1415;


-- Check the locks -- TM lock on tableA
SELECT sid,
(SELECT username
FROM v$session s
WHERE s.sid = v$lock.sid)
uname,
TYPE,
id1,
id2,
(SELECT object_name
FROM user_objects
WHERE object_id = v$lock.id1)
nm
FROM v$lock
WHERE sid IN (SELECT sid
FROM v$session
WHERE username IN (USER));



-- Connect to another session run below statement


delete tableB where pk_id = 1213;


-- You can see the blocking sessions


SELECT (SELECT username
FROM v$session
WHERE sid = a.sid)
blocker,
a.sid,
' is blocking ',
(SELECT username
FROM v$session
WHERE sid = b.sid)
blockee,
b.sid
FROM v$lock a, v$lock b
WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;


-- Execute below statement in session1

delete tableB where pk_id = 1213;

-- Oracle is throwing deadlock error as below in session2


[Error] Execution (3: 1): ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TABLEB_ST_TRG", line 11
ORA-04088: error during execution of trigger 'TABLEB_ST_TRG'

Few times in production we are facing deadlock errors due to above scenarios.

What is causing this issue? I learnt from your book TM locks are to prevent structural change(DDL- TM lock on TableA) but I am not sure why session2 is getting block from session1 when both are trying to delete independent rows in TableB.

My point is I know this is bad design but my client required that tableA.total_amt column updated in real time whenever someone do DML operations on tableB. Do we have any solution other than triggers?

I also proposed to have a job(Oracle job which runs every ten minutes) to do summation in near realtime to update the tableA but users are not ready to accept it.


Please advise

Thanks,

GPU

ops$tkyte%ORA11GR2> create table tableA (pk_id number primary key, total_amt number);

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table tableB (pk_id number primary key, fk_id number references tableA(pk_id) not null, amt number);

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE TRIGGER tableB_ROW_TRG
  2     BEFORE INSERT OR UPDATE OR DELETE
  3     ON tableB
  4     FOR EACH ROW
  5  BEGIN
  6     IF INSERTING OR UPDATING
  7     THEN
  8            update tablea set total_amt = total_amt + nvl(:new.amt,0) where pk_id = :new.fk_id;
  9     end if;
 10
 11     if updating or deleting
 12     then
 13            update tablea set total_amt = total_amt - nvl(:old.amt,0) where pk_id = :old.fk_id;
 14     end if;
 15  END tableB_ROW_TRG;
 16  /

Trigger created.

ops$tkyte%ORA11GR2> insert into tableA values (1, 0);

1 row created.

ops$tkyte%ORA11GR2> insert into tableA values (2, 0);

1 row created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into tableB values (123, 1, 100);

1 row created.

ops$tkyte%ORA11GR2> insert into tableB values (456,1, 200);

1 row created.

ops$tkyte%ORA11GR2> insert into tableB values (789, 1, 100);

1 row created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into tableB values (1011, 2, 50);

1 row created.

ops$tkyte%ORA11GR2> insert into tableB values (1213,2, 150);

1 row created.

ops$tkyte%ORA11GR2> insert into tableB select 1415, 2, 50 from dual;

1 row created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> Commit;

Commit complete.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from tablea;

     PK_ID  TOTAL_AMT
---------- ----------
         1        400
         2        250

ops$tkyte%ORA11GR2> update tableb set amt = amt + 1;

6 rows updated.

ops$tkyte%ORA11GR2> select * from tablea;

     PK_ID  TOTAL_AMT
---------- ----------
         1        403
         2        253

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> delete from tableb;

6 rows deleted.

ops$tkyte%ORA11GR2> select * from tablea;

     PK_ID  TOTAL_AMT
---------- ----------
         1          0
         2          0

ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from tablea;

     PK_ID  TOTAL_AMT
---------- ----------
         1        403
         2        253

hi tom,
i am a big fan of yours.your posts has always helped me. this is my first post on your blog :). pleasee guide.

scenario:
1.) table A (child table)
2.) table B (parent table)
3.) performing delete.

in my scenario i have an index on the foreign key.
the index is not even in the UNUSABLE state.
the order of columns is also correct.

the sequence i am following is as follows:

1.)delete from the child table.
2.)then delete from the parent table.

but the delete from parent table is getting stuck indefinately even the index on foreign key is present and is USABLE.

it only works when i REBUILD the foreign key index on child table.

question:
is stucking of query because of delete performed on the child table and the corresponding index(or its stats) not being updated accordingly?
please put some light on this

Using the information of the Deadlock Trace file, is it possible to reproduce the same Deadlock?

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
UL-40085693-00000000       321    1091     X            407     730           X
TM-000120a1-00000000       407     730    SX             75    3892           S
TM-000120a1-00000000        75    3892           S      321    1091          SX
 
session 1091: DID 0001-0115-00000604 session 730: DID 0001-00E1-00000D14 
session 730: DID 0001-00E1-00000D14 session 3892: DID 0001-003B-00004455 
session 3892: DID 0001-003B-00004455 session 1091: DID 0001-0115-00000604 
 
Rows waited on:
  Session 1091: obj - rowid = 000120a1 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 73889, file - 0, block - 0, slot - 0)
  Session 730: no row
  Session 3892: obj - rowid = 000120a1 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 73889, file - 0, block - 0, slot - 0)
 
----- Information for the OTHER waiting sessions -----
Session 730:
  sid: 730 ser: 13917 audsid: 2348098 user: 307/JTESH
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 407 O/S info: user: norjob, term: UNKNOWN, ospid: 17832
    image: oracle@custdb01 (TNS V1-V3)
  client details:
    O/S info: user: norjob, term: , ospid: 17831
    machine: custdb01 program: MRUAFL@custdb01 (TNS V1-V3)
    application name: MRUAFL, hash value=3576485517
    action name: MRU_DEFAULT, hash value=2415775335
  current SQL:
  begin SFKFEES . P_MRUINTJOB ( :in_term , :stud , TO_DATE ( :assesment , G$_DATE . GET_NLS_DATE_FORMAT ) , TO_DATE ( :refund , G$_DATE . GET_NLS_DATE_FORMAT ) , :rule , :create_ind , :name , :commit , :date , 'N' , :return_status:Ind_01 ) ; END ;
 
Session 3892:
  sid: 3892 ser: 43240 audsid: 3123548 user: 1208/EHARP
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 75 O/S info: user: oracle, term: UNKNOWN, ospid: 22734
    image: oracle@custdb01
  client details:
    O/S info: user: oracle, term: , ospid: 89023
    machine: custinb01 program: frmweb@custinb01 (TNS V1-V3)
    application name: MRUEMM, hash value=2551670113
    action name: MRU_DEFAULT, hash value=2715222117
  current SQL:
  DELETE FROM RZBEVA  WHERE ( RZBEVA_PIDM , RZBEVA_TERM_CODE , RZBEVA_SRCE_CODE , RZBEVA_ACTIVITY_DATE , RZBEVA_AMOUNT , RZBEVA_CROSSREF_PIDM , RZBEVA_CROSSREF_NUMBER , NVL(RZBEVA_CROSSREF_DETAIL_CODE , '""""' )  )  IN (SELECT RZBEVA_CROSSREF_PIDM , :b1 , 'C' , TO_DATE (:b2 , '' ||  G$_DATE.GET_NLS_DATE_FORMAT   ||  'HH24:MI:SS'  )  , RZBEVA_AMOUNT , :b3 , RZBEVA_CROSSREF_NUMBER , NVL(RZBEVA_CROSSREF_DETAIL_CODE , '""""' )  FROM RZBEVA   WHERE RZBEVA_SRCE_CODE =  'C'  AND RZBEVA_TRAN_NUMBER >  :b4  AND RZBEVA_TERM_CODE =  :b1  AND RZBEVA_PIDM =  :b3   )
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=bpva86cw27mgh) -----
INSERT INTO RZBEVA VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 ,:B48 ,:B49 ,:B50 ,:B51 ,:B52 ,:B53 ,:B54 ,:B55 ,:B56 ,:B57 ,:B58 ,:B75 ) RETURNING ROWID INTO :O0 
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
23bc321d0        89  package body CGMS.DML_RZBEVA
25ce3c701      1211  package body CGMS.YUJUMON

I'm getting deadlock doing a MERGE into table from a global temporary table.

create table tbl(col_a varchar2(10), col_b number, col_c date);
create unique index tbl_uk on tbl (nls_upper(col_a), col_b);

No child tables exist (so no unindexed foreign keys), no bitmap indexes, no autonomous transaction.

Just a MERGE, with either an UPDATE to COL_C (non-unique, not indexed), or INSERT into the tbl.

merge into tbl
using (select /*+ no_eliminate_oby */
              distinct tt.col_b
       from tt
       -- order by 1
      ) tt
on    (    nls_upper(col_a) = :b1
       and tbl.col_b = tt.col_b
      )
when   matched then update set col_c = sysdate
when   not matched then
       insert (col_a,col_b,col_c)
         (:b1, tt.col_b, sysdate);

I'm a bit stuck on ideas and have been thinking it may be a bug with the MERGE, so have tried an ORDER by with a HINT (commented out above), but still getting deadlock.

We're on 11.2.0.3.

Could you please advise if there are any other causes of deadlocks?

I went through your explanation on deadlocks and they seem to have problem with 2 sessions involved in deadlock. But my situation is same session is blocking and waiting which is wierd.

A procedure is called to insert the data and I have given the procedure code below. The SQL involved in this session is a insert statement to a GLOBAL temporary table. I understand that GTT is session specific and how could a deadlock happen in that.

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0004001b-00148e18 10 194 X 10 194 S
session 194: DID 0001-000A-0064290E session 194: DID 0001-000A-0064290E
Rows waited on:
Session 194: no row
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

BEGIN
select 1 into v_cnt
from dual
where exists (select 1 from stgmgr.global_stg_loc_hierarchy);
exception
when no_data_found then
INSERT INTO STGMGR.GLOBAL_STG_LOC_HIERARCHY ( LEVEL_1_ID, LEVEL_1_CODE, LEVEL_1_DESC, LEVEL_1_LABEL_ID, LEVEL_1_LABEL_DESC, LEVEL_2_ID, LEVEL_2_CODE, LEVEL_2_DESC, LEVEL_2_LABEL_ID, LEVEL_2_LABEL_DESC, LEVEL_3_ID, LEVEL_3_CODE, LEVEL_3_DESC, LEVEL_3_LABEL_ID, LEVEL_3_LABEL_DESC, LEVEL_4_ID, LEVEL_4_CODE, LEVEL_4_DESC, LEVEL_4_LABEL_ID, LEVEL_4_LABEL_DESC, LEVEL_5_ID, LEVEL_5_CODE, LEVEL_5_DESC, LEVEL_5_LABEL_ID, LEVEL_5_LABEL_DESC, LEVEL_6_ID, LEVEL_6_CODE, LEVEL_6_DESC, LEVEL_6_LABEL_ID, LEVEL_6_LABEL_DESC, LEVEL_7_ID, LEVEL_7_CODE, LEVEL_7_DESC, LEVEL_7_LABEL_ID, LEVEL_7_LABEL_DESC, LEVEL_8_ID, LEVEL_8_CODE, LEVEL_8_DESC, LEVEL_8_LABEL_ID, LEVEL_8_LABEL_DESC, LEVEL_9_ID, LEVEL_9_CODE, LEVEL_9_DESC, LEVEL_9_LABEL_ID, LEVEL_9_LABEL_DESC, LEVEL_10_ID, LEVEL_10_CODE, LEVEL_10_DESC, LEVEL_10_LABEL_ID, LEVEL_10_LABEL_DESC, LOC_TYPE_ID, LOC_TYPE_CODE, LOC_TYPE_DESC, LOC_DISTR_TYPE_ID, LOC_DISTR_TYPE_CODE, LOC_DISTR_TYPE_DESC, HOME_CURRENCY, INTL_LOC, FISCAL_CAL_ID, FISCAL_CAL_DESC, FIN_ACCTNG_MTHS_4_4_5, RECORD_FLAG, DELETE_FLAG, FORECAST_MONTH, HISTORICAL_MONTHS, GL_ENTITY, BUSINESS_GROUP, BUSINESS_GROUP_DESC, DIVISION, DIVISION_DESC, AREA, AREA_DESC, SUB_AREA, SUB_AREA_DESC, REGION, REGION_DESC, SUB_REGION, SUB_REGION_DESC, INTL_DOMESTIC, INTL_DOMESTIC_DESC, MFG_DISTRIBUTION, MFG_DISTRIBUTION_DESC, LOCATION_TYPE, LOCATION_TYPE_DESC, AFFILIATE, AFFILIATE_DESC, SUB_AFFILIATE, SUB_AFFILIATE_DESC, LOC, LOC_DESC, OTHER, OTHER_DESC, CREATED_BY, CREATION_DATE, LAST_UPD_BY, LAST_UPD_DATE, DATAMART_LAST_UPD_BY, DATAMART_LAST_UPD_DATE, LOH_ADJUSTMENT, WH_LEVEL_PLNG_FLAG, SENDING_SCHD_SHIP_FLAG, ALLOW_ICB_METHOD_FOR_INTRANSIT, MAJOR_SOURCE_SYSTEM, REPORTING_AFFILIATE_DEFAULT, GL_SALES_DIVISION_DEFAULT, GL_INV_PERFORMANCE_DEFAULT, GL_SALES_PERFORMANCE_DEFAULT, INVOICE_LOCATION_DEFAULT, ALTER_PLANT_DEFAULT, WIP_COST_METHOD , INV_RPTG_CURRENCY ) (SELECT LEVEL_1_ID, LEVEL_1_CODE, LEVEL_1_DESC, LEVEL_1_LABEL_ID, LEVEL_1_LABEL_DESC, LEVEL_2_ID, LEVEL_2_CODE, LEVEL_2_DESC, LEVEL_2_LABEL_ID, LEVEL_2_LABEL_DESC, LEVEL_3_ID, LEVEL_3_CODE, LEVEL_3_DESC, LEVEL_3_LABEL_ID, LEVEL_3_LABEL_DESC, LEVEL_4_ID, LEVEL_4_CODE, LEVEL_4_DESC, LEVEL_4_LABEL_ID, LEVEL_4_LABEL_DESC, LEVEL_5_ID, LEVEL_5_CODE, LEVEL_5_DESC, LEVEL_5_LABEL_ID, LEVEL_5_LABEL_DESC, LEVEL_6_ID, LEVEL_6_CODE, LEVEL_6_DESC, LEVEL_6_LABEL_ID, LEVEL_6_LABEL_DESC, LEVEL_7_ID, LEVEL_7_CODE, LEVEL_7_DESC, LEVEL_7_LABEL_ID, LEVEL_7_LABEL_DESC, LEVEL_8_ID, LEVEL_8_CODE, LEVEL_8_DESC, LEVEL_8_LABEL_ID, LEVEL_8_LABEL_DESC, LEVEL_9_ID, LEVEL_9_CODE, LEVEL_9_DESC, LEVEL_9_LABEL_ID, LEVEL_9_LABEL_DESC, LEVEL_10_ID, LEVEL_10_CODE, LEVEL_10_DESC, LEVEL_10_LABEL_ID, LEVEL_10_LABEL_DESC, LOC_TYPE_ID, LOC_TYPE_CODE, LOC_TYPE_DESC, LOC_DISTR_TYPE_ID, LOC_DISTR_TYPE_CODE, LOC_DISTR_TYPE_DESC, HOME_CURRENCY, INTL_LOC, FISCAL_CAL_ID, FISCAL_CAL_DESC, FIN_ACCTNG_MTHS_4_4_5, RECORD_FLAG, DELETE_FLAG, FORECAST_MONTH, HISTORICAL_MONTHS, GL_ENTITY, BUSINESS_GROUP, BUSINESS_GROUP_DESC, DIVISION, DIVISION_DESC, AREA, AREA_DESC, SUB_AREA, SUB_AREA_DESC, REGION, REGION_DESC, SUB_REGION, SUB_REGION_DESC, INTL_DOMESTIC, INTL_DOMESTIC_DESC, MFG_DISTRIBUTION, MFG_DISTRIBUTION_DESC, LOCATION_TYPE, LOCATION_TYPE_DESC, AFFILIATE, AFFILIATE_DESC, SUB_AFFILIATE, SUB_AFFILIATE_DESC, LOC, LOC_DESC, OTHER, OTHER_DESC, CREATED_BY, CREATION_DATE, LAST_UPD_BY, LAST_UPD_DATE, DATAMART_LAST_UPD_BY, DATAMART_LAST_UPD_DATE, LOH_ADJUSTMENT, WH_LEVEL_PLNG_FLAG, SENDING_SCHD_SHIP_FLAG, ALLOW_ICB_METHOD_FOR_INTRANSIT, MAJOR_SOURCE_SYSTEM, REPORTING_AFFILIATE_DEFAULT, GL_SALES_DIVISION_DEFAULT, GL_INV_PERFORMANCE_DEFAULT, GL_SALES_PERFORMANCE_DEFAULT, INVOICE_LOCATION_DEFAULT, ALTER_PLANT_DEFAULT, WIP_COST_METHOD , INV_RPTG_CURRENCY FROM STGMGR.STG_LOC_HIERARCHY);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(substr('Error while populating GLOBAL_STG_LOC_HIERARCHY '||SQLCODE||'-'||SQLERRM,1,250));
RAISE;
end;

Table definition has all these columns with "ON COMMIT PRESERVE ROWS NOCACHE" and with the below index

CREATE UNIQUE INDEX GLOBAL_STG_LOC_HIERARCHY_PK ON GLOBAL_STG_LOC_HIERARCHY
(DIVISION, LOC);

Eagerly expecting your response on this. Thanks.

ops$tkyte%ORA11GR2> create global temporary table gtt ( x int ) on commit preserve rows;

Table created.

ops$tkyte%ORA11GR2> create unique index t_idx on gtt(x);

Index created.

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

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into gtt values ( 1 );
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


...
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0d95001c-0000e5de        20      68     X             20      68           S

session 68: DID 0001-0014-0000708A  session 68: DID 0001-0014-0000708A

Rows waited on:
  Session 68: no row
.....

Tom, this is my first post. Can you give direction to where we should look to resolve the deadlock issue. its happenning frequently. Is it a inittran issue or application design issue.

trace file contents


*** 2014-04-24 17:00:10.191
*** SESSION ID:(833.14399) 2014-04-24 17:00:10.191
*** CLIENT ID:(THURUMA:1755612465949901) 2014-04-24 17:00:10.191
*** SERVICE NAME:(DG_SALEPROD) 2014-04-24 17:00:10.191
*** MODULE NAME:(SFA/APEX:APP 200) 2014-04-24 17:00:10.191
*** ACTION NAME:(PAGE 600) 2014-04-24 17:00:10.191



*** 2014-04-24 17:00:10.192
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00140014-003d915f 475 833 X 523 827 X
TX-00170005-002d31cb 523 827 X 475 833 X

session 833: DID 0001-01DB-002D3499 session 827: DID 0001-020B-001BDCE6
session 827: DID 0001-020B-001BDCE6 session 833: DID 0001-01DB-002D3499

Rows waited on:
Session 833: obj - rowid = 00033888 - AAAziIAACAAA6WjAAI
(dictionary objn - 211080, file - 2, block - 239011, slot - 8)
Session 827: obj - rowid = 00033888 - AAAziIAACAAA6WjAAN
(dictionary objn - 211080, file - 2, block - 239011, slot - 13)

----- Information for the OTHER waiting sessions -----
Session 827:
sid: 827 ser: 47882 audsid: 15037563 user: 613/<none>
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 523 O/S info: user: oracle, term: UNKNOWN, ospid: 16602
image: oracle@smprd001
client details:
O/S info: user: tomcat, term: unknown, ospid: 1234
machine: prjbs001 program: APEX Listener
client info: THURUMA:734305062768486
application name: SFA/APEX:APP 200, hash value=4168807911
action name: Processes - point: BEFORE_BOX_BODY, hash value=1685858885
current SQL:
UPDATE WWV_FLOW_DATA SET ITEM_VALUE = :B6 || ':' || :B5 || ':' || :B4 || ':' || :B3 WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=70rnf8na61nju) -----
DELETE FROM WWV_FLOW_DATA WHERE FLOW_INSTANCE = :B1 AND ITEM_ID IN (SELECT ID FROM WWV_FLOW_PAGE_PLUGS WHERE FLOW_ID = :B3 AND PAGE_ID = :B2 AND PLUG_SOURCE_TYPE IN ( 'SIMPLE_CHART', 'UPDATABLE_SQL
_QUERY', 'DBMSSQL_CURSOR', 'FUNCTION_RETURNING_DBMSSQL_CURSOR', 'FUNCTION_RETURNING_SQL_QUERY', 'STRUCTURED_QUERY', 'SQL_QUERY', 'DYNAMIC_QUERY'))
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x11f827b58 417 package body APEX_040100.WWV_FLOW_DISP_PAGE_PLUGS
0x11bd04990 7620 package body APEX_040100.WWV_FLOW
0x119da19e8 249 procedure APEX_040100.F
0x14b569d78 2 anonymous block
===================================================
PROCESS STATE
-------------
Process global information:
process: 0x3aa03dda0, call: 0x3a927a840, xact: 0x3a7f6b080, curses: 0x3aa52f8c0, usrses: 0x3aa52f8c0
in_exception_handler: no
----------------------------------------
SO: 0x3aa03dda0, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x3aa03dda0, name=process, file=ksu.h LINE:12451, pg=1
(process) Oracle pid:475, ser:73, calls cur/top: 0x3a927a840/0x3a91e7538
flags : (0x0) -
flags2: (0x0), flags3: (0x0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 136 0 2
last post received-location: ksl2.h LINE:2293 ID:kslpsr
last process to post me: 3aa003120 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:282 ID:ksasnd
last process posted by me: 3aa003120 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x728f8530
O/S info: user: oracle, term: UNKNOWN, ospid: 1197
OSD pid info: Unix process pid: 1197, image: oracle@smprd001

same thing occur to me ... in your it does not talk about Rows waited on: kindly give me exact example..