Oracle死锁log

Question

以下是lock信息，谁能看出点问题来吗？
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 21 15:50:01 2009Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing optionsSQL> ** Sessions Waiting on Blocked Session SID SERIAL# STATUS   WAITING_USERNAME                        OSUSER
MACHINE                 STATUS                  ROWS_PROCESSED
----- ------- -------- ------------------------------
------------------------------ ---------- -------- --------------
SQL_TEXT
-----------------------------------------------------------------
 121                  3266 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   137127
                
p5
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 252                  5424 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   137127
                
p5
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 252                  8481 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   140278
                
p2
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 270                  7869 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   137127
                
p3
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 279                  9276 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                        6
                
p3
UPDATE WORK_ORDER_ITEMS SET quantity_in_progress=quantity_in_prog
ress+:"SYS_B_0", last_modified_time=TO_TIMESTAMP (:"SYS_B_1", :"S
YS_B_2"), trx_id=:"SYS_B_3", xfr_update_pid=:"SYS_B_4" WHERE orde
r_item_key=:"SYS_B_5" 279                  9276 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                       67
                
p3
UPDATE WORK_ORDER_ITEMS SET quantity_in_progress=quantity_in_prog
ress+:"SYS_B_0", last_modified_time=TO_TIMESTAMP (:"SYS_B_1", :"S
YS_B_2"), trx_id=:"SYS_B_3", xfr_update_pid=:"SYS_B_4" WHERE orde
r_item_key=:"SYS_B_5"
6 rows selected.** Session Sql Blocking Lock SID SERIAL# STATUS   BLOCKING_USERNAME                       OSUSER
MACHINE                 STATUS                  ROWS_PROCESSED
----- ------- -------- ------------------------------
------------------------------ ---------- -------- --------------
SQL_TEXT
-----------------------------------------------------------------
 121                  3266 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   137129
                
p5
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 273                  5172 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                      697
                
p3
UPDATE TRACKED_OBJECT_STATUS SET previous_status=:"SYS_B_00", sta
tus=:"SYS_B_01", state=:"SYS_B_02", reason=NULL, previous_node_ke
y=-:"SYS_B_03", previous_node_name=NULL, previous_node_type=NULL,
previous_p_line_key=-:"SYS_B_04", previous_p_line_name=NULL, pre
vious_robj_key=-:"SYS_B_05", previous_robj_name=NULL, previous_ro
bj_type=NULL, queue_key=-:"SYS_B_06", queue_name=NULL, route_step
_key=:"SYS_B_07", route_step_name=NULL, wc_key=-:"SYS_B_08", wc_n
ame=NULL, p_queue_key=:"SYS_B_09", p_queue_name=NULL, p_line_key=
-:"SYS_B_10", p_line_name=NULL, tobj_queue_history_key=-:"SYS_B_1
1", revision=revision+:"SYS_B_12", last_modified_time=TO_TIMESTAM
P (:"SYS_B_13", :"SYS_B_14"), trx_id=:"SYS_B_15", xfr_update_pid=
:"SYS_B_16" WHERE tobj_status_key=:"SYS_B_17" 273                  5172 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                      715
                
p3
UPDATE TRACKED_OBJECT_STATUS SET previous_status=:"SYS_B_00", sta
tus=:"SYS_B_01", state=:"SYS_B_02", reason=NULL, previous_node_ke
y=-:"SYS_B_03", previous_node_name=NULL, previous_node_type=NULL,
previous_p_line_key=-:"SYS_B_04", previous_p_line_name=NULL, pre
vious_robj_key=-:"SYS_B_05", previous_robj_name=NULL, previous_ro
bj_type=NULL, queue_key=-:"SYS_B_06", queue_name=NULL, route_step
_key=:"SYS_B_07", route_step_name=NULL, wc_key=-:"SYS_B_08", wc_n
ame=NULL, p_queue_key=:"SYS_B_09", p_queue_name=NULL, p_line_key=
-:"SYS_B_10", p_line_name=NULL, tobj_queue_history_key=-:"SYS_B_1
1", revision=revision+:"SYS_B_12", last_modified_time=TO_TIMESTAM
P (:"SYS_B_13", :"SYS_B_14"), trx_id=:"SYS_B_15", xfr_update_pid=
:"SYS_B_16" WHERE tobj_status_key=:"SYS_B_17" 273                  5172 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                      850
                
p3
UPDATE TRACKED_OBJECT_STATUS SET previous_status=:"SYS_B_00", sta
tus=:"SYS_B_01", state=:"SYS_B_02", reason=NULL, previous_node_ke
y=-:"SYS_B_03", previous_node_name=NULL, previous_node_type=NULL,
previous_p_line_key=-:"SYS_B_04", previous_p_line_name=NULL, pre
vious_robj_key=-:"SYS_B_05", previous_robj_name=NULL, previous_ro
bj_type=NULL, queue_key=-:"SYS_B_06", queue_name=NULL, route_step
_key=:"SYS_B_07", route_step_name=NULL, wc_key=-:"SYS_B_08", wc_n
ame=NULL, p_queue_key=:"SYS_B_09", p_queue_name=NULL, p_line_key=
-:"SYS_B_10", p_line_name=NULL, tobj_queue_history_key=-:"SYS_B_1
1", revision=revision+:"SYS_B_12", last_modified_time=TO_TIMESTAM
P (:"SYS_B_13", :"SYS_B_14"), trx_id=:"SYS_B_15", xfr_update_pid=
:"SYS_B_16" WHERE tobj_status_key=:"SYS_B_17" 273                  5172 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                     1770
                
p3
UPDATE TRACKED_OBJECT_STATUS SET previous_status=:"SYS_B_00", sta
tus=:"SYS_B_01", state=:"SYS_B_02", reason=NULL, previous_node_ke
y=-:"SYS_B_03", previous_node_name=NULL, previous_node_type=NULL,
previous_p_line_key=-:"SYS_B_04", previous_p_line_name=NULL, pre
vious_robj_key=-:"SYS_B_05", previous_robj_name=NULL, previous_ro
bj_type=NULL, queue_key=-:"SYS_B_06", queue_name=NULL, route_step
_key=:"SYS_B_07", route_step_name=NULL, wc_key=-:"SYS_B_08", wc_n
ame=NULL, p_queue_key=:"SYS_B_09", p_queue_name=NULL, p_line_key=
-:"SYS_B_10", p_line_name=NULL, tobj_queue_history_key=-:"SYS_B_1
1", revision=revision+:"SYS_B_12", last_modified_time=TO_TIMESTAM
P (:"SYS_B_13", :"SYS_B_14"), trx_id=:"SYS_B_15", xfr_update_pid=
:"SYS_B_16" WHERE tobj_status_key=:"SYS_B_17" 252                  5424 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   137129
                
p5
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 270                  7869 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   137129
                
p3
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 252                  8481 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                   140279
                
p2
UPDATE UNIT SET note_key=note_key WHERE unit_key=:"SYS_B_0" 279                  9276 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                        6
                
p3
UPDATE WORK_ORDER_ITEMS SET quantity_in_progress=quantity_in_prog
ress+:"SYS_B_0", last_modified_time=TO_TIMESTAMP (:"SYS_B_1", :"S
YS_B_2"), trx_id=:"SYS_B_3", xfr_update_pid=:"SYS_B_4" WHERE orde
r_item_key=:"SYS_B_5" 279                  9276 ACTIVE   DEANZA                                                         root
ussy-mesap ACTIVE                                       67
                
p3
UPDATE WORK_ORDER_ITEMS SET quantity_in_progress=quantity_in_prog
ress+:"SYS_B_0", last_modified_time=TO_TIMESTAMP (:"SYS_B_1", :"S
YS_B_2"), trx_id=:"SYS_B_3", xfr_update_pid=:"SYS_B_4" WHERE orde
r_item_key=:"SYS_B_5"

Answer 1

肯定有commit.这是一个JBoss cluster (5台server) + Oracle RAC的环境。死锁出现时，客户提供的信息只有以上这些。

Answer 2

1.死锁检测：
SELECT /*+ rule */ s.username, 
decode(l.type,'TM','TABLE LOCK', 
              'TX','ROW LOCK', 
              NULL) LOCK_LEVEL, 
o.owner,o.object_name,o.object_type, 
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
FROM v$session s,v$lock l,dba_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) 
AND s.username is NOT NULL 2.以DBA用户,可以通过alter system kill session 'sid,serial'来杀掉会话。

Answer 3

检查一下应用，是不是应用里有dead lock

Answer 4

已经让客户执行了
ALTER SYSTEM KILL SESSION '121,3266';
ALTER SYSTEM KILL SESSION '252,5424';
ALTER SYSTEM KILL SESSION '270,7869';
ALTER SYSTEM KILL SESSION '273,5172';
ALTER SYSTEM KILL SESSION '279,9276';
ALTER SYSTEM KILL SESSION '252,8481';
ALTER SYSTEM KILL SESSION '273,1';
ALTER SYSTEM KILL SESSION '279,1';我无法看客户的DB，所以上述的死锁检测语句不适合，只能在客户提供的信息上分析:(

Answer 5

那就难办了，查查V$LOCK, V$LOCKED_OBJECT这两个动态视图，看看被锁的对象

Answer 6

既然是update比较多.那么从客户给的信息中没办法看出来是具体哪种情况造成的.
1. 是否有itl waits2. 是否有unique index or bitmap index3. 普通的行级别的竞争,也就是commit/rollback的问题.可看看v$lock  dba_waiters   v$enqueue_status的情况.特别是v$lockdba_waiters可以看到谁阻塞了谁.最后一个可以看到整体的enqueue情况.v$session_wait也最好是观察一下.

Answer 7

看看相关的表是不是建了外键，却没有在从表外键字段上建立相应的索引，这个是最有可能引起死锁的原因之一。Bitmap索引也是存在这个问题。

Answer 8

SELECT /*+ rule */ s.username, 
decode(l.type,'TM','TABLE LOCK', 
              'TX','ROW LOCK', 
              NULL) LOCK_LEVEL, 
o.owner,o.object_name,o.object_type, 
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
FROM v$session s,v$lock l,dba_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) 
AND s.username is NOT NULL test