酷酷娱乐网

站内广告

外键没有索引哪些DML操作会被阻塞

时间:2020-09-24 18:30:02   作者:酷酷娱乐网   来源:www.kukuyl.com  
内容摘要:



通过主外键关联的两张表,除非永远不对主表的主键字段作update、delete操作,否......

通过主外键关联的两张表,除非永远不对主表的主键字段作update、delete操作,否则oracle会推荐在子表的外键上建索引。为何要在外键上建索引,不建索引会有产生哪些负面影响,我们就来测试一下


###准备好测试用的表
create table t1010_pk1 (id number,pcol varchar2(1)) tablespace test;
alter table t1010_pk1 add constraint pk_t1010_pk1_id primary key(id) using index tablespace test;
insert into t1010_pk1 values(1,'A');
insert into t1010_pk1 values(2,'B');
insert into t1010_pk1 values(3,'C');
insert into t1010_pk1 values(4,'D');
insert into t1010_pk1 values(5,'E');
commit;


create table t1010_fk1 (id number,fcol varchar2(1)) tablespace test;
alter table t1010_fk1 add constraint fk_t1010_fk1_id foreign key(id) references t1010_pk1(id);
insert into t1010_fk1 values(1,'a');
insert into t1010_fk1 values(2,'a');
commit;


col object_name format a30
set linesize 60
select object_name,object_id from dba_objects where object_name in ('T1010_PK1','T1010_FK1');


OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
T1010_PK1                           18316
T1010_FK1                           18319


select * from t1010_pk1;
        ID P
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E


select * from t1010_fk1;
        ID F
---------- -
         1 a
         2 b


我们聚焦以下四个场景:
1、子表t1010_fk1上的DML执行后不提交,对之后在主表t1010_pk1上发起的DML操作是否会形成阻塞
2、主表t1010_pk1上的DML执行后不提交,对之后在主表t1010_pk1上发起的DML操作是否会形成阻塞
3、子表t1010_fk1上的DML执行后不提交,对之后在子表t1010_fk1上发起的DML操作是否会形成阻塞
4、主表t1010_pk1上的DML执行后不提交,对之后在子表t1010_fk1上发起的DML操作是否会形成阻塞


需要说明的的是, 我们的测试中只关注表级锁,即类型为TM的锁

///////////////////
// 场景1;
// 子表执行insert操作后不提交
// 另一个事务中在主表t1010_pk1上发起update、insert、delete操作
///////////////////
---session 1: sid=162 insert子表
insert into t1010_fk1 values(3,'c');


---session 2: sid=18  update主表
update t1010_pk1 set id=14 where id=4; <---Hung住


---session 3: 查锁
SQL> select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       5313
        18 TM          3          0      18316          0          0         17
        18 TM          0          4      18319          0          0         17   <---申请在子表上持有S锁
       162 AE          4          0        100          0          0       5380
       162 TM          3          0      18316          0          0         22
       162 TM          3          0      18319          0          1         22   <---已在子表上持有RX锁
       162 TX          6          0     393226       1979          0         22


因为S与RX不兼容所以session 2的update被阻塞


---session 2: sid=18  中断update操作后,发起insert主表操作
insert into t1010_pk1 values(6,'F');


1 row created.


---session 3: 查锁
SQL> select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       5889
        18 TM          3          0      18316          0          0          8
        18 TM          3          0      18319          0          0          8   <---已在子表上持有RX锁
        18 TX          6          0     131105       2003          0          8
       162 AE          4          0        100          0          0       5956
       162 TM          3          0      18316          0          0        598
       162 TM          3          0      18319          0          0        598   <---已在子表上持有RX锁
       162 TX          6          0     393226       1979          0        598


因为RX与RX相兼容,所以session 2的insert成功执行


---session 2: sid=18  回滚update操作后,发起delete主表操作
rollback;


delete t1010_pk1 where id=4;   <---Hung住


---session 3: 查锁
SQL> select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       6207
        18 TM          3          0      18316          0          0         14   
        18 TM          0          4      18319          0          0         14   <---申请在子表上持有S锁
       162 AE          4          0        100          0          0       6274
       162 TM          3          0      18316          0          0        916
       162 TM          3          0      18319          0          1        916   <---已在子表上持有RX锁
       162 TX          6          0     393226       1979          0        916


因为S与RX不兼容所以session 2的delete被阻塞


场景1-结论:
子表上的insert操作执行后不提交,会阻塞后续主表上的update(针对主键的update)、delete操作,主表上的insert操作不受影响。


阻塞原因分析:
主表上delete操作、涉及主键的update操作进行前必须要扫描子表,确保主表上的DML操作完成后,子表的外键依然能与主表的主键关联上,凡是任何破坏这一数据完整性的操作都会收到ORA-02292: integrity constraint的提示。当子表的外键没有索引时,对子表扫描就必须先申请持有S锁,S锁与insert子表时已经持有的RX锁是不兼容的,必须等RX锁释放后才能申请到,由此产生了阻塞。


如果对子表进行update(无论是外键还是非外键字段的update)、delete操作后不提交,也会对主表形成相同的阻塞后果,验证过程类似,不再赘述


///////////////////
// 场景2;
// 主表t1010_pk1上的DML执行后不提交
// 另一个事务中在主表t1010_pk1上发起update、insert、delete操作
///////////////////
======> A. 主表发起的是insert操作 <======


---session 1: sid=162: 主表发起insert操作
insert into t1010_pk1 values(6,'F');


1 row created.


---session 2: sid=18: update主表
update t1010_pk1 set id=15 where id=5;   <---Hung住


---session 3: 查锁
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       8050
        18 TM          3          0      18316          0          0          2
        18 TM          0          4      18319          0          0          2   <---子表上申请持有S锁
       162 AE          4          0        100          0          0       8117
       162 TM          3          0      18316          0          0         29
       162 TM          3          0      18319          0          1         29   <---已在子表上持有RX锁
       162 TX          6          0     196614       2011          0         29


因S与RX锁不兼容,所以session 2的update操作阻塞


---session 2: sid=18: insert主表
insert into t1010_pk1 values(7,'G');   


1 row created.


---session 3: 查锁
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       8799
        18 TM          3          0      18316          0          0        117
        18 TM          3          0      18319          0          0        117   <---已在子表上持有RX锁
        18 TX          6          0     393235       1982          0        117
       162 AE          4          0        100          0          0       8866
       162 TM          3          0      18316          0          0        778
       162 TM          3          0      18319          0          0        778   <---已在子表上持有RX锁
       162 TX          6          0     196614       2011          0        778


RX与RX兼容,所以session 2上的insert操作未被阻塞


---session 2: sid=18: delete主表
rollback;


delete t1010_pk1 where id=3;   <---Hung住


---session 3: 查锁
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       8914
        18 TM          3          0      18316          0          0         15
        18 TM          0          4      18319          0          0         15    <---申请在子表上持有S锁

本文章url:https://www.kukuyl.com/new/30000924.html

相关文章

CopyRight 2018 - 2020 http://www.kukuyl.com 酷酷娱乐网 All Rights Reserved .