当两个或多个用户同时等待对方所持有的资源时就会产生死锁。有时我们也会在alert日志中看到一些日志提到deadlock,下面根据它的产生情况作了一个测试，并且演示了怎么去找出引起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执行完之后
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. 某些系统Bug 可能也会引起deadlock,在各个版本不定的bug list能够查询到很多。
