Oracle死結產生的原因和解決辦法

標籤：

如果有兩個會話，每個會話都持有另一個會話想要的資源，此時就會發生死結。
用下面實驗來說明死結的產生原因和解決辦法。
SESSION1:
SQL> create table t2 as select * from emp;
SQL> select * from t2 where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00        800                    20

SQL> update t2 set sal=sal+200 where empno=7369;

SQL> select * from t2 where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00       1000                    20

SESSION2:

SQL> select * from t2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 19811203 00:00:00        950                    30

SQL> update t2 set sal=sal+200 where empno=7900;

1 row updated.

SQL> select * from t2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 19811203 00:00:00       1150                    30

SESSION1:
SQL> select * from t2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 19811203 00:00:00        950                    30

SESSION2:
SQL> select * from t2 where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00        800                    20

SESSION1:
SQL> update t2 set sal=sal-200 where empno=7900; 
出現等待

SESSION2:
SQL> update t2 set sal=sal-200 where empno=7369;
死結發生，session2被阻塞
系統復原session1跟死結有關的sql。
update t2 set sal=sal-200 where empno=7900
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SESSION1:
SQL> select * from t2 where empno in (7369,7900);

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00       1000                    20
      7900 JAMES      CLERK           7698 19811203 00:00:00        950                    30
看到系統只復原了對empno=7900的修改。
而此時SESSION2的update t2 set sal=sal-200 where empno=7369;依然被SESSION1的第一條語句所阻塞。


我們現在查看產生鎖的情況
開啟第三個會話以DBA身份登入
SQL> select * from v$lock;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
29434224 29434238        165 XR          4          0          1          0       2998          0
29434280 29434294        165 CF          0          0          2          0       2988          0
294342DC 294342F0        167 PW          1          0          3          0       2980          0
29434338 2943434C        165 RS         25          1          2          0       2983          0
294343F0 29434404        166 RT          1          0          6          0       2983          0
2943444C 29434460        153 TX     589868        325          0          4         24          0
29434560 29434574        167 MR          1          0          4          0       2983          0
294345BC 294345D0        167 MR          2          0          4          0       2983          0
29434618 2943462C        167 MR          3          0          4          0       2983          0
29434674 29434688        167 MR          4          0          4          0       2983          0
294346D0 294346E4        167 MR          5          0          4          0       2983          0
2943472C 29434740        167 MR          6          0          4          0       2983          0
29434788 2943479C        167 MR          7          0          4          0       2983          0
294347E4 294347F8        167 MR        201          0          4          0       2983          0
2943489C 294348B0        164 TS          3          1          3          0       2979          0
288F6030 288F6048        144 TM      53257          0          3          0         51          0
288F60DC 288F60F4        153 TM      53257          0          3          0         24          0
28934170 28934194        153 TX     655363        241          6          0         24          0
28969404 28969428        144 TX     589868        325          6          0         51          1

19 rows selected.

用下面的SQL語句可以直接得出誰阻塞誰
SQL> select ( select username from v$session where sid=a.sid) blocker, a.sid,
        ‘ is blocking ‘,
        (select username from v$session where sid=b.sid) blockee, b.sid
from v$lock a, v$lock b
where a.block=1 and b.request>0 and a.id1=b.id1 and a.id2=b.id2;


BLOCKER                               SID ‘ISBLOCKING‘  BLOCKEE                               SID
------------------------------ ---------- ------------- ------------------------------ ----------
STONE                                 144  is blocking  STONE                                 153


發生死結的時候會在alert檔案裡記錄下資訊。
--查看作業系統下$ORACLE_BASE/admin/orcl/bdump/alert_orcl.log檔案，會找到如下資訊
Thu Sep  2 20:41:25 2010
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/orcl/udump/orcl_ora_4945.trc.

再查看追蹤檔案詳細資料
*** 2010-09-02 20:41:25.700
*** ACTION NAME () 2010-09-02 20:41:25.699
*** MODULE NAME (SQL*Plus) 2010-09-02 20:41:25.699
*** SERVICE NAME (SYS$USERS) 2010-09-02 20:41:25.699
*** SESSION ID (144.3) 2010-09-02 20:41:25.699
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090015-00000146        19     144     X             23     148           X
.......

SESSION2還在被SESSION1阻塞，只有在SESSION1執行提交或回退才能讓SESSION2繼續下去。
SESSION1:
commit; --將提交update t2 set sal=sal+200 where empno=7369的修改
或
rollback;--將取消update t2 set sal=sal+200 where empno=7369的修改

隨即SESSION2得到1 row updated.的資訊。

我們也可以用管理員帳號執行kill命令殺死導致阻塞的會話SESSION1來解決死結。
sqlplus / as sysdba
從上面查過的資訊可以知道產生阻塞的sid=144
通過查v$session視圖可以得出serial#

SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME       COMMAND    OWNERID TADDR
-------- ---------- ---------- ---------- -------- ---------- ----------- ---------- ---------- -----
29F0BED4        144          3     210009 29E24174         64 STONE                0 2147483644

殺死這個會話用下面的命令
alter system kill session ‘144,3‘;

總結：
    Oralce認為死結是應用導致的錯誤，Oracle中很少出現死結。導致死結的的頭號殺手是外鍵沒有加索引，第二號殺手則是位元影像索引遭到並發更新。
    如果外鍵沒有索引，我們更新了父表的主鍵時將會鎖住整個子表。如果外鍵沒有索引，刪除了父表中的一行，整個子表也會被鎖住。這樣就很容易產生很多問題，如果其中任何阻塞的會話鎖住了某一會話需要的資源就會出現一個死結。至於位元影像索引的並發更新，是會鎖住所有相關聯的行，也會導致問題的發生。
    在Oracle9i及以上版本中，這些全表鎖都是短期的，也就是僅在DML操作期間存在，而不是存在於整個事務期間。

本文轉自：http://www.itpub.net/thread-1347577-1-1.html

Oracle死結產生的原因和解決辦法