【Troubleshooting】外键不建索引易导致死锁

1.外键不建索引，更新外键字段会锁住父表，易导致死锁

父表：P_TABLE

子表：F_TABLE

外键字段：object_id

 

未建索引：

session1:

SQL>update f_table set object_id='10000' where object_id=116053;

 

1 rowupdated.

 

session2:

SQL>update f_table set object_id='10001' where object_id=116054;

 

1 rowupdated.

 

session1：

SQL>update p_table set object_id='10000' where object_id=116053;

hang住！

 

查看锁情况：

[EPDEV]/u01/oracle$orawholockwho

 

USERNAME          SID   SERIAL# 'ISBLOCKING' USERNAME          SID    SERIAL#

-------------------- ---------- ------------- ---------- ---------- ----------

XQW               302      18695 is blocking  XQW               177      28312

 

[EPDEV]/u01/oracle$oralock

 

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------------------ ------------ ---------- ---------- ---------- ----------------

       177 DML          Row-X (SX)   None             257638          0        113 Not Blocking

       177 DML          Row-X (SX)   S/Row-X(SSX)     257637          0        66 Not Blocking

       177 Transaction  Exclusive   None             262176     311407         66 Not Blocking

       302 DML          Row-X (SX)   None             257637          0         57 Blocking

       302 DML          Row-S (SS)   None            257638          0         57 Not Blocking

       302 Transaction  Exclusive   None             655375     322355         57 Not Blocking

session1需要S/Row-X(SSX)锁，而这个锁为session2所拥有，因此session2阻塞了session1，session1  hang住。

 

session2：

SQL>update p_table set object_id='10001' where object_id=116054;

hang住！

此时就产生了死锁，到session1发现报错：

session1：

SQL>update p_table set object_id='10000' where object_id=116053;

updatep_table set object_id='10000' where object_id=116053

       *

ERROR atline 1:

ORA-00060: deadlock detected while waiting for resource

 

查看锁情况：

[EPDEV]/u01/oracle$ora wholockwho

 

USERNAME          SID   SERIAL# 'ISBLOCKING' USERNAME          SID    SERIAL#

-------------------- ---------- ------------- ---------- ---------- ----------

XQW               177      28312 is blocking  XQW               302      18695

 

[EPDEV]/u01/oracle$oralock

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------------------ ------------ ---------- ---------- ---------- ----------------

       177 DML          Row-X (SX)   None             257638          0        144 Not Blocking

       177 DML          Row-X (SX)   None             257637          0         97 Blocking

       177 Transaction  Exclusive   None             262176     311407         97 Not Blocking

       302 DML          Row-X (SX)   S/Row-X(SSX)     257637          0         88 Not Blocking

       302 DML          Row-S (SS)   None             257638          0         88 Not Blocking

       302 Transaction  Exclusive   None             655375     322355         88 Not Blocking

 

 

建了索引：

SQL>create index idx_ftable on f_table(object_id);

 

Indexcreated.

 

session1:

SQL>update f_table set object_id='10000' where object_id=116053;

 

1 rowupdated.

 

session2:

SQL>update f_table set object_id='10001' where object_id=116054;

 

1 rowupdated.

 

session1：

SQL>update p_table set object_id='10000' where object_id=116053;

updatep_table set object_id='10000' where object_id=116053

*

ERROR atline 1:

ORA-00001:unique constraint (XQW.PK_PTABLE) violated

 

session2：

SQL>update p_table set object_id='10001' where object_id=116054;

updatep_table set object_id='10001' where object_id=116054

*

ERROR atline 1:

ORA-00001:unique constraint (XQW.PK_PTABLE) violated

 

没有hang住！

 

看下锁的情况：

[EPDEV]/u01/oracle$ora lock

 

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------------------ ------------ ---------- ---------- ---------- ----------------

       216 DML          Row-S (SS)   None             257638          0         18 Not Blocking

       216 DML          Row-X (SX)   None             257637          0        136 Not Blocking

       216 Transaction  Exclusive   None             458779    310843         70 Not Blocking

       302 Transaction  Exclusive   None             131077     332185         45 Not Blocking

       302 DML          Row-S (SS)   None             257638          0         45 Not Blocking

       302 DML          Row-X (SX)   None             257637          0         45 Not Blocking

 

 

2.外键不建索引，删除父表会锁住子表，易导致死锁

未建索引：

session1:

--执行一个删除操作，这时候在子表和父表上都加了一个Row-S(SX)锁

SQL>delete from f_table where object_id=100;

 

1 rowdeleted.

 

SQL>delete from p_table where object_id=100;

 

1 rowdeleted.

 

[EPDEV]/u01/oracle$ora lock 216

 

Sessionaltered.

 

 

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------------------ ------------ ---------- ---------- ---------- ----------------

       216 DML          Row-X (SX)   None             257638          0          3 Not Blocking

       216 DML          Row-X (SX)   None             257637          0         30 Not Blocking

       216 Transaction  Exclusive   None             196616     423931         30 Not Blocking

 

session2:

--执行另一个删除操作，发现这时候第二个删除语句等待

SQL>delete from f_table where object_id=200;

 

1 rowdeleted.

 

SQL>delete from p_table where object_id=200;

hang住！

 

session1：

--死锁马上发生

SQL> deletefrom p_table where object_id=100;

hang住！

 

查看锁的情况：

[EPDEV]/u01/oracle$orawholockwho

 

USERNAME          SID   SERIAL# 'ISBLOCKING' USERNAME          SID    SERIAL#

-------------------- ---------- ------------- ---------- ---------- ----------

XQW               216      49878 is blocking  XQW               302      18695

 

[EPDEV]/u01/oracle$oralock

 

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------------------ ------------ ---------- ---------- ---------- ----------------

       216 DML          Row-X (SX)   None             257637          0         21Blocking

       216 DML          Row-X (SX)   None             257638          0         21 Not Blocking

       216 Transaction  Exclusive   None             655371     322311         21 Not Blocking

       302 Transaction  Exclusive   None              65583     323836         14 Not Blocking

       302 DML          Row-X (SX)   S/Row-X(SSX)     257637          0         14 Not Blocking                                 

       302 DML          Row-S (SS)   None             257638          0         14 Not Blocking

 

session2：

SQL>delete from p_table where object_id=200;

deletefrom p_table where object_id=200

            *

ERROR atline 1:

ORA-00060: deadlock detected while waiting for resource

 

查看锁情况：

[EPDEV]/u01/oracle$orawholockwho

 

USERNAME          SID   SERIAL# 'ISBLOCKING' USERNAME          SID    SERIAL#

-------------------- ---------- ------------- ---------- ---------- ----------

XQW               302      18695 is blocking  XQW               216      49878

 

[EPDEV]/u01/oracle$oralock

 

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------ ------------------------ ---------- ---------- ---------- ----------------

       216 Transaction  None        Exclusive        458775     312402         48 Not Blocking

       216 Transaction  Exclusive   None             524309     396893         69 Not Blocking

       216 DML          Row-X (SX)   None             257638          0         66 Not Blocking

       216 DML          Row-X (SX)   None             257637          0         66 Not Blocking

       302 DML          Row-X (SX)   None             257637          0         60 Not Blocking

       302 DML          Row-S (SS)   None             257638          0         60 Not Blocking

       302 Transaction  Exclusive   None             458775     312402         60 Blocking

 

建了索引：

SQL>create index idx_ftable on f_table(object_id);

 

Indexcreated.

 

session1:

SQL>delete from f_table where object_id=100;

 

1 rowdeleted.

 

SQL>delete from p_table where object_id=100;

 

1 rowdeleted.

 

session2:

SQL>delete from f_table where object_id=200;

 

1 rowdeleted.

 

SQL>delete from p_table where object_id=200;

 

1 rowdeleted.

 

 

查看锁的情况：

[EPDEV]/u01/oracle$oralock

 

       SID TYPE         HOLD         REQUEST             ID1        ID2     CTIME BLOCK_OTHERS

---------- ------------------------ ------------ ---------- ---------- ---------- ----------------

       216 DML          Row-X (SX)   None             257638          0        135 Not Blocking

       216 DML          Row-X (SX)   None             257637          0        136 Not Blocking

       216 Transaction  Exclusive   None             589868     400648        136 Not Blocking

       302 DML          Row-X (SX)   None             257638          0        193 Not Blocking

       302 DML          Row-X (SX)   None             257637          0        196 Not Blocking

       302 Transaction  Exclusive   None             524321     396932        196 Not Blocking

 

回到session1：

SQL>delete from f_table where object_id=100;

 

0 rowsdeleted.

没有hang住，没有产生死锁

 

--当对子表的外键列添加索引后，死锁被消除，因为这时删除父表记录不需要对子表加表级锁。

 

----------------------------------------------------------------------------------------------
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/39183189
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/39183189
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/39183189