之前有個客戶提出，在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号會話上插入同樣的一條記錄,這時被阻塞了.
這裡就出現了上面提到的同時插入同一行記錄，産生阻塞的現象,現象出現我們如何判斷分析呢?
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);
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> 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産生了行鎖争用.
問題就在這裡當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号鎖...
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)