deadlock的一些Sample

rudeguy 2011-03-22

展開全文

當兩個或多個用戶同時等待對方所持有的資源時就會產生死鎖。有時我們也會在alert日志中看到一些日志提到deadlock,下面根據(jù)它的產生情況作了一個測試，并且演示了怎么去找出引起deadlock的原因。

測試：
Session 1 SID=144
SQL> update emp set sal=sal+1
2 where empno=7369;

1 row updated.

Session 2 SID=159
SQL> update emp set mgr=7001 where empno=7499;

1 row updated.

上面的sql執(zhí)行完之后

Session 1 SID =144
SQL> update emp set sal=sal+1
2 where empno=7499;
Hang....

這個時候查詢鎖的信息：

SQL> col owner format a15
SQL> col object_name format a20
SQL> col oracle_username format a15
SQL> select b.owner,b.object_name,b.object_id,a.session_id,a.oracle_username,a.process
2 from v$locked_object a,dba_objects b
3 where a.object_id=b.object_id;

OWNER       OBJECT_NAME OBJECT_ID SESSION_ID ORACLE_USERNAME PROCESS
--------------- -------------------- --------------- --------------- --------------------------    ------------
SCOTT        EMP                   51209         159               SCOTT                        4790
SCOTT         EMP                    51209         144               SCOTT                        5091

SQL> select sid,
        2         row_wait_obj# object_id,
      3         row_wait_file# file_no,
       4         row_wait_block# block,
       5         row_wait_row# row_num
       6 from v$session
       7 where row_wait_obj#=&object_id;
Enter value for object_id: 51209
old   7: where row_wait_obj#=&object_id
new   7: where row_wait_obj#=51209

       SID OBJECT_ID    FILE_NO BLOCK    ROW_NUM
---------- --------------   ----------   ---------- ----------
       144   51209           4             32           1

可以看到Session 1（SID 144）被阻塞，它在等待51209的row_num=1.

Session 2 sid=159
SQL> update emp set mgr=6000 where empno=7369;
Hang...

這個時候快速查詢會話的鎖定信息：
SQL> select sid,
2         row_wait_obj# object_id,
3         row_wait_file# file_no,
4         row_wait_block# block,
5         row_wait_row# row_num
6 from v$session
7 where row_wait_obj#=&object_id;
Enter value for object_id: 51209
old   7: where row_wait_obj#=&object_id
new   7: where row_wait_obj#=51209

       SID   OBJECT_ID   FILE_NO    BLOCK   ROW_NUM
----------   -------------- ------------ ---------- ----------
       144    51209          4               32          1
       159    51209          4               32          0

看到兩個會話都被阻塞，row_num也使對方所修改的行。

很快，session 1 SID=144報錯deadlock：

update emp set sal=sal+1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

查看alert 日志：
Wed Feb 20 17:20:34 2008
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/oracle/product/10.2.0/db_1/admin/dg1/udump/dg1_ora_5092.trc.

Trc文件的內容大略：黑體位說明

[oracle@dg1 bdump]$ more /u01/app/oracle/oracle/product/10.2.0/db_1/admin/dg1/udump/dg1_ora_5092.trc

*** 2008-02-20 17:20:34.411
*** ACTION NAME:() 2008-02-20 17:20:34.409
*** MODULE NAME:(SQL*Plus) 2008-02-20 17:20:34.409
*** SERVICE NAME:(SYS$USERS) 2008-02-20 17:20:34.409
*** SESSION ID:(144.12) 2008-02-20 17:20:34.409
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session: Current session 就是session 1，是這個session 報錯被回滾，并產生trc文件
update emp set sal=sal+1
where empno=7499

                       ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name          process session holds waits process session holds waits
TX-0009000b-00000106        21     144     X             15     159           X      --x鎖被144持有，159在等待
TX-0004000c-000000da        15     159     X              21     144           X     --x鎖被159持有，144在等待
session 144: DID 0001-0015-00000016     session 159: DID 0001-000F-00000011
session 159: DID 0001-000F-00000011     session 144: DID 0001-0015-00000016
Rows waited on:
Session 159: obj - rowid = 0000C809 - AAAMgJAAEAAAAAgAAA --等待的行的rowid
(dictionary objn - 51209, file - 4, block - 32, slot - 0)
Session 144: obj - rowid = 0000C809 - AAAMgJAAEAAAAAgAAB --等待的行的rowid
(dictionary objn - 51209, file - 4, block - 32, slot - 1)
Information on the OTHER waiting sessions:
Session 159:                     ---Session 159的一些信息
pid=15 serial=7 audsid=186 user: 54/SCOTT
O/S info: user: oracle, term: pts/2, ospid: 4790, machine: dg1
            program: sqlplus@dg1 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update emp set mgr=6000 where empno=7369

通過上面的rowid查詢，得出的幾個與查詢v$session得到的阻塞信息中的row_num對應

Session 159: obj - rowid = 0000C809 - AAAMgJAAEAAAAAgAAA
SQL> select dbms_rowid.rowid_object('AAAMgJAAEAAAAAgAAA') data_object_id#,
2         dbms_rowid.rowid_relative_fno('AAAMgJAAEAAAAAgAAA') rfile#,
3         dbms_rowid.rowid_block_number('AAAMgJAAEAAAAAgAAA') block#,
4         dbms_rowid.rowid_row_number('AAAMgJAAEAAAAAgAAA') row# from dual;

DATA_OBJECT_ID#    RFILE#   BLOCK#    ROW#
------------------------   ----------   ----------    ----------
          51209              4             32             0

Session 144: obj - rowid = 0000C809 - AAAMgJAAEAAAAAgAAB
SQL> select dbms_rowid.rowid_object('AAAMgJAAEAAAAAgAAB') data_object_id#,
2         dbms_rowid.rowid_relative_fno('AAAMgJAAEAAAAAgAAB') rfile#,
3         dbms_rowid.rowid_block_number('AAAMgJAAEAAAAAgAAB') block#,
4         dbms_rowid.rowid_row_number('AAAMgJAAEAAAAAgAAB') row# from dual;

DATA_OBJECT_ID#     RFILE# BLOCK#    ROW#
-------------------           ---------- ----------   ----------
          51209                4            32            1

到這里告一段落。

1. 通常，在alert日志中偶爾看到有deadlock的信息完全不必要驚慌,因為Oracle自己會處理
2. 當有大量deadlock的時候可以通過trc文件來確定產生的原因。
3. 某些系統(tǒng)Bug 可能也會引起deadlock,在各個版本不定的bug list能夠查詢到很多。