set wrap off
set line 132
col USERNAME for a7
col MACHINE for a10
col PROGRAM for a20
select username,sid,serial#,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
USERNAME SID SERIAL# LOCKWAIT STATUS MACHINE PROGRAM
---------- ------- ---------- ---------------- -------- ---------- --------------------
FWBZ 18 61108 ACTIVE ctbjoss JDBC Thin Client
FWBZ 219 109 ACTIVE ctbjoss JDBC Thin Client
FWBZ 221 54948 ACTIVE ctbjoss JDBC Thin Client
set wrap off
set line 132
col OWNER for a15
col OBJECT_NAME for a20
col ORACLE_USERNAME for a15
col OS_USER_NAME for a12
select l.xidusn, l.object_id, o.owner, o.object_name,o.object_type,
l.session_id, l.oracle_username, l.os_user_name, l.process,
decode(l.locked_mode,
0, '',
1, 'NULL',
2, '(SS)',
3, '(SX)',
4, '(S)',
5, '(SSX)',
6, '(X)',
'???') locked_mode
from v$locked_object l, dba_objects o
where l.object_id= o.object_id
v$locked_object视图列出当前系统中哪些对象正被锁定,其主要字段说明如下:
字段名称 | 类型 | 说明 |
XIDUSN | NUMBER | 回滚段号; |
XIDSLOT | NUMBER | 事物槽号; |
XIDSQN | NUMBER | 序列号; |
OBJECT_ID | NUMBER | 被锁对象标识; |
SESSION_ID | NUMBER | 持有锁的会话(SESSION)标识; |
ORACLE_USERNAME | VARCHAR2(30) | 持有该锁的用户的Oracle用户名; |
OS_USER_NAME | VARCHAR2(15) | 持有该锁的用户的操作系统用户名; |
PROCESS | VARCHAR2(9) | 操作系统的进程号; |
LOCKED_MODE | NUMBER | 锁模式,取值同表三中的LMODE; |
LOCKED_MODE:
锁模式:0(None),1(null),2(row share), 3(row exclusive),4 (share),5(share row exclusive),6(exclusive)
排查手法:
通过查询视图,找到被锁住的对象v$locked_object,根据其locked_mode,判断其锁类型
查询SQL语句:
set wrap off
set line 132
col OWNER for a15
col OBJECT_NAME for a20
col ORACLE_USERNAME for a15
col OS_USER_NAME for a12
select l.xidusn, l.object_id, o.owner, o.object_name,
l.session_id, l.oracle_username, l.os_user_name, l.process,
decode(l.locked_mode, 0, '',
1, 'NULL',
2, '(SS)',
3, '(SX)',
4, '(S)',
5, '(SSX)',
6, '(X)',
'???') locked_mode
from v$locked_object l, dba_objects o
where l.object_id= o.object_id
判断查询结果,发现两个Session对同一个表的数据行进行了排他。
用以下的语句对视图v$sqltext进行查询,可以得到当前正在执行的SQL语句,以及执行SQL语句的session
select username, osuser, machine, terminal, program,
sid, serial#, status, sql_address, sql_text
from v$session ss, v$sqltext sq
where type ='USER'
and ss.sql_address = sq.address
orderby ss.sid, ss.serial#, sq.piece
可以发现对同一表中的同一数据行进行更新的两条SQL语句。
通过这两条SQL语句,可以定位Java程序中导致问题的代码。
问题原因分析:
对Java代码进行分析后,发现有一个按照数据行主键,逐行循环处理的操作。
不幸的是,循环前,从KeySet()生成的主键列表没有进行排序,导致每次循环的执行顺序都是随机的。
例如,假设两个session都想要对数据行A,B,C,D进行处理,很有可能session1先处理了A,B,
而此时session2刚好处理完了C,D。这样,session1想要继续处理的C,D由于正被session2给锁住,所以无法继续执行。
session2想要处理的A,B也被session1给锁着,session2也无法继续,两个session最终都没有办法终止。
借助于ORACLE的TRACE文件
Oracle发现死锁后,会在alert_[SID].log文件中输出如下的警告信息:ORA-00060: Deadlock detected.
并提示去查看相应的*.trc文件。通过分析*.trc文件可以看到死锁的详细情况,
下面是一个*.trc文件的例子:
*** 2012-01-09 20:11:22.379
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-000a0006-0000f48f 65 101 X 64 102 X
TX-0007000f-0000d8a3 64 102 X 65 101 X
session 101: DID 0001-0041-00000002 session 102: DID 0001-0040-00000002
session 102: DID 0001-0040-00000002 session 101: DID 0001-0041-00000002
这里明确指出了发生死锁的两个session的ID
Rows waited on:
Session 101: obj - rowid = 0008915A - AACJFaAAFAAEwq1AAA
(dictionary objn - 561498, file - 5, block - 1247925, slot - 0)
Session 102: obj - rowid = 0008915A - AACJFaAAFAAEwq1AAI
(dictionary objn - 561498, file - 5, block - 1247925, slot - 8)
----- Information for the OTHER waiting sessions -----
Session 102:
sid: 102 ser: 2 audsid: 25260645 user: 87/USR_BAT flags: 0x41
pid: 64 O/S info: user: ora_1, term: UNKNOWN, ospid: 5915
image: oracle@pcXX
client details:
O/S info: user: root, term: unknown, ospid: 1234
machine: pcXX program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
UPDATE XXXX SET XXXX 这里是导致死锁的SQL语句1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
*** 2012-01-09 20:11:22.530
----- Current SQL Statement for this session (sql_id=b0qn65w78t10b) -----
UPDATE XXXX SET XXXX 这里是导致死锁的SQL语句2
===================================================