[20160616]IOT与主外键.txt-低调大师

[20160616]IOT与主外键.txt

2016-06-15 680

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

微信关注我们

原文链接：https://yq.aliyun.com/articles/279750

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

史上最全的物联网资料

在网络技术飞速发展的今天，物联网（InternetofThings，IoT）概念再次备受关注，更成为引爆IT变革的新导火索。物联网是新一代信息网络技术的高度集成和综合运用，是新一轮产业革命的重要方向和推动力量，将是下一个推动世界高速发展的“重要生产力”，是继通信网之后的另一个万亿级市场。物联网不仅仅提供了传感器的连接，其本身也具有智能处理的能力，能够对物体实施智能控制。物联网将传感器和智能处理相结合，利用云计算、模式识别等各种智能技术，扩充其应用领域。从传感器获得的海量信息中分析、加工和处理出有意义的数据，以适应不同用户的不同需求，发现新的应用领域和应用模式。为了让开发者进一步的了解、熟悉物联网,了解物联网的最新知识；掌握物联网的最新技术，真正地实现万物互联。云栖社区特意组织翻译了《GitHub Awesome IoT》资源，其中不仅涵盖物联网开发测试中软硬件资源，还包括相关的协议和标准，以及与物联网相关的书籍、论文等资源。目录列表硬件软件操作系统编程语言框架中间件库和工具其他协议和网络技术标准和联盟资源书籍文章论文硬件 ...

2016-06-15

619

做嵌入式开发，UART几乎是必不可少的，调试串口、GPS、GPRS、Bluetooth等模块很多都是用的UART接口。时下火热的IoT也不乏UART的身影，串口的BLE、WIFI、Zigbee、Lora等等模块，一堆一堆。前一篇提到的NUC972竟然内置了11个UART，也就可以理解了。虽然之前已基于串口做了很多工具软件，如S3C2410的烧录助手、WinCE串口调试助手、GPRS拨号助手、蓝牙模块调试助手等等，但现在搞IoT再弄串口，还是有必要整理一下这方面的知识，毕竟IoT跟教学实验设备甚至车载导航有很大不同，是不能够随便重启的。IoT产品可靠性要求极高，避免自己掉到坑里或者挖坑害了战友，花点时间整理UART相关知识是非常值得的。本篇转载的文章主要介绍了UART的基本特性，个人感觉写的非常好，具体如下。 Introduction to the standard serial bus UART (Universal Asynchronous Receiver/Transmitter) is one of the earliest modes of communication appl...

2016-06-25

581

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。