一次ORA-60死锁故障的处理

最新推荐文章于 2021-06-10 10:43:54 发布

aicon

最新推荐文章于 2021-06-10 10:43:54 发布

阅读量742

点赞数

分类专栏： Oracle Oracle EBS 文章标签： session delete transactions dictionary jdbc application

Oracle 同时被 2 个专栏收录

523 篇文章 0 订阅

订阅专栏

Oracle EBS

416 篇文章 0 订阅

订阅专栏

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

警告日志节选

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 <a href="javascript:;" onClick="javascript:tagshow(event, 'SQL');" target="_self"><u><strong>SQL</strong></u></a> 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.

from:http://space.itpub.net/12015/viewspace-627673