[20150304]唯一索引与阻塞.txt
--昨天帮别人定位一个唯一索引导致出现ora-00001的问题,实际上很简单,程序使用max(id)取得最大号,然后插入,这样的结果在业务
--高峰,出现阻塞或者ora-00001错误。我仅仅简单做一下跟踪很容易定位这个问题。
--换一个角度,使用别的方法是否可行呢,自己做一个例子来验证看看。
1.建立测试环境:
SCOTT@test> create table t as select rownum id,cast ( 'test' as varchar2(10)) name from dual connect by levelTable created.
SCOTT@test> create unique index i_t_id on t(id);
Index created.
2.测试:
--session 1,插入不提交:
SCOTT@test> insert into t values (11,'a');
1 row created.
--session 2:
SCOTT@test> insert into t values (11,'b');
--出现阻塞情况,如果会话1提交,会报ora-00001错误。
3.分析:
$ cat viewlock.sql
column sid format 99999
column username format a10
column osuser format a10
column machine format a10
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column lock_id1 format a10
column lock_id2 format a10
column owner format a6
column object_type format a10
column object_name format a20
column block format a5
column lockwait format a20
SELECT se.SID, se.serial#,se.username, se.osuser, se.machine,
DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML(TM)', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
DECODE (lk.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.lmode)
) mode_held,
DECODE (lk.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.request)
) mode_requested,
TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,
DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait
FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+);
SCOTT@test> @viewlock ;
SID SERIAL# USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
401 15 SCOTT oracle11g hisdg DML(TM) Row-X (SX) None 300095 0 SCOTT TABLE T No 00000000BDC92778
597 65 SCOTT oracle11g hisdg DML(TM) Row-X (SX) None 300095 0 SCOTT TABLE T No
597 65 SCOTT oracle11g hisdg Transaction Exclusive None 1835029 71 Yes
401 15 SCOTT oracle11g hisdg Transaction None Share 1835029 71 No 00000000BDC92778
401 15 SCOTT oracle11g hisdg Transaction Exclusive None 851975 1740 No 00000000BDC92778
--注意看阻塞的LOCK_ID1=1835029,LOCK_ID2=71. 这个LOCK_ID1 前16位表示事务的 XIDUSN,后16位表示XIDSLOT。可以做一个简单计算。
SCOTT@test> select trunc(1835029/power(2,16)),mod(1835029,power(2,16)) from dual;
TRUNC(1835029/POWER(2,16)) MOD(1835029,POWER(2,16))
-------------------------- ------------------------
28 21
SCOTT@test> host cat xid.sql
select dbms_transaction.local_transaction_id() x from dual ;
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR from v$transaction;
SCOTT@test> @xid
X
------------------------------
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ----------------
28 21 71 3 11125 10 83 ACTIVE 1 2 1C00150047000000 00000000BBF2E4A0
13 7 1740 3 13079 15 523 ACTIVE 1 1 0D000700CC060000 00000000B81C13B0
--可以发现正好对上。
SCOTT@test> select sid,serial# from v$session where taddr='00000000BBF2E4A0';
SID SERIAL#
------ ----------
597 65
--这些信息都正好对上。
4.然后做一个转储看看:
SCOTT@test> alter system dump datafile 3 block 11125;
System altered.
..
*-----------------------------
* Rec #0xa slt: 0x15 objn: 300096(0x00049440) objd: 300096 tblspc: 4(0x00000004)
* Layer: 10 (Index) opc: 22 rci 0x09
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: 0x0002.cb8eb5e2
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=42 indexid=0x1000722 block=0x01000723
(kdxlpu): purge leaf row
key :(3): 02 c1 0c
SCOTT@test> select name from sys.undo$ where us#=28;
NAME
---------------------
_SYSSMU28_3223209608$
SCOTT@test> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU28_3223209608$' XID 28 21 71;
System altered.
********************************************************************************
Undo Segment: _SYSSMU28_3223209608$ (28)
xid: 0x001c.015.00000047
Low Blk : (0, 0)
High Blk : (2, 127)
Object Id : ALL
Layer : ALL
Opcode : ALL
Level : 2
********************************************************************************
UNDO BLK: Extent: 2 Block: 117 dba (file#, block#): 3,0x00002b75
xid: 0x001c.015.00000047 seq: 0x53 cnt: 0xa irb: 0xa icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f34 0x02 0x1e8c 0x03 0x1d90 0x04 0x1d28 0x05 0x1cd0
0x06 0x1be4 0x07 0x1b3c 0x08 0x1a5c 0x09 0x19d4 0x0a 0x1978
*-----------------------------
* Rec #0xa slt: 0x15 objn: 300096(0x00049440) objd: 300096 tblspc: 4(0x00000004)
* Layer: 10 (Index) opc: 22 rci 0x09
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: 0x0002.cb8eb5e2
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=42 indexid=0x1000722 block=0x01000723
(kdxlpu): purge leaf row
key :(3): 02 c1 0c
*-----------------------------
* Rec #0x9 slt: 0x15 objn: 300095(0x0004943f) objd: 300095 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c02b75.0053.08 ctl max scn: 0x0002.cb8e8911 prv tx scn: 0x0002.cb8e8914
txn start scn: scn: 0x0002.cb8eb610 logon user: 84
prev brb: 12594031 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100071f hdba: 0x0100071a
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
+++++++++++ Next block not in extent map - rollback segment has been shrunk.
+ WARNING + Block dba (file#, block#): 0,0x00000000
+++++++++++
*************************************
Total undo blocks scanned = 1
Total undo records scanned = 2
Total undo blocks dumped = 1
Total undo records dumped = 2
##Total warnings issued = 1
*************************************
--插入索引的objd: 300096,indexid=0x1000722 block=0x01000723
SCOTT@test> select object_name from dba_objects where owner=user and data_object_id=300096;
OBJECT_NAME
--------------------
I_T_ID
SCOTT@test> set verify off
SCOTT@test> @dfb 1000723
RFILE# BLOCK#
---------- ----------
4 1827
TEXT
-----------------------------------------
alter system dump datafile 4 block 1827 ;
SCOTT@test> select * from dba_extents where owner=user and segment_name='I_T_ID';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------- ------------- ----------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT I_T_ID INDEX USERS 0 4 1824 65536 8 4
SCOTT@test> select HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from dba_segments where owner=user and segment_name='I_T_ID';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
----------- ------------ ---------- ---------- ---------- -------------- -----------
4 1826 65536 8 1 65536 1048576
--正好是块头的下1块,1827.当然这样定位有点麻烦,了解许多知识点还是很用帮助的。