模拟会话被阻塞
--会话1
SQL> select sid from v$mystat where rownum=1;
SID
----------
15
SQL> create table t_xifenfei (id number,name varchar2(20));
Table created.
SQL> insert into t_xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into t_xifenfei values(2,'www.xifenfei');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
ID NAME
---------- ----------------------------------------
1 xifenfei
2 www.xifenfei
SQL> delete from t_xifenfei where id=1;
1 row deleted.
--会话2
SQL> select sid from v$mystat where rownum=1;
SID
----------
143
SQL> delete from chf.t_xifenfei where id=1;
--hang住
新打开会话做Systemstates
我们假设不知道会话1和会话2的sid,现在特定的使用Systemstates分析问题,后面给出简单分析方法
SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug dump systemstate 10 Statement processed. SQL> oradebug dump systemstate 10 Statement processed. SQL> oradebug tracefile_name /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_31027.trc SQL> exit
阅读trace文件
会话2在这样的情况下hang住,而其他会话正常,第一反应是在lock级别阻塞了,而这个又是DML操作,很自然想到是TX,于是在trace文件中搜索”enq: TX” ,找到对应记录,然后向上找到对应的进程号,开始读相关内容,发现有小信息如下:
PROCESS 20:
----------------------------------------
--客户端信息
client details:
O/S info: user: oracle, term: pts/0, ospid: 30622
machine: xifenfei program: sqlplus@xifenfei (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
--进程相关session信息
(session) sid: 15 ser: 151 trans: 0x343a4c2c, creator: 0x35fe2218
flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: (nil)
oct: 0, prv: 0, sql: (nil), psql: 0x2f6e7b68, user: 84/CHF
--被阻塞会话信息
There are 1 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 143, ser: 229
wait event: 'enq: TX - row lock contention'
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x40005
p3: 'sequence'=0x252
row_wait_obj#: 75928, block#: 171, row#: 0, file# 4
min_blocked_time: 296 secs, waiter_cache_ver: 7860
Wait State:
fixed_waits=0 flags=0x22 boundary=(nil)/-1
--54580006 is split into ASCII 54 + ASCII 58 (TX) + Mode 0006 (X) ...
SQL> select object_type,object_name,owner from dba_objects where object_id=75928;
OBJECT_TYP OBJECT_NAME OWNER
---------- -------------------- ----------
TABLE T_XIFENFEI CHF
--持有锁的信息
(enqueue) TX-00040005-00000252 DID: 0001-0014-0000009C
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
mode: X, lock_flag: 0x0, lock: 0x343a4c6c, res: 0x353606a8
own: 0x355ae5b8, sess: 0x355ae5b8, proc: 0x35fe2218, prv: 0x353606b0
--通过上述信息可以分析出结论：
sqlplus登录的sid=15的会话占用了TX mode=6(mode:x)的锁,阻塞了sid=143会话对chf.t_xifenfei表操作
找出被阻塞进程相关信息(sid 为143的进程),搜索”sid: 143″，阅读相关进程信息
PROCESS 21:
----------------------------------------
--相关session信息
(session) sid: 143 ser: 229 trans: 0x343915a0, creator: 0x35fe2d3c
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: (nil)
oct: 7, prv: 0, sql: 0x2f6cb234, psql: 0x2f6dd5cc, user: 0/SYS
--当前等待信息
Current Wait Stack:
0: waiting for 'enq: TX - row lock contention'
name|mode=0x54580006, usn<<16 | slot=0x40005, sequence=0x252
wait_id=12 seq_num=13 snap_id=1
wait times: snap=5 min 1 sec, exc=5 min 1 sec, total=5 min 1 sec
wait times: max=infinite, heur=5 min 1 sec
wait counts: calls=101 os=101
in_wait=1 iflags=0x15a0
--阻塞该会话的session信息
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 15, ser: 151
Dumping final blocker:
inst: 1, sid: 15, ser: 151
Wait State:
fixed_waits=0 flags=0x22 boundary=(nil)/-1
--请求锁信息
SO: 0x352f8fcc, type: 8, owner: 0x35765fe8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x35fe2d3c, name=enqueue, file=ksq1.h LINE:380, pg=0
(enqueue) TX-00040005-00000252 DID: 0001-0015-0000003B
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
req: X, lock_flag: 0x10, lock: 0x352f8ff8, res: 0x353606a8
own: 0x356f49b8, sess: 0x356f49b8, proc: 0x35fe2d3c, prv: 0x353606b8
--通过对被阻塞对象分析,可以得出和阻塞者相同的信息
对该问题的常规分析思路
--查询等待事件
SQL> select event,p1,p2,p3 from v$session where wait_class#<>6;
EVENT P1 P2 P3
------------------------------ ---------- ---------- ----------
SQL*Net message to client 1650815232 1 0
enq: TX - row lock contention 1415053318 262149 594
--查询锁信息(因为通过上面的等待事件分析,TX可能引起会话hang)
SQL> SELECT * FROM v$lock where type in ('TM','TX');
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
352F8FCC 352F8FF8 143 TX 262149 594 0 6 4181 0
B69CC7A8 B69CC7D8 143 TM 75928 0 3 0 4181 0
B69CC7A8 B69CC7D8 15 TM 75928 0 3 0 4266 0
343A4C2C 343A4C6C 15 TX 262149 594 6 0 4267 1
--通过TM查询出来对应对象
SQL> select object_type,object_name,owner from dba_objects where object_id=75928;
OBJECT_TYP OBJECT_NAME OWNER
---------- -------------------- ----------
TABLE T_XIFENFEI CHF
--通过观察v$lock查询结果可以知道:
15会话的TX MODE=6的锁阻塞了143会话想会的的TX MODE=6的锁,从而是的143会话hang住
Systemstates分析参考文档:Understanding and Reading Systemstates或者[ID 423153.1]
