VMCD.ORG » 基于foreign key 没有index引起的ssx锁等待的测试

创建测试环境：
[oracle@yang ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Oct 27 00:03:46 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn liu/liu
Connected.
SQL> create table b as select * from dba_objects;

Table created.

SQL> create table a as select * from dba_objects;

Table created.

SQL> alter table a add constraint a_pk primary key (OBJECT_ID);

Table altered.

SQL> alter table b add constraint b_fk foreign key (OBJECT_ID) references a (OBJECT_ID);

Table altered.

SQL>

session 1 执行：
SQL> delete from b where object_id=10000;

1 row deleted.

SQL> delete from a where object_id=10000;

1 row deleted.

session 2 执行：

s[oracle@yang ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Oct 27 00:03:46 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn liu/liu
Connected.
SQL> delete from b where object_id=10001;

1 row deleted.

SQL> delete from a where object_id=10001;

session 2被hang住；

通过以下SQL查询：

SELECT LK.SID,
SE.USERNAME,
SE.OSUSER,
SE.MACHINE,
DECODE(LK.TYPE,
‘TX’,
‘Transaction’,
‘TM’,
‘DML’,
‘UL’,
‘PL/SQL User Lock’,
LK.TYPE) LOCK_TYPE,
DECODE(LK.LMODE,
0,
‘None’,
1,
‘Null’,
2,
‘Row-S (SS)’,
3,
‘Row-X (SX)’,
4,
‘Share’,
5,
‘S/Row-X (SSX)’,
6,
‘Exclusive’,
TO_CHAR(LK.LMODE)) MODE_HELD,
DECODE(LK.REQUEST,
0,
‘None’,
1,
‘Null’,
2,
‘Row-S (SS)’,
3,
‘Row-X (SX)’,
4,
‘Share’,
5,
‘S/Row-X (SSX)’,
6,
‘Exclusive’,
TO_CHAR(LK.REQUEST)) MODE_REQUESTED,
TO_CHAR(LK.ID1) LOCK_ID1,
TO_CHAR(LK.ID2) LOCK_ID2,
OB.OWNER,
OB.OBJECT_TYPE,
OB.OBJECT_NAME,
LK.BLOCK,
SE.LOCKWAIT
FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE
WHERE LK.TYPE IN (‘TM’, ‘UL’)
AND LK.SID = SE.SID
AND LK.ID1 = OB.OBJECT_ID(+);

141 LIU oracle yang DML Row-S (SS) None 52153 0 LIU TABLE A 0 2F9C69BC
144 LIU oracle yang DML Row-X (SX) None 52153 0 LIU TABLE A 0
141 LIU oracle yang DML Row-X (SX) S/Row-X (SSX) 52152 0 LIU TABLE B 0 2F9C69BC
144 LIU oracle yang DML Row-X (SX) None 52152 0 LIU TABLE B 1

可以发现session 141 请求 SSX锁而 SX与SSX不兼容而被hang住

此时session 1:

SQL> delete from a where object_id=10002;

立刻产生死锁因为session 1 需要短暂的获得b表的ssx锁而此时b表正在被以sx锁持有 session1 hang死而session 2在等待session 1持有b表的 sx锁释放所以发生了死锁；

Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TM-0000cbb8-00000000 24 141 SX SSX 22 144 SX SSX
TM-0000cbb8-00000000 22 144 SX SSX 24 141 SX SSX
session 141: DID 0001-0018-00000006 session 144: DID 0001-0016-00000011
session 144: DID 0001-0016-00000011 session 141: DID 0001-0018-00000006

发现需要持有ssx锁

通常这种情况发生在外键没有建立index的情况下

oracle 建议外键必须加上index：
“You should almost always index foreign keys.
The only exception is when the matching unique or primary key is never updated or deleted.”

When a foreign key is unindexed, DML on the parent primary key results in a share row exclusive table lock
(or share-subexclusive table lock, SSX) on the child table, preventing DML from other transactions
against the child table. If the DML affects several rows in the parent table, the lock on the child table
is obtained and released immediately for each row in turn. Despite the speed of the lock-release process,
this can cause significant amounts of contention on the child table during periods of
heavy update/delete activity on the parent table.

When a foreign key is indexed, DML on the parent primary key results in a row share table lock
(or subshare table lock, SS) on the child table. This type of lock prevents other transactions
from issuing whole table locks on the child table, but does not block DML on either the parent or
the child table. Only the rows relating to the parent primary key are locked in the child table.

以下脚本可以帮助解决foreign key的问题：

=======
Script:
=======

———– cut ———————- cut ————– cut ————–

SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:”@path/tfsfkchk”
REM ————————————————————————–
REM REQUIREMENTS:
REM None — checks only the USER_ views
REM ————————————————————————–
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM
REM
REM ————————————————————————-
REM Main text of script follows:

drop table ck_log;

create table ck_log (
LineNum number,
LineMsg varchar2(2000));

declare
t_CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%type;
t_CONSTRAINT_NAME USER_CONSTRAINTS.CONSTRAINT_NAME%type;
t_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
t_R_CONSTRAINT_NAME USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;
tt_CONSTRAINT_NAME USER_CONS_COLUMNS.CONSTRAINT_NAME%type;
tt_TABLE_NAME USER_CONS_COLUMNS.TABLE_NAME%type;
tt_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
tt_POSITION USER_CONS_COLUMNS.POSITION%type;
tt_Dummy number;
tt_dummyChar varchar2(2000);
l_Cons_Found_Flag VarChar2(1);
Err_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
Err_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
Err_POSITION USER_CONS_COLUMNS.POSITION%type;

tLineNum number;

cursor UserTabs is
select table_name
from user_tables
order by table_name;

cursor TableCons is
select CONSTRAINT_TYPE,
CONSTRAINT_NAME,
R_CONSTRAINT_NAME
from user_constraints
where OWNER = USER
and table_name = t_Table_Name
and CONSTRAINT_TYPE = ‘R’
order by TABLE_NAME, CONSTRAINT_NAME;

cursor ConColumns is
select CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;

cursor IndexColumns is
select TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;

DebugLevel number := 99; — >>> 99 = dump all info`
DebugFlag varchar(1) := ‘N’; — Turn Debugging on
t_Error_Found varchar(1);

begin

tLineNum := 1000;
open UserTabs;
LOOP
Fetch UserTabs into t_TABLE_NAME;
t_Error_Found := ‘N’;
exit when UserTabs%NOTFOUND;

— Log current table
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Checking Table ‘||t_Table_Name);

l_Cons_Found_Flag := ‘N’;
open TableCons;
LOOP
FETCH TableCons INTO t_CONSTRAINT_TYPE,
t_CONSTRAINT_NAME,
t_R_CONSTRAINT_NAME;
exit when TableCons%NOTFOUND;

if ( DebugFlag = ‘Y’ and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found CONSTRAINT_NAME = ‘|| t_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found CONSTRAINT_TYPE = ‘|| t_CONSTRAINT_TYPE);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found R_CONSTRAINT_NAME = ‘|| t_R_CONSTRAINT_NAME);
commit;
end;
end if;

open ConColumns;
LOOP
FETCH ConColumns INTO
tt_CONSTRAINT_NAME,
tt_TABLE_NAME,
tt_COLUMN_NAME,
tt_POSITION;
exit when ConColumns%NOTFOUND;
if ( DebugFlag = ‘Y’ and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found CONSTRAINT_NAME = ‘|| tt_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found TABLE_NAME = ‘|| tt_TABLE_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found COLUMN_NAME = ‘|| tt_COLUMN_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Found POSITION = ‘|| tt_POSITION);
commit;
end;
end if;

begin
select 1 into tt_Dummy
from user_ind_columns
where TABLE_NAME = tt_TABLE_NAME
and COLUMN_NAME = tt_COLUMN_NAME
and COLUMN_POSITION = tt_POSITION;

if ( DebugFlag = ‘Y’ and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Row Has matching Index’ );
end;
end if;
exception
when Too_Many_Rows then
if ( DebugFlag = ‘Y’ and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Row Has matching Index’ );
end;
end if;

when no_data_found then
if ( DebugFlag = ‘Y’ and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘NO MATCH FOUND’ );
commit;
end;
end if;

t_Error_Found := ‘Y’;

select distinct TABLE_NAME
into tt_dummyChar
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, ‘Changing data in table ‘||tt_dummyChar
||’ will lock table ‘ ||tt_TABLE_NAME);

commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,’Create an index on table ‘||tt_TABLE_NAME
||’ with the following columns to remove lock problem’);

open IndexColumns ;
loop
Fetch IndexColumns into Err_TABLE_NAME,
Err_COLUMN_NAME,
Err_POSITION;
exit when IndexColumns%NotFound;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,’Column = ‘||Err_COLUMN_NAME||’ (‘||Err_POSITION||’)’);
end loop;
close IndexColumns;
end;
end loop;
commit;
close ConColumns;
end loop;
if ( t_Error_Found = ‘N’ )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,’No foreign key errors found’);
end;
end if;
commit;
close TableCons;
end loop;
commit;
end;
/

select LineMsg
from ck_log
where LineMsg NOT LIKE ‘Checking%’ AND
LineMsg NOT LIKE ‘No foreign key%’
order by LineNum
/

通过以下脚本可以查出没有被indexed的外键：

REM List foreign keys with no matching index on child table – causes locks

set linesize 150;

col owner for a20;
col COLUMN_NAME for a20;

SELECT c.owner, c.constraint_name, c.table_name, cc.column_name, c.status
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_type = ‘R’
AND c.owner NOT IN (‘SYS’,
‘SYSTEM’,
‘SYSMAN’,
‘EXFSYS’,
‘WMSYS’,
‘OLAPSYS’,
‘OUTLN’,
‘DBSNMP’,
‘ORDSYS’,
‘ORDPLUGINS’,
‘MDSYS’,
‘CTXSYS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘XDB’,
‘FLOWS_030000’,
‘FLOWS_FILES’)
AND c.owner = cc.owner
AND c.constraint_name = cc.constraint_name
AND NOT EXISTS
(SELECT ‘x’
FROM dba_ind_columns ic
WHERE cc.owner = ic.table_owner
AND cc.table_name = ic.table_name
AND cc.column_name = ic.column_name
AND cc.position = ic.column_position
AND NOT EXISTS
(SELECT owner, index_name
FROM dba_indexes i
WHERE i.table_owner = c.owner
AND i.index_Name = ic.index_name
AND i.owner = ic.index_owner
AND (i.status = ‘UNUSABLE’ OR
i.partitioned = ‘YES’ AND EXISTS
(SELECT ‘x’
FROM dba_ind_partitions ip
WHERE status = ‘UNUSABLE’
AND ip. index_owner = i. owner
AND ip. index_Name = i. index_name
UNION ALL
SELECT ‘x’
FROM dba_ind_subpartitions isp
WHERE status = ‘UNUSABLE’
AND isp. index_owner = i. owner
AND isp. index_Name = i. index_name))))
ORDER BY 1, 2

related posts：
dead lock引起的数据库hang死

admin 13 years ago

Troubleshooting “ORA-60 Deadlock Detected” Errors [ID 62365.1]
Modified 01-AUG-2011 Type TROUBLESHOOTING Status PUBLISHED

Checked for relevance on 8-16-2010
Introduction
~~~~~~~~~~~~
This short article describes how to determine the cause of ORA-60
“deadlock detected while waiting for resource” errors.
Note that 99% of the time deadlocks are caused by application or
configuration issues. This article attempts to highlight the most
common deadlock scenarios.

What is Deadlock?
~~~~~~~~~~~~~~~~~
A deadlock occurs when a session (A) wants a resource held by another
session (B) , but that session also wants a resource held by the first
session (A). There can be more than 2 sessions involved but the idea is
the same.

Example of Deadlock
~~~~~~~~~~~~~~~~~~~
To reinforce the description the following simple test demonstrates a
a deadlock scenario. This is on Oracle 8.0.4 so if you are used to Oracle7
the ROWIDs may look a little strange:

Setup: create table eg_60 ( num number, txt varchar2(10) );
insert into eg_60 values ( 1, ‘First’ );
insert into eg_60 values ( 2, ‘Second’ );
commit;
select rowid, num, txt from eg_60;

ROWID NUM TXT
—————— ———- ———-
AAAAv2AAEAAAAqKAAA 1 First
AAAAv2AAEAAAAqKAAB 2 Second

Ses#1: update eg_60 set txt=’ses1′ where num=1;

Ses#2: update eg_60 set txt=’ses2′ where num=2;
update eg_60 set txt=’ses2′ where num=1;

> Ses#2 is now waiting for the TX lock held by Ses#1

Ses#1: update eg_60 set txt=’ses1′ where num=2;

> This update would cause Ses#1 to wait on the TX lock
> held by Ses#2, but Ses#2 is already waiting on this session.
> This causes a deadlock scenario so one of the sessions
> signals an ORA-60.

Ses#2: ORA-60 error

Ses#1: Still blocked until Ses#2 commits or rolls back as ORA-60
only rolls back the current statement and not the entire
transaction.

Diagnostic information produced by an ORA-60
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-60 error normally writes to the alert log.
And the user that gets the ORA-60 error writes information to their trace file.
The exact format of this varies between Oracle releases. The trace
file will be written to the directory indicated by the USER_DUMP_DEST and sometimes
to the background_dump_dest.

The trace file will contain a deadlock graph and additional information
similar to that shown below. This is the trace output from the above example
which signaled an ORA-60 to Ses#2:

———————————————————————–
DEADLOCK DETECTED
Current SQL statement for this session:
update eg_60 set txt=’ses2′ where num=1

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-00020012-0000025e 12 11 X 11 10 X
TX-00050013-0000003b 11 10 X 12 11 X
session 11: DID 0001-000C-00000001 session 10: DID 0001-000B-00000001
session 10: DID 0001-000B-00000001 session 11: DID 0001-000C-00000001
Rows waited on:
Session 10: obj – rowid = 00000BF6 – AAAAv2AAEAAAAqKAAB
Session 11: obj – rowid = 00000BF6 – AAAAv2AAEAAAAqKAAA
———————————————————————–

What does the trace information mean ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In this section we explain each part of the above trace.
Note that not all this information is produced in all Oracle releases.

———————————————————————–
DEADLOCK DETECTED
Current SQL statement for this session:
update eg_60 set txt=’ses2′ where num=1
———————————————————————–
This shows the statement which was executing which received the ORA-60
error. It is this statement which was rolled back.

———————————————————————–
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00020012-0000025e 12 11 X 11 10 X
TX-00050013-0000003b 11 10 X 12 11 X
———————————————————————–
This shows who was holding each lock, and who was waiting for each lock.
The columns in the graph indicate:

Resource Name Lock name being held / waited for.
process V$PROCESS.PID of the Blocking / Waiting session
session V$SESSION.SID of the Blocking / Waiting session
holds Mode the lock is held in
waits Mode the lock is requested in

So in this example:

SID 11 holds TX-00020012-0000025e in X mode
and wants TX-00050013-0000003b in X mode

SID 10 holds TX-00050013-0000003b in X mode
and wants TX-00020012-0000025e in X mode

The important things to note here are the LOCK TYPE, the MODE HELD and
the MODE REQUESTED for each resource as these give a clue as to the
reason for the deadlock.

———————————————————————–
Rows waited on:
Session 10: obj – rowid = 00000BF6 – AAAAv2AAEAAAAqKAAB
Session 11: obj – rowid = 00000BF6 – AAAAv2AAEAAAAqKAAA
———————————————————————–
If the deadlock is due to row-level locks being obtained in different
orders then this section of the trace file indicates the exact rows that
each session is waiting to lock for themselves. Ie: If the lock requests
are TX mode X waits then the ‘Rows waited on’ may show useful information.
For any other lock type / mode the ‘Rows waited on’ is not relevant and
usually shows as “no row”.

In the above example:

SID 10 was waiting for ROWID ‘AAAAv2AAEAAAAqKAAB’ of object 0xBF6
(which is 3062 in decimal)

SID 11 was waiting for ROWID ‘AAAAv2AAEAAAAqKAAA’ of object 0xBF6

This can be decoded to show the exact row/s.
Eg: SID 10 can be shown to be waiting thus:

SELECT owner, object_name, object_type
FROM dba_objects WHERE object_id = 3062;

Owner Object_Name Object_Type
——- ————— —————
SYSTEM EG_60 TABLE

SELECT * FROM system.eg_60 WHERE ROWID=’AAAAv2AAEAAAAqKAAB’;

NUM TXT
———- ———-
2 Second

Avoiding Deadlock
~~~~~~~~~~~~~~~~~
The above deadlock example occurs because the application which issues
the update statements has no strict ordering of the rows it updates.
Applications can avoid row-level lock deadlocks by enforcing some ordering
of row updates. This is purely an application design issue.
Eg: If the above statements had been forced to update rows in ascending
‘num’ order then:

Ses#1: update eg_60 set txt=’ses1′ where num=1;

Ses#2: update eg_60 set txt=’ses2′ where num=1;
> Ses#2 is now waiting for the TX lock held by Ses#1

Ses#1: update eg_60 set txt=’ses1′ where num=2;
> Succeeds as no-one is locking this row
commit;
> Ses#2 is released as it is no longer waiting for this TX

Ses#2: update eg_60 set txt=’ses2′ where num=2;
commit;

The strict ordering of the updates ensures that a deadly embrace cannot
occur. This is the simplest deadlock scenario to identify and resolve.
Note that the deadlock need not be between rows of the same table – it
could be between rows in different tables. Hence it is important to place
rules on the order in which tables are updated as well as the order of the
rows within each table.
Other deadlock scenarios are discussed below.

Different Lock Types and Modes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The most common lock types seen in deadlock graphs are TX and TM locks.
These may appear held / requested in a number of modes. It is the
lock type and modes which help determine what situation has caused the
deadlock.

Lock Mode
Type Requested Probable Cause
~~~~ ~~~~~~~~~ ~~~~~~~~~~~~~~
TX X (mode 6) Application row level conflict.
Avoid by recoding the application to ensure
rows are always locked in a particular order.
TX S (mode 4) There are a number of reasons that a TX lock
may be requested in S mode. See Note:62354.1
for a list of when TX locks are requested in
mode 4.
TM SSX (mode 5) This is usually related to the existence of
or foreign key constraints where the columns
S (mode 4) are not indexed on the child table.
See Note:33453.1 for how to locate such
constraints. See below for locating
the OBJECT being waited on.

Although other deadlock scenarios can happen the above are the most common.

TM locks – which object ?
~~~~~~~~~~~~~~~~~~~~~~~~~
ID1 of a TM lock indicates which object is being locked. This makes it
very simple to isolate the object involved in a deadlock when a TM lock
is involved.

1. Given the TM lock id in the form TM-AAAAAAAA-BBBBBBBB
convert AAAAAAAA from hexadecimal to a decimal number

2. Locate the object using DBA_OBJECTS:

SELECT * FROM dba_objects WHERE object_id= NNNN;

This is the object id that the TM lock covers.
Note that with TM locks it is possible that the lock is already held in
some mode in which case the REQUEST is to escalate the lock mode.

Additional Information
~~~~~~~~~~~~~~~~~~~~~~
If you are still having problems identifying the cause of a deadlock
Oracle Support may be able to help. Additional information can be collected
by adding the following to the init.ora parameters:

event=”60 trace name errorstack level 3;name systemstate level 266″

Note that this can generate a very large trace file which may get
truncated unless MAX_DUMP_FILE_SIZE is large enough to accommodate the output.

When this is set any session encountering an ORA-60 error will write
information about all processes on the database at the time of the error.
This may help show the cause of the deadlock as it can show information
about both users involved in the deadlock. Oracle Support will need
all the information you have collected in addition to the new trace file
to help identify where in the application you should look for problems.

It may be necessary to run the offending jobs with SQL_TRACE enabled
to show the order in which each session issues its commands in order
to get into a deadlock scenario.

Known Issues and References
~~~~~~~~~~~~~~~~~~~~~~~~~~~
TX lock waits and why they occur Note:62354.1

TM locks and Foreign Key Constraints Note:33453.1

Example TM locks During Referential Integrity Enforcement Note:38373.1

INSERT into a clustered table can give ORA-60 (Fixed 7.1.4.) Bug:197942

ORA-60 / ORA-604 against UET$ (Fixed 7.2) Bug:231455

ORA-60 from ANALYZE … VALIDATE …
This can occur if the data dictionary has been ANALYZED and contains
statistics. Delete the statistics.

ORA-60 on startup in Oracle 6
This can be caused by a datafile being inaccessible which is not
marked as offline. Offline the file.