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.

ITPUB个人空间m,dlKX;U7QG)CD
Connected to:ITPUB个人空间D:DwH*P q%tm+C X
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
`
V%W&g(j)Z0With the Partitioning, OLAP and Oracle Data Mining options
f+sh
]!?3wNJG%^0JServer Release 9.2.0.4.0 - Production

SQL> select * from test;

        IDITPUB个人空间;nq$^'zsW8O_
----------
Js@DD\E#h0      1000

SQL> create table testb as select * from test;

Table created.

SQL> select * from testb;

        ID
;M%\k,n,ZQ,t0----------ITPUB个人空间FC?j9B[2Pt
      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;
6Cc'|o c!J*G(a3]k|0update test set id=300
N]~]&JwA0       *
jK"C/et!PJ3K[0ERROR at line 1:
&q6~]KM [0ORA-00060: deadlock detected while waiting for resource

oracle 认为死锁是少见的,不一般的,所以一旦发生死锁,它在服务器上创建一个跟踪文件,跟踪文件的内容

大概如下:

*** SESSION ID:(10.11) 2008-03-03 09:44:24.113ITPUB个人空间4`*fMhjk
DEADLOCK DETECTED
"XDL9ke6[3FR2g0Current SQL statement for this session:ITPUB个人空间JG c'G'GS:enl
update test set id=300
xW7aDJJ#L0The following deadlock is not an ORACLE error. It is aITPUB个人空间C cDU-|v&f&@
deadlock due to user error in the design of an applicationITPUB个人空间-|&`l9D2Eo-l.`
or from issuing incorrect ad-hoc SQL. The followingITPUB个人空间%KW*y!h/M&Cw
information may aid in determining the deadlock:ITPUB个人空间;tHRS5W3Y5\
Deadlock graph:
.a&h2F0J}i&@0                       ---------Blocker(s)--------  ---------Waiter(s)---------
t} f)gBS0Resource Name          process session holds waits  process session holds waitsITPUB个人空间W4wrG5I
TX-000a0019-000003bc        12      10     X             10       9           X
h*J#M#\Q:si0TX-0007002a-00000366        10       9     X             12      10           XITPUB个人空间qV0t];J)|D
session 10: DID 0001-000C-00000002 session 9: DID 0001-000A-0000000BITPUB个人空间km b
Q$`8t`K
session 9: DID 0001-000A-0000000B session 10: DID 0001-000C-00000002ITPUB个人空间M*nH6U*l!X-y"tUvt
Rows waited on:ITPUB个人空间%j|8FM"F;lRq
Session 9: obj - rowid = 00007D89 - AAAH2JAABAAAKuiAAAITPUB个人空间;ueu.f)C/Trgk
}3b
  (dictionary objn - 32137, file - 1, block - 43938, slot - 0)ITPUB个人空间1Us1T^L
Session 10: obj - rowid = 00001A6F - AAABpvAAFAAAAAOAAA
,z\PoO3rQ0  (dictionary objn - 6767, file - 5, block - 14, slot - 0)
2? G$bb`"lR0Information on the OTHER waiting sessions:
N6b.C;UH1[E0Session 9:
#mv
T KQ$i0}^,Z0  pid=10 serial=3 audsid=0 user: 0/SYSITPUB个人空间*Y k:p;lvg
  O/S info: user: AVOCADO\Paul Yi, term: , ospid: 2696:2688, machine: AVOCADO\D10063ITPUB个人空间`G `5Bw2Z
            program: sqlplus.exe
Ch`a2z0  application name: sqlplus.exe, hash value=0ITPUB个人空间L#XJ4GjA!I.M'n
  Current SQL Statement:
`rf0[$Caw)w\0  update testb set id=4000ITPUB个人空间-y.F7X*Y#fIy$[R
End of information on OTHER waiting sessions.