测试的时候数据库外键导致死锁_外键缺乏索引导致的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

KJUSERCWrp 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

KJUSERCWrp 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

KJUSERCWrp 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

KJUSERCWrp 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

KJUSERPWrp 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