ORA-60死锁解决

在一个数据库的警告日志中发现产生大量的ORA-60死锁报错，并导致了udump下产生了大量的trace文件。导致/app文件系统迅速增长。分析警告日志，和死锁产生的trace文件。

警告日志节选
Tue Feb 9 14:19:13 2010
ORA-00060: Deadlock detected. More info in file /app/oracle/admin/NMPR1/udump/nmpr1_ora_4366.trc.
Tue Feb 9 14:19:13 2010
ORA-00060: Deadlock detected. More info in file /app/oracle/admin/NMPR1/udump/nmpr1_ora_4366.trc.

trace 文件节选如下：
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-00014a53-00000000 1585 4558 SX SSX 1355 1692 SX SSX
TM-00014a53-00000000 1355 1692 SX SSX 1247 1984 SX SSX
TM-00014a53-00000000 1247 1984 SX SSX 1764 3012 SX SSX
TM-00014a53-00000000 1764 3012 SX SSX 1585 4558 SX SSX
session 4558: DID 0001-0631-000000A2 session 1692: DID 0001-054B-000002AC
session 1692: DID 0001-054B-000002AC session 1984: DID 0001-04DF-00000202
session 1984: DID 0001-04DF-00000202 session 3012: DID 0001-06E4-0000013E
session 3012: DID 0001-06E4-0000013E session 4558: DID 0001-0631-000000A2
Rows waited on:
Session 1692: no row
Session 1984: obj - rowid = 00016548 - AAAYVPAAJAAJCZSAAA
(dictionary objn - 91464, file - 9, block - 2369106, slot - 0)
Session 3012: obj - rowid = 0001653E - AAAYVSAAJAAEXXpAAA
(dictionary objn - 91454, file - 9, block - 1144297, slot - 0)
Session 4558: obj - rowid = 000164B3 - AAAWSzAAyAABUdUAAA
(dictionary objn - 91315, file - 50, block - 345940, slot - 0)
Information on the OTHER waiting sessions:
Session 1692:
pid=1355 serial=58130 audsid=4027385 user: 67/NGRM
O/S info: user: dn, term: unknown, ospid: , machine: nmint01
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
DELETE FROM XB_CODE WHERE ID = :B1
Session 1984:
pid=1247 serial=46815 audsid=4005478 user: 67/NGRM
O/S info: user: dn, term: unknown, ospid: , machine: nmint02
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
DELETE FROM XB_CODE WHERE ID = :B1
Session 3012:
pid=1764 serial=12750 audsid=4027382 user: 67/NGRM
O/S info: user: dn, term: unknown, ospid: , machine: nmint01
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
DELETE FROM XB_CODE WHERE ID = :B1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
DELETE FROM XB_CODE WHERE ID = :B1
----- PL/SQL Call Stack -----
object line object
handle number name
c0000007647c9940 33 procedure NGRM.P_SYNC_DELTWOLAYERVLAN
c0000007647d3670 313 procedure NGRM.P_SYNC_NM2TELANT_FORNM
c00000076347db90 1 anonymous block
===================================================

发现这些会话都是在执行DELETE FROM XB_CODE WHERE ID = :B1语句时，其中某个会话报ORA-60错误。

原因分析：
--------------------------------------------------------------------------------
分析发现，在存储过程NGRM.P_SYNC_DELTWOLAYERVLAN中，有以下代码
DELETEFROMXS_VLANWHEREENTITYID = V_CODEID;
DELETEFROMXB_CODEWHEREID = V_CODEID ;

其中XS_VLAN是子表，XB_CODE是父表。因为在子表XS_VLAN的外键上没有索引，会话在删除父表XB_CODE的记录时，需要获得子表的LMODE=4（shared）的TM锁。

SID 1990执行了DELETE FROM XS_VLAN WHERE ENTITYID = :B1删除子表记录，获得了子表XS_VLAN上的LMODE=3的锁，正在执行语句DELETE FROM XB_CODE WHERE ID = :B1删除父表记录，获得了XB_CODE（79389）上的LMODE=3的锁，由于子表外键上没有索引，要求暂时获得XS_VLAN上的LMODE=5（删子表要TM3锁，删父表要字表的TM4锁，综合以下，就是TM5锁）的锁。
SID 1984的情况同1990，也是获得了子表的TM3锁，在删除父表记录是，需要获得子表的TM5（TM3+TM4->TM5）锁。
这样，就会导致会话1990要求的TM5锁，要等待1984持有的TM3锁的释放。而会话1984要求的TM5锁，同样要等待1990持有的TM3锁的释放。从而，形成了死锁。
解决方案：

--------------------------------------------------------------------------------
通过在子表的外键列上加索引，解决了这个问题。
如果子表的外键列上有索引，删除父表的记录，只需要子表的TM2锁。
结论：
--------------------------------------------------------------------------------
如果父表不是静态表，或者要通过子表的外键进行查询，都需要在子表外键上建索引。

参考：

--------------------------------------------------------------------------------
Lock modes WITHOUT an index
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Below are the locks you can expect WITHOUT an index on the child tables
foreign key columns for various operations on the parent and/or child tables.
Version 7.1.6 onwards:
~~~~~~~~~~~~~~~~~~~~~~
Action Emp table lock Dept table lock
~~~~~~ ~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
insert into emp RX
update emp (empno) RX
update emp (deptno) RX
delete from emp RX

insert into dept RX
update dept (deptno) S RX
update dept (dname) RX
delete from dept S RX

insert into dept & insert into emp RX RX
delete from dept & delete from emp SRX RX
Observations:
~~~~~~~~~~~~~
1) Without the index, checking for existing child records when a delete of a
master record is attempted (cascade or restrict makes no difference)
incurs a full scan on the child table.
This may be undesirable from a performance point of view.

2) Since S and SRX locks are incompatible with RX locks, a transaction taking a
share lock on a table will block other transactions from performing DML on
that table for the duration of the transaction, which can be really bad. SRX
locks are even more restrictive (only 1 allowed) than S locks (more than 1
allowed), and will allow other transactions to do only selects, optionally
for update. These locks may be undesirable from a concurrency point of view.

3) In 7.1.6 the locking mechanisms where slightly relaxed, but there still
remain potentially hazardous side-effects from not indexing your FK's.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1047210/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1047210/