[20160616]IOT与主外键.txt

[20160616]IOT与主外键.txt

https://ilmarkerm.eu/blog/2016/06/interesting-difference-in-foreign-key-locking-behavior-between-heap-and-index-organized-tables

--许多人都知道主外键引起的阻塞或者死锁,如果主表是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 Production

create 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/spid

       SID    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 1

SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.4.44155

--session 2:
SCOTT@book> @ &r/spid

       SID    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/viewlock

   SID    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 selected

SCOTT@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          1

SCOTT@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          8

SCOTT@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          8

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: 0

Itl           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: 0

Itl           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很少,出现主外键也许概率更低.不过理解后面的原理很重要.

时间： 2024-10-23 20:08:32

[20160616]IOT与主外键.txt的相关文章

[20151231]主外键与空表.txt

[20151231]主外键与空表.txt --主外键的测试例子很多,今天做一个特别的,外部键表为空,也会出现阻塞吗?测试看看. 1.建立环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------

[20160719]主外键与延迟约束2.txt

[20160719]主外键与延迟约束2.txt --前几天遇到的问题,因为开发没有在2个存在主外键上的表上建立约束,导致主表记录删除了,而外表数据还在. --主要开发有需求要删除主表的记录,由于条件写错,导致以上情况出现.实际上oracle支持延迟约束,只有提交的时候才会检查. --自己通过例子说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ---------------

[20160716]主外键与延迟约束.txt

[20160716]主外键与延迟约束.txt --前几天遇到的问题,因为开发没有在2个存在主外键上的表上建立约束,导致主表记录删除了,而外表数据还在. --主要开发有需求要删除主表的记录,由于条件写错,导致以上情况出现.实际上oracle支持延迟约束,只有提交的时候才会检查. --自己通过例子说明: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING           VERSION    BANNER                                

[20160704]NULL与主外键问题.txt

[20160704]NULL与主外键问题.txt --主外键的问题主要出现在阻塞等情况,有许多极端dba认为应该取消这个约束.当然从使用的角度出发有总比没有好.只是不要过度滥用. --我这里遇到一个问题,开发指出主外键"失效",我仔细检查发现建立的主表索引是唯一,而非主键,这样可以插入NULL,导致所谓的"失效". --我开始意味是因为oracle索引不记录NULL而导致的(主键仅仅一个字段).实际上我的测试还是有点意外: 1.环境: SCOTT@book>

[20151231]主外键与空表(12c).txt

[20151231]主外键与空表(12c).txt --主外键的测试例子很多,今天做一个特别的,外部键表为空,也会出现阻塞吗?测试看看. 1.建立环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID --------------

[20121020]主外键约束以及NULL问题.txt

[20121020]主外键约束以及NULL问题.txt 主外键约束可以一定程度保证数据完整性,但是如果外键输入的是NULL,情况会如何呢? SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 6

[20140210]主外键和阻塞.txt

[20140210]主外键和阻塞.txt 许多人都知道如果几个表之间存在主外键关系的情况下,许多情况下会出现阻塞情况. 具体的例子还很多,当然如果我觉得最常见如果你不修改主外键值,外键的索引多数情况下可以不建. 而且有些外键的索引建立有点多余的. 今天我看了一篇blog,链接如下: http://blog.yavor.info/?p=564&lang=en 给出的例子很奇特,就是这个问题在11g下会出现阻塞,而10g下不会.12c下我也做了测试,也不会, 自己重复测试做一个记录: 1.12c的情

城市，区县，地址，没有主外键区分，没有父级ID，所以数据在一个表如何实现级联查询？

问题描述 城市,区县,地址,没有主外键区分,没有父级ID,所以数据在一个表如何实现级联查询? 城市,区县,地址,没有主外键区分,没有父级ID,所以数据在一个表如何实现级联查询? 解决方案 一般这样的情况都是三张表.如果你要放在一张表里面,那么你肯定会有一个父级ID的呀.不然怎么区分,怎么级联? 解决方案二: 都在一个表,我也很纳闷,接手的项目数据库是这样,表示压力好大 解决方案三: 一般不是全在一张表,用level和parentId来区分关联:就是三张表,parentId关联. 没有parent

在PowerDesigner中设计物理模型1——表和主外键

原文:在PowerDesigner中设计物理模型1--表和主外键 在PD中建立物理模型由以下几种办法: 直接新建物理模型. 设计好概念模型,然后由概念模型生成物理模型. 设计好逻辑模型,然后由逻辑模型生成物理模型. 使用逆向工程的方法,连接到现有的数据库,由数据库生成物理模型. 物理模型能够直观的反应出当前数据库的结构.在数据库中的表.视图.存储过程等数据库对象都可以在物理模型中进行设计.由于物理模型和数据库的一致性,接下来以数据库对象和物理模型对象的对应来一一介绍: 表 新建物理模型时需要指定