之前有个客户提出，在insert的时候被hang住了，碰到这样的问题很可能是锁的问题。对于这样的问题我一般就会想到几个常用的视图：v$lock/v$transaction/v$session/v$sql,仔细观察就可以查出问题的所在。
好，那我们现在去分析一下上面的现象，分析问题的一个常规手段就是能模拟故障的重现，我来试试模拟一下：
1.查出当前操作的会话号为125
SQL> select sid from v$mystat where rownum=1;
SID----------
125
2.创建表及插入数据
SQL> create table t_gyj(id int primary key,name varchar2(10));
Table created.
SQL> insert into t_gyj values(1,'gyj1');
1 row created.
注意这里先不要commit(提交).
3.再开一个会话,会话号为17
SQL> select sid from v$mystat where rownum=1;
SID----------
17
4.在17号会话上插入同样的一条记录,这时被阻塞了.
SQL> insert into t_gyj values(1,'gyj1');
这里就出现了上面提到的同时插入同一行记录，产生阻塞的现象,现象出现我们如何判断分析呢?
5.开始分析,刚刚提到几个视图v$lock,v$transaction,v$session,v$sql,这几个视图是DBA必须的.
为了方便查询,我在上面的操作特意把会话号给显示一下125号和17号会话.
被阻塞了我们一般首先会想到是不是锁住了
OK,我再开一个会话:
SQL> select sid from v$mystat where rownum=1;
SID----------
19
SQL> set linesize 1000
SQL> select * from v$lock where sid in (125,17);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
00000003A445F998 00000003A445F9F0 125 AE 100 0 4 0 883 0
00000003A44601D0 00000003A4460228 17 AE 100 0 4 0 595 0
00000003A44602A0 00000003A44602F8 17 TX 327681 1022 0 4 474 0
FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0 17 TM 74868 0 3 0 474 0
FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0 125 TM 74868 0 3 0 565 0
00000003A2CFF908 00000003A2CFF980 17 TX 458765 764 6 0 474 0
00000003A2D3E928 00000003A2D3E9A0 125 TX 327681 1022 6 0 565 1
我们在v$lock.REQUEST中看到会话17在请求4号锁,那到底是谁阻塞了17号呢,我们他细看在v$lock.BLOCK中看到125号会话阻塞了别的
会话(因为125号会话的BLOCK=1,说明他持有了锁资源,这里如果另一会话去申请这个锁就会被阻塞),OK,那这里要找到125号到底阻塞了
哪个会话,这个很简单,我们看BLCOK=1的这行,它的锁名称是:TX-327681-1022.知道了锁名称是不是去查一下哪个会话也要这个锁,那是
不是17号会话在请求4号锁的这行REQUEST=4,它的锁名称是不是也是:TX-327681-1022.
找到锁的阻塞之后,要解决很简单kill掉125会话就可以了,当然这里最好要和开发的确认完过之后再操作.
方法一:通过会话号找到serial#(串行号)
SQL> select sid,serial# from v$session where sid=125;
SID SERIAL# 125 13
alter system kill session '125,13';
方法二:通过会话找到SPID(进程号):
SQL> select spid from v$process where addr =(select paddr from v$session where sid=125);
SPID
7048
-bash-3.2# kill -9 7048
6.到这里为止我们已把问题解决了,但这不是我们的目的,我们继续为分析一下4号锁的原凶.
大家知道v$lock.LMODE或v$lock.REQUEST有几种模式吗?马上打开官方文档Books->REF->Reference->搜索v$lock
Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
4号锁叫共享锁,可以通过lock table table_name in share mode;手工命令添加该共享锁.
那我们这里怎么会产生的4号锁呢?
我结合四个视图v$lock,v$transaction,v$session,v$sql看看能不能找到点有作的信息.
SQL> select * from v$lock where sid in (125,17);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
00000003A445F998 00000003A445F9F0 125 AE 100 0 4 0 2550 0
00000003A44601D0 00000003A4460228 17 AE 100 0 4 0 2262 0
00000003A44602A0 00000003A44602F8 17 TX 327681 1022 0 4 2141 0
FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0 17 TM 74868 0 3 0 2141 0
FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0 125 TM 74868 0 3 0 2232 0
00000003A2CFF908 00000003A2CFF980 17 TX 458765 764 6 0 2141 0
00000003A2D3E928 00000003A2D3E9A0 125 TX 327681 1022 6 0 2232 1
从上面v$lock中能看出一点信息:
TM锁:TM-74868-0,可以找出是哪个对象被阻塞了:
SQL> select owner,object_name from dba_objects where object_id=74868;
OWNER OBJECT_NAME
GYJ T_GYJ
TX锁:TX-327681-1022,可以找到undo相关的信息
对327681分解,它是由四个字节组成的,把它从10进制先转成16进制.
SQL> select to_char('327681','xxxxxxxxx') from dual;
TO_CHAR('3
----------
50001
10进制327681分解成16进制0x50001,由四字节由成,高字节由两字了0005,低字节由两字节0001,可以得出很有用的信息5号回滚段第1号槽
可以对应视图v$transaction( XIDUSN ,XIDSLOT),同时XIDSQN=1022正好与TX-327681-1022吻合.
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS 7 13 764 3 703 253 33 ACTIVE
5 1 1022 3 11539 186 14 ACTIVE
嘿嘿,这个时候我来个dump,做dump操作主要是为了能找到更有助于我们分析的信息.
我dump 5号回滚段的3号文件的11539号块:
SQL> alter system dump datafile 3 block 11539;
System altered.
-bash-3.2$ cd /export/home/oracle/diag/rdbms/dtrace/dtrace/trace
找到３号文件的第11539号undo块的第14(v$transaction.UBAREC=14)条undo记录的dump信息
*-----------------------------
- Rec #0xe slt: 0x01 objn: 74869(0x00012475) objd: 74869 tblspc: 7(0x00000007)
- Layer: 10 (Index) opc: 22 rci 0x0d
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0xffff.000.00000000 uba: 0x00000000.0000.00
flg: C--- lkc: 0 scn: 0x0000.001598deDump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x18000ea block=0x018000eb
(kdxlpu): purge leaf row
key :(3): 02 c1 02
从上面的 objn: 74869可以得到对象号:
SQL> select owner,object_name,object_type from dba_objects where object_id=74869;
OWNER OBJECT_NAME OBJECT_TYPE
GYJ SYS_C0011159 INDEX
是个索引,这是哪个表的索引呢???
SQL> select owner,table_name from dba_indexes where index_name='SYS_C0011159';
OWNER TABLE_NAME
GYJ T_GYJ
是表T_GYJ上的索引,这个我没建过索引呢,怎么会有索引呢,哦想起来了,我对这个表建了主键(PRIMARY KEY),
那肯定产生了唯一索引
SQL> select OWNER,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where index_name='SYS_C0011159';
OWNER TABLE_NAME CONSTRAINT_NAME C
GYJ T_GYJ SYS_C0011159 P
继续查下去:看看v$session的等待事件以及是哪个sql产生的
SQL> set linesize 1000
SQL> select sid,event,sql_id from v$session where wait_class <>'Idle';
SID EVENT SQL_ID 17 enq: TX - row lock contention 2f007gzsps7a0
正是17号会话产生了等待事件enq: TX - row lock contention ,请求4号锁,那是哪个sql呢,根据sql_id.
SQL> col sql_text for a50
SQL> select sql_text from v$sql where sql_id='2f007gzsps7a0';
SQL_TEXT
insert into t_gyj values(1,'gyj1')
找到sql语句insert into t_gyj values(1,'gyj1');
至目前为止我这里找到了哪条sql被阻塞,产生了行锁的等待,已及通过dump知道是唯一索引SYS_C0011159产生了行锁争用.
SQL> select * from v$lock where sid in (125,17);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
00000003A44602A0 00000003A44602F8 17 TX 327681 1022 0 4 2141 0
00000003A2D3E928 00000003A2D3E9A0 125 TX 327681 1022 6 0 2232 1
问题就在这里当125号做insert操作时在唯一索引(SYS_C0011159)持有了6号排它锁,而17号会话也在做insert操作时要在唯一索引上(SYS_C0011159)
请求4号共享锁,这时被阻塞了.那就是说4号锁与6号锁不兼容(锁的兼容性是锁中很重要的一个概念),请看下图:
为了更清楚分析,可能要去dump索引结构了,是不是有点晕了.确实再dump会很晕.但是为了看清楚,还得dump一把.
先找到索引的root,为了找到root, 我要先找索引段的段头块
SQL> select HEADER_FILE, HEADER_BLOCK from dba_segments where segment_name='SYS_C0011159';
HEADER_FILE HEADER_BLOCK
6 234
嘿嘿!可以得出root就是6号文件的235号块上,继续dump
SQL> alter system dump datafile 6 block 235;
Object id on Block? Y
seg/obj: 0x12475 csc: 0x00.1598de itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x18000e8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0005.001.000003fe 0x00c02d13.00ba.0e ---- 1 fsc 0x0000.00000000
Leaf block dump
header address 18446741324875057764=0xfffffd7ffcb24a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8021=0x1f55
kdxcoavs 7983
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: ------, lock: 2, len=11, data:(6): 01 80 00 e7 00 00
col 0; len 2; (2): c1 02
row#0[8021] flag: ------, lock: 2,这里产生了锁,对应的事务槽ITL=0x02
col 0; len 2; (2): c1 02是指第一列,长度两字节,数据内容是: c1 02,转换如下,第1列就是1,即插入id=1
SQL> select utl_raw.cast_to_number('c102') from dual;
UTL_RAW.CAST_TO_NUMBER('C102')
1
推测唯一索引上会产生的锁是:根块和枝叶块上会产生4号共享锁,叶块上会产生6号排它锁.
从上面dump的结果可以看出由于我这个表据量很少只有一行,所以根块和叶块在同一个块中...由于4号锁与6号锁不兼容,所以导致阻塞...这种情况下一般是应用的问题！
这里4号锁不止是唯一索引会产生,当事务槽ITL空间产生争用时也可能会产生4号锁这个有待于我们下一步验证,等等...,请大师们一起讨论,提些建义和思路,这样我好继续更新4号锁...
