本文主要是介绍外键缺乏索引导致的Oracle数据库死锁，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
与MySQL不同，Oracle不会在子表上为外键列自动创建索引。 外键上 缺乏索引可能会造成严重的性能问题，甚至于死锁。对于从MySQL
迁移至Oracle的数据库应用，应该特别注意这个问题。如果父表存在删除记录或者更改外键列的情形，那么就需要在子表上为外键列创建索引。
下面通过一个实例，分析 Oracle数据库 缺乏外键索引可能造成的后果。
具体情况是，在数据库报警日志中发现了死锁情况：
将十六进制编码转化为十进制数：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）减小表级锁粒度，降低死锁发生的可能性
迁移至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 文件中的内容：
查看 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]
可以看到引发死锁的具体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）减小表级锁粒度，降低死锁发生的可能性
这篇关于外键缺乏索引导致的Oracle数据库死锁的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
