会话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.