同时插入同一行记录，产生阻塞的处理分析(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          000000003A44601D0 00000003A4460228         17 AE        100          0          4          0        595          000000003A44602A0 00000003A44602F8         17 TX     327681       1022          0          4        474          0FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0         17 TM      74868          0          3          0        474          0FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0        125 TM      74868          0          3          0        565          000000003A2CFF908 00000003A2CFF980         17 TX     458765        764          6          0        474          000000003A2D3E928 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 70486.到这里为止我们已把问题解决了,但这不是我们的目的,我们继续为分析一下4号锁的原凶.大家知道v$lock.LMODE或v$lock.REQUEST有几种模式吗?马上打开官方文档Books->REF->Reference->搜索v$lockLock mode in which the session holds the lock:0 - none1 - 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          000000003A44601D0 00000003A4460228         17 AE        100          0          4          0       2262          000000003A44602A0 00000003A44602F8         17 TX     327681       1022          0          4       2141          0FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0         17 TM      74868          0          3          0       2141          0FFFFFD7FFC9F2040 FFFFFD7FFC9F20A0        125 TM      74868          0          3          0       2232          000000003A2CFF908 00000003A2CFF980         17 TX     458765        764          6          0       2141          000000003A2D3E928 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:  NoTemp Object:  NoTablespace Undo:  Nordba: 0x00000000*-----------------------------index undo for leaf key operationsKTB Redoop: 0x04  ver: 0x01 compat bit: 4 (post-11) padding: 1op: 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 rowkey :(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 1000SQL> 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 a50SQL> 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          000000003A2D3E928 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号块上,继续dumpSQL>  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/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0005.001.000003fe  0x00c02d13.00ba.0e  ----    1  fsc 0x0000.00000000Leaf block dump===============header address 18446741324875057764=0xfffffd7ffcb24a64kdxcolev 0KDXCOLEV Flags = - - -kdxcolok 0kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Ykdxconco 1kdxcosdc 0kdxconro 1kdxcofbo 38=0x26kdxcofeo 8021=0x1f55kdxcoavs 7983kdxlespl 0kdxlende 0kdxlenxt 0=0x0kdxleprv 0=0x0kdxledsz 6kdxlebksz 8032row#0[8021] flag: ------, lock: 2, len=11, data:(6):  01 80 00 e7 00 00col 0; len 2; (2):  c1 02row#0[8021] flag: ------, lock: 2,这里产生了锁,对应的事务槽ITL=0x02col 0; len 2; (2):  c1 02是指第一列,长度两字节,数据内容是: c1 02,转换如下,第1列就是1,即插入id=1SQL> 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:    guoyJoeQQ:      252803295Email:    [e-mail]:     http://blog.csdn.net/guoyJoeITPUB:  http://www.itpub.net/space-uid-28460966.htmlOCM:   http://education.oracle.com/education/otn/YGuo.HTM_____________________________________________________________加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!答案在：http://blog.csdn.net/guoyjoe/article/details/8624392DSI&Core Search（QQ群）:127149411