oracle 死鎖發生的測試用例

會話A:

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 3 09:42:02 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select * from test;

        ID
----------
      1000

SQL> create table testb as select * from test;

Table created.

SQL> select * from testb;

        ID
----------
      1000

SQL> update test set id=20000;

1 row updated.    --更新表A test

會話B 

SQL> update testb set id=100;

1 row updated.    --更新表B testb

會話A

SQL> update testb set id=4000;

 阻塞等待  --更新表B

會話B  --更新表A  發生死鎖

SQL> update test set id=300;
update test set id=300
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

oracle 認為死鎖是少見的,不一般的,所以一旦發生死鎖,它在伺服器上建立一個跟蹤檔案,跟蹤檔案的內容

大概如下:

*** SESSION ID:(10.11) 2008-03-03 09:44:24.113
DEADLOCK DETECTED
Current SQL statement for this session:
update test set id=300
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-000a0019-000003bc        12      10     X             10       9           X
TX-0007002a-00000366        10       9     X             12      10           X
session 10: DID 0001-000C-00000002 session 9: DID 0001-000A-0000000B
session 9: DID 0001-000A-0000000B session 10: DID 0001-000C-00000002
Rows waited on:
Session 9: obj - rowid = 00007D89 - AAAH2JAABAAAKuiAAA
  (dictionary objn - 32137, file - 1, block - 43938, slot - 0)
Session 10: obj - rowid = 00001A6F - AAABpvAAFAAAAAOAAA
  (dictionary objn - 6767, file - 5, block - 14, slot - 0)
Information on the OTHER waiting sessions:
Session 9:
  pid=10 serial=3 audsid=0 user: 0/SYS
  O/S info: user: AVOCADO\Paul Yi, term: , ospid: 2696:2688, machine: AVOCADO\D10063
            program: sqlplus.exe
  application name: sqlplus.exe, hash value=0
  Current SQL Statement:
  update testb set id=4000
End of information on OTHER waiting sessions.