本文共 9195 字，大约阅读时间需要 30 分钟。
[20160616]IOT与主外键.txt
--许多人都知道主外键引起的阻塞或者死锁,如果主表是IOT呢? IOT实际上一种特殊的索引结构,测试看看.
1.环境:
SCOTT@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productioncreate table p (
id number(10) primary key, v varchar2(10) not null ) organization index;create table c (
id number(10) primary key, p_id number(10) not null references p(id), v varchar2(10) not null );insert into p values (1, '1');
insert into p values (2, '2'); insert into c values (1, 1, '1'); insert into c values (2, 1, '2'); insert into c values (3, 2, '3');create index cpid on c (p_id);
--注意建立了外键索引.2.测试:
--session 1: SCOTT@book> @ &r/spidSID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- -------------------------------------------------- 232 1775 61605 21 53 alter system kill session '232,1775' immediate;SCOTT@book> SELECT * FROM p WHERE id=1 FOR UPDATE;
ID V ---------- ---------- 1 1SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN ------------------------------ 10.4.44155--session 2:
SCOTT@book> @ &r/spidSID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- -------------------------------------------------- 14 1579 61619 25 236 alter system kill session '14,1579' immediate;SCOTT@book> INSERT INTO c (id, p_id, v) VALUES (12, 2, 'not locked');
1 row created. --ok!没有问题. SCOTT@book> INSERT INTO c (id, p_id, v) VALUES (11, 1, 'locked');--挂起!!!
SCOTT@book> @ &r/viewlockSID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- -------------------- 14 1579 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94325 0 SCOTT TABLE P No 00000000851E4548 14 1579 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94327 0 SCOTT TABLE C No 00000000851E4548 14 1579 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction None Share 655364 44155 No 00000000851E4548 14 1579 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 589844 13261 No 00000000851E4548 232 1775 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655364 44155 Yes 232 1775 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94325 0 SCOTT TABLE P No 6 rows selected.--为什么呢?我的理解因为IOT是特殊的索引,也就是P表实际上段不存在,仅仅存在索引段.
SCOTT@book> select * from dba_segments where owner=user and segment_name='P'; no rows selectedSCOTT@book> select * from dba_objects where owner=user and object_name='P';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYP CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME ------ -------------------- ---------- ---------- -------------- ---------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------ SCOTT P 94325 TABLE 2016-06-08 10:26:18 2016-06-08 10:26:18 2016-06-08:10:26:18 VALID N N N 1SCOTT@book> select owner,SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,blocks from dba_segments where owner=user and segment_name like '%94325';
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS ------ -------------------- ----------- ------------ ---------- ---------- SCOTT SYS_IOT_TOP_94325 4 530 65536 8SCOTT@book> alter system checkpoint;
System altered.--也就是数据在索引段.通过bbed观察:
BBED> set dba 4,531
DBA 0x01000213 (16777747 4,531)BBED> p /d ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 10 ub2 kxidslt @70 4 ub4 kxidsqn @72 44155 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~可以发现在索引段出现一个事务槽,与前面的对应. struct ktbituba, 8 bytes @76 ub4 kubadba @76 12583551 ub2 kubaseq @80 8662 ub1 kubarec @82 14 ub2 ktbitflg @84 1 (NONE) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0 ub4 ktbitbas @88 0--当插入INSERT INTO c (id, p_id, v) VALUES (11, 1, 'locked');时,要检查P的主键是否有事务存在,而P表实际上是IOT表,这样lck的
--标识实际是在索引上.如果换成堆表就不存在这个问题了.3.继续做一个特殊的例子:
--drop table cheap purge; --drop table pheap purge;create table pheap (
id number(10) , v varchar2(10) not null );--先建立索引,注意不唯一.并且包含字段v.再建立约束.
create index pk_pheap on scott.pheap (id,v); alter table pheap add constraint pk_headp primary key (id) enable validate;create table cheap (
id number(10) primary key, p_id number(10) not null references pheap(id), v varchar2(10) not null );insert into pheap values (1, '1');
insert into pheap values (2, '2'); insert into cheap values (1, 1, '1'); insert into cheap values (2, 1, '2'); insert into cheap values (3, 2, '3');create index cheappid on cheap (p_id);
--session 1:
SCOTT@book(80,113)> update pheap set id=1 ,v='a' WHERE id=1 ; 1 row updated.SCOTT@book(80,113)> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN ------------------------------ 6.29.4198--session 2:
SCOTT@book(101,57)> INSERT INTO cheap (id, p_id, v) VALUES (12, 2, 'not locked'); 1 row created.SCOTT@book(101,57)> INSERT INTO cheap (id, p_id, v) VALUES (11, 1, 'locked');
--挂起!SYS@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- -------------------- 80 113 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 393245 4198 Yes 80 113 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94505 0 SCOTT TABLE PHEAP No 80 113 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94507 0 SCOTT TABLE CHEAP No 101 57 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655379 44196 No 00000000851E40E8 101 57 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction None Share 393245 4198 No 00000000851E40E8 101 57 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94507 0 SCOTT TABLE CHEAP No 00000000851E40E8 101 57 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 94505 0 SCOTT TABLE PHEAP No 00000000851E40E8 7 rows selected.--因为我修改pheap的索引,导致插入INSERT INTO cheap (id, p_id, v) VALUES (11, 1, 'locked');时阻塞.你可以转储索引看看.
SCOTT@book(80,113)> select owner,SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,blocks from dba_segments where owner=user and segment_name like 'PK_PHEAP';
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS ------ -------------------- ----------- ------------ ---------- ---------- SCOTT PK_PHEAP 4 618 65536 8SYS@book> alter system checkpoint;
System altered.SYS@book> alter system dump datafile 4 block 619;
System altered.Block header dump: 0x0100026b
Object id on Block? Y seg/obj: 0x1712a csc: 0x03.1553b03e itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x1000268 ver: 0x01 opc: 0 inc: 0 exflg: 0Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0006.01d.00001066 0x00c004a0.0739.09 ---- 2 fsc 0x0010.00000000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Leaf block dump =============== header address 140177634691684=0x7f7da61f0a64 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 3 kdxcosdc 0 kdxconro 3 kdxcofbo 42=0x2a kdxcofeo 7990=0x1f36 kdxcoavs 7948 kdxlespl 0 kdxlende 1 kdxlenxt 0=0x0 kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0[8018] flag: ---D--, lock: 2, len=14 col 0; len 2; (2): c1 02 col 1; len 1; (1): 31 col 2; len 6; (6): 01 00 02 44 00 00 row#1[7990] flag: ------, lock: 2, len=14 col 0; len 2; (2): c1 02 col 1; len 1; (1): 61 col 2; len 6; (6): 01 00 02 44 00 00 row#2[8004] flag: ------, lock: 0, len=14 col 0; len 2; (2): c1 03 col 1; len 1; (1): 32 col 2; len 6; (6): 01 00 02 44 00 01 ----- end of leaf block dump ----- End dump data blocks tsn: 4 file#: 4 minblk 619 maxblk 619--注意~,xid与前面的可以对上.全表回滚,然后执行如下:
--session 1:
SCOTT@book(80,113)> update pheap set id=1 WHERE id=1 ; 1 row updated.--//我仅仅修改id,而且前后数组没有变化.注如果执行这样,后面的结果也一样,不会阻塞.
--//SCOTT@book(80,113)> update pheap set id=1 ,v='1' WHERE id=1 ; --//1 row updated.SCOTT@book(80,113)> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN ------------------------------ 10.33.44192--session 2:
SCOTT@book(101,57)> INSERT INTO cheap (id, p_id, v) VALUES (11, 1, 'locked'); 1 row created.--可以发现没有阻塞.
SYS@book> alter system checkpoint;
System altered.SYS@book> alter system dump datafile 4 block 619;
System altered.--检查转储内容:
Block header dump: 0x0100026b Object id on Block? Y seg/obj: 0x1712a csc: 0x03.1553b03e itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x1000268 ver: 0x01 opc: 0 inc: 0 exflg: 0Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x000a.01a.0000ac9d 0x00c00196.21dd.2c C--- 0 scn 0x0003.1553b019--//可以发现索引上没有相关事务.因为前后修改的指一样.这样就很容易理解前面的SELECT * FROM p WHERE id=1 FOR UPDATE;另外的会
--//话插入INSERT INTO c(id, p_id, v) VALUES (11, 1, 'locked');会出现阻塞.就是因为IOT表本身就是索引结构,SELECT * FROM p --WHERE id=1 FOR UPDATE;时,事务发生在相关索引段上.而堆表不会.--当然在应用的表中使用IOT很少,出现主外键也许概率更低.不过理解后面的原理很重要.
转载地址：http://ywibo.baihongyu.com/