同时插入同一行记录，产生阻塞的处理分析(4号锁分析之Unique key的约束)

之前有个客户提出，在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.001598de
Dump 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号锁

 

 

 

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name:    guoyJoe

QQ:      252803295

Email:    [e-mail]

Blog:      http://blog.csdn.net/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

OCM:   
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

DSI&Core Search（QQ群）:127149411