外键缺乏索引导致的Oracle数据库死锁

与MySQL不同，Oracle不会在子表上为外键列自动创建索引。 外键上 缺乏索引可能会造成严重的性能问题，甚至于死锁。对于从MySQL
迁移至Oracle的数据库应用，应该特别注意这个问题。如果父表存在删除记录或者更改外键列的情形，那么就需要在子表上为外键列创建索引。

下面通过一个实例，分析 Oracle数据库 缺乏外键索引可能造成的后果。

具体情况是，在数据库报警日志中发现了死锁情况：

Global Enqueue Services Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/mdmdb/mdmdb1/trace/mdmdb1_ora_2752986.trc.

查看 mdmdb1_ora_2752986.trc 文件中的内容：

Trace file /u01/app/oracle/diag/rdbms/mdmdb/mdmdb1/trace/mdmdb1_ora_65732640.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

...

Single resource deadlock: blocking enqueue which blocks itself, f 0

Granted global enqueue 700000356fa12a8

----------enqueue 700000356fa12a8------------------------

lock version     : 107165

Owner inst       : 1

grant_level      : KJUSERNL

req_level        : KJUSERPW

bast_level       : KJUSERNL

notify_func      : 0

resp             :  70000036b321090

procp            : 700000352b7f3c8

pid              : 21954568

proc version     : 89

oprocp           : 0

opid             : 21954568

group lock owner : 7000003565f7208

possible pid     : 21954568

xid              : 101C-01C9-00000C78

dd_time          : 10.0 secs

dd_count         : 0

timeout          : 0.0 secs

On_timer_q?      : N

On_dd_q?         : Y

lock_state       : OPENING CONVERTING 

ast_flag         : 0x0

Open Options     : KJUSERDEADLOCK 

Convert options  : KJUSERGETVALUE 

History          : 0x4951449a

Msg_Seq          : 0x0

res_seq          : 5

valblk           : 0x07000002899609580000000110d3fb10 .X

user session for deadlock lock 0x700000356fa12a8

  sid: 452 ser: 11575 audsid: 374059 user: 85/MDM

    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x40009) -/-/INC

  pid: 457 O/S info: user: grid, term: UNKNOWN, ospid: 21954568

    image: oracle@ora5g

  client details:

    O/S info: user: root, term: , ospid: 27811

    machine: phy-mdm-121 program: python@phy-mdm-121 (TNS V1-V3)

    application name: python@phy-mdm-121 (TNS V1-V3), hash value=14486045

  current SQL:

  DELETE FROM t_device WHERE t_device.id = :id

Requesting global enqueue 700000358ec44e0

----------enqueue 700000358ec44e0------------------------

lock version     : 87133

Owner inst       : 1

grant_level      : KJUSERCW

req_level        : KJUSERPW

bast_level       : KJUSERNL

notify_func      : 0

resp             :  70000036b321090

procp            : 700000352b29748

pid              : 65732640

proc version     : 154

oprocp           : 0

opid             : 65732640

group lock owner : 7000003546d2ad0

possible pid     : 65732640

xid              : 1013-0136-000001A4

dd_time          : 0.0 secs

dd_count         : 0

timeout          : 0.0 secs

On_timer_q?      : N

On_dd_q?         : N

lock_state       : CONVERTING 

ast_flag         : 0x0

Open Options     : KJUSERDEADLOCK 

Convert options  : KJUSERGETVALUE 

History          : 0x51449a55

Msg_Seq          : 0x0

res_seq          : 5

valblk           : 0x07000003565f72080700000356fa12a8 .V_rV

user session for deadlock lock 0x700000358ec44e0

  sid: 1083 ser: 1655 audsid: 373847 user: 85/MDM

    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x40009) -/-/INC

  pid: 310 O/S info: user: grid, term: UNKNOWN, ospid: 65732640

    image: oracle@ora5g

  client details:

    O/S info: user: root, term: , ospid: 21862

    machine: phy-mdm-130 program: python@phy-mdm-130 (TNS V1-V3)

    application name: python@phy-mdm-130 (TNS V1-V3), hash value=3173956709

  current SQL:

  DELETE FROM t_device WHERE t_device.id = :id

----------resource 70000036b321090----------------------

resname       : [ 0x13d03][0x0],[ TM][ext 0x0,0x0]

hash mask     : x3

Local inst    : 1

dir_inst      : 1

master_inst   : 1

hv idx        : 98

hv last r.inc : 8

current inc   : 8

hv status     : 0

hv master     : 0

open options  : dd cached 

grant_bits    : KJUSERNL KJUSERCW 

grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX

count         : 1         0         4         0         0         0

val_state     : KJUSERVS_NOVALUE

valblk        : 0x00000000000000000000000000000000 .

access_inst   : 1

vbreq_state   : 0

state         : x0

resp          : 70000036b321090

On Scan_q?    : N

cache level   : 0

Total accesses: 336659265

Imm.  accesses: 336357556

Granted_locks : 4 

Cvting_locks  : 1 

value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

GRANTED_Q :

lp 700000354c49708 gl  KJUSERCW rp 70000036b321090 [0x13d03][0x0],[TM][ext 0x0,0x0]

  master 1 gl owner 7000003525fb4f8 possible pid 16122136 xid 101E-01EB-00000177 bast 0 rseq 5 mseq 0 history 0x51449a51

  open opt KJUSERDEADLOCK  

lp 70000035aca2438 gl  KJUSERCW rp 70000036b321090 [0x13d03][0x0],[TM][ext 0x0,0x0]

  master 1 gl owner 70000035a680930 possible pid 65601692 xid 101B-01B1-0000041A bast 0 rseq 5 mseq 0 history 0x51449a51

  open opt KJUSERDEADLOCK  

lp 70000036cfa71c0 gl  KJUSERCW rp 70000036b321090 [0x13d03][0x0],[TM][ext 0x0,0x0]

  master 1 gl owner 70000035264bbb0 possible pid 39190724 xid 1008-008F-00000286 bast 0 rseq 5 mseq 0 history 0x51449a51

  open opt KJUSERDEADLOCK  

lp 700000358ec44e0 gl  KJUSERCW rp 70000036b321090 [0x13d03][0x0],[TM][ext 0x0,0x0]

  master 1 gl owner 7000003546d2ad0 possible pid 65732640 xid 1013-0136-000001A4 bast 0 rseq 5 mseq 0 history 0x51449a55

  open opt KJUSERDEADLOCK  

CONVERT_Q: 

lp 700000356fa12a8 gl KJUSERNL rl  KJUSERPW rp 70000036b321090 [0x13d03][0x0],[TM][ext 0x0,0x0]

  master 1 gl owner 7000003565f7208 possible pid 21954568 xid 101C-01C9-00000C78 bast 0 rseq 5 mseq 0 history 0x4951449a

  convert opt KJUSERGETVALUE  

 

从中可以看出，在资源70000036b321090上发生了单资源死锁。一些会话已经获得了KJUSERCW模式（即SX）的锁，又申请KJUSERPW模式（即SSX）的锁。由于这两种锁模式不兼容，导致锁等待。相互的锁等待造成了死锁。

可以看到引发死锁的具体SQL语句为：

DELETE FROM T_DEVICE WHERE T_DEVICE.ID = :id

可以从跟踪文件中看到被争用的具体资源：

resname       : [ 0x13d03][0x0],[ TM][ext 0x0,0x0]  

将十六进制编码转化为十进制数：0x13d03 = 81155

使用如下查询可以定位它表示的数据库对象：

  SELECT * FROM DBA_OBJECTS WHERE object_id = 81155;

结果显示是表T_DEVICE_APN。这张表是T_DEVICE的子表（外键定义为ON DELETE CASCADE），而且它在外键列上没有建索引。

这样就比较好解释了。在删除父表时，会引发子表上删除对应记录。由于外键没有索引，Oracle会扫描整张表来定位记录，并删除。这个操作会申请SSX模式（即KJUSERPW）的锁。多个并发会话同时申请SSX模式的锁，刚好与此前已申请的SX模式的锁冲突，导致了死锁。

解决办法也很简单，在子表上为外键建立索引即可解决问题。

归纳起来说，子表上为外键列建立索引，可以：
1）提高针对外键列的查询或改动性能
2）减小表级锁粒度，降低死锁发生的可能性