ITPub博客

首页 > 数据库 > Oracle > 外键没有索引哪些DML操作会被阻塞

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

Oracle 作者:n-lauren 时间:2019-05-18 10:39:51 0 删除 编辑

通过主外键关联的两张表,除非永远不对主表的主键字段作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锁
       162 AE          4          0        100          0          0       8981
       162 TM          3          0      18316          0          0        893
       162 TM          3          0      18319          0          1        893    <---已在子表上持有RX锁
       162 TX          6          0     196614       2011          0        893


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


======> B. 主表发起的是update操作 <======
---session 1: sid=162: 主表发起update操作
rollback;


update t1010_pk1 set id=15 where id=5;


1 row created.


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


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       9052
        18 TM          3          0      18316          0          0         31   
        18 TX          6          0      65542       1515          0         31
       162 AE          4          0        100          0          0       9119
       162 TM          3          0      18316          0          0         43
       162 TX          6          0     262150       1501          0         43


子表上没有发现表级锁
       
---session 2: sid=18: insert主表
rollback;


insert into t1010_pk1 values(7,'G'); 


---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       9254
        18 TM          3          0      18316          0          0         11
        18 TM          3          0      18319          0          0         11   <---已在子表上持有RX锁
        18 TX          6          0     393249       1982          0         11
       162 AE          4          0        100          0          0       9321
       162 TM          3          0      18316          0          0        245
       162 TX          6          0     262150       1501          0        245


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


delete t1010_pk1 where id=3; 


---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       9449
        18 TM          3          0      18316          0          0          3
        18 TX          6          0     196634       2010          0          3
       162 AE          4          0        100          0          0       9516
       162 TM          3          0      18316          0          0        440
       162 TX          6          0     262150       1501          0        440


子表上没有发现表级锁       


======> C. 主表发起的是delete操作 <======
结果同B,不再赘述


场景2-结论:
主表发起insert后不提交,在另一个事务中对主表发起的update、delete操作会被阻塞、insert操作不会被阻塞;
主表发起update、delete后不提交,在另一个事务中对主表发起的DML操作不会被阻塞;


阻塞原因分析:
主表insert操作执行时会在子表上持有RX锁,另一个事务中发起针对主表的update、delete操作会申请子表上的S锁,S锁与RX锁不兼容,故导致阻塞。
如果主表先发起update、delete操作,接着在另一个事务中对主表发起DML操作虽然不会被阻塞。但并不意味着先发起的update、delete操作不会在子表上加锁,只不过这个锁持有的时间较短,我们比较难观测到,下面的补充测试可以证明这点:


---session 1: sid=187:主表发起update
update t1010_pk1 set id=14 where id=4;


---session 2: sid=178:主表发起update
update t1010_pk1 set id=15 where id=5;


---session 3: 锁的情况显示只在父表上持有RX锁,子表上没有任何锁
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (178,187) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       178 AE          4          0        100          0          0        174
       178 TM          3          0      18316          0          0         11
       178 TX          6          0     327712       2037          0         11
       187 AE          4          0        100          0          0       1893
       187 TM          3          0      18316          0          0         17
       187 TX          6          0     196639       2034          0         17


---session 2:回滚
rollback;


---session 4:以exclusive模式锁住子表
lock table t1010_fk1 in exclusive mode;


Table(s) Locked.


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


---session 3: 锁的情况显示只在父表上持有RX锁,子表上没有任何锁
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (178,187) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       178 AE          4          0        100          0          0        738
       178 TM          3          0      18316          0          0         22
       178 TM          0          4      18319          0          0         22  <---正在申请子表上的S锁
       187 AE          4          0        100          0          0       2457
       187 TM          3          0      18316          0          0        581
       187 TX          6          0     196639       2034          0        581


---session 4:回滚
rollback;


---session 2:   


1 row updated.    <---更新成功


---session 3: 锁信息显示里关于子表的锁又消失了
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       178 AE          4          0        100          0          0        977
       178 TM          3          0      18316          0          0        261
       178 TX          6          0     589850       2034          0         30
       187 AE          4          0        100          0          0       2696
       187 TM          3          0      18316          0          0        820
       187 TX          6          0     196639       2034          0        820


以上可以证明在update主表主键字段的过程中会短暂持有子表上的S锁,update成功后随即释放


///////////////////
// 场景3;
// 子表t1010_fk1上的DML执行后不提交
// 另一个事务中在子表t1010_fk1上发起的DML操作是否会形成阻塞
///////////////////              
---session 1: sid=162:子表发起insert操作
insert into t1010_fk1 values(3,'c');


1 row created.


---session 2: sid=18: 子表执行insert操作
insert into t1010_fk1 values(4,'d');        


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      16428
        18 TM          3          0      18316          0          0         46   <---已持有主表上的RX锁
        18 TM          3          0      18319          0          0         46   
        18 TX          6          0     458762       1533          0         46
       162 AE          4          0        100          0          0      16495
       162 TM          3          0      18316          0          0        741   <---已持有主表上的RX锁
       162 TM          3          0      18319          0          0        741   
       162 TX          6          0     655382       1497          0        741 


---session 2: sid=18: 子表执行delete操作
rollback;


delete t1010_fk1 where id=1;


1 row deleted.


---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      16598
        18 TM          3          0      18316          0          0         17   <---已持有主表上的RX锁
        18 TM          3          0      18319          0          0         17
        18 TX          6          0     589834       2011          0         17
       162 AE          4          0        100          0          0      16665
       162 TM          3          0      18316          0          0        911   <---已持有主表上的RX锁
       162 TM          3          0      18319          0          0        911
       162 TX          6          0     655382       1497          0        911


---session 2: sid=18: 子表执行update操作
rollback;


update t1010_fk1 set id=4 where id=2;


1 row updated.


---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      16880
        18 TM          3          0      18316          0          0         17   <---已持有主表上的RX锁
        18 TM          3          0      18319          0          0         17
        18 TX          6          0     458761       1539          0         17
       162 AE          4          0        100          0          0      16947
       162 TM          3          0      18316          0          0       1193   <---已持有主表上的RX锁
       162 TM          3          0      18319          0          0       1193
       162 TX          6          0     655382       1497          0       1193


场景3-结论:
子表上的insert操作执行后不提交,不会阻塞另一个事务中对子表的insert、update、delete操作
子表上若先进行的是update、delete操作,也不会对另一个事务中子表上的DML操作形成阻塞,验证过程类似,不再赘述


///////////////////
// 场景4;
// 主表t1010_fk1上的DML执行后不提交
// 另一个事务中在子表t1010_fk1上发起的DML操作是否会形成阻塞
///////////////////       
---session 1: sid=27:主表发起update操作
update t1010_pk1 set id=15 where id=5;


---session 2: sid=23:子表发起insert操作
insert into t1010_fk1 values(3,'c');


---session 3: 观察锁情况
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       3915
        27 AE          4          0        100          0          0       3923
        27 TM          3          0      18316          0          0         18
        23 TM          3          0      18316          0          0         12  <---已经持有主表上的RX锁
        23 TM          3          0      18319          0          0         12  <---已经持有子表上的RX锁
        27 TX          6          0     655374       1544          0         18
        23 TX          6          0     458753       1584          0         12       


---session 2: sid=23:子表发起update操作
rollback;


update t1010_fk1 set id=1 where id=2;


---session 3: 观察锁情况
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       4407
        27 AE          4          0        100          0          0       4415
        27 TM          3          0      18316          0          0        510   
        23 TM          3          0      18316          0          0          2   <---已经持有主表上的RX锁
        23 TM          3          0      18319          0          0          2   <---已经持有子表上的RX锁
        27 TX          6          0     655374       1544          0        510
        23 TX          6          0     262160       1577          0          2


---session 2: sid=23:子表发起delete操作
rollback;


delete t1010_fk1 where id=2;


---session 3: 观察锁情况        
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
      SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       4486
        27 AE          4          0        100          0          0       4494
        27 TM          3          0      18316          0          0        589  
        23 TM          3          0      18316          0          0          2  <---已经持有主表上的RX锁
        23 TM          3          0      18319          0          0          2  <---已经持有子表上的RX锁
        27 TX          6          0     655374       1544          0        589
        23 TX          6          0     393216       2099          0          2


场景4-结论:
主表上的update操作执行后不提交,不会阻塞另一个事务中对子表的DML操作
子表上若先进行的是insert、delete操作,也不会对另一个事务中子表上的DML操作形成阻塞,验证过程类似,不再赘述




外键没有索引的情况,主表与子表上可能产生的锁总结为下面两张表:

表一: DML 操作持有的锁类型对照表:


Emp

Dept

Insert into t1010_fk1

RX

Null

Update t1010_fk1 (fcol)

RX

Null

Update t1010_fk1 (id)

RX

RX

Delete from t1010_fk1

RX

RX

Insert into t1010_pk1

RX

RX

Update t1010_pk1 (id)

S( 瞬间持有 )

RX

Update t1010_pk1 (pcol)

null

RX

Delete from t1010_pk1

S( 瞬间持有 )

RX


Emp

Dept

Insert into t1010_fk1

RX

Null

Update t1010_fk1 (fcol)

RX

Null

Update t1010_fk1 (id)

RX

RX

Delete from t1010_fk1

RX

RX

Insert into t1010_pk1

RX

RX

Update t1010_pk1 (id)

S( 瞬间持有 )

RX

Update t1010_pk1 (pcol)

null

RX

Delete from t1010_pk1

S( 瞬间持有 )

RX






































表二: 主子表各类DML操作互斥关系 对照表(红色的单元格会产生阻塞):





外键字段建了索引之后,子表上的S锁会被RX锁替代,RX与RX之间是完全兼容的,引用官方文档的说法,避免了在子表上加全表锁,转而在外键索引上申请了行级锁
---session 1:sid=27:update子表
create index fk_t1010_fk1_id on t1010_fk1(id) tablespace test;
update t1010_fk1 set id=3 where id=2;


1 row updated.


---session 2:sid=23: update主表
update t1010_pk1 set id=15 where id=5;


1 row updated.


---session 3: 观察锁信息
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       6883
        27 AE          4          0        100          0          0       6891
        27 TM          3          0      18316          0          0         25   
        27 TM          3          0      18319          0          0         25
        23 TM          3          0      18316          0          0         21
        23 TM          3          0      18319          0          0         21   <----子表上持有RX锁
        27 TX          6          0     393224       2098          0         25
        23 TX          6          0     262156       1577          0         21

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22990797/viewspace-2644833/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Servlet连接Oracle
请登录后发表评论 登录
全部评论

注册时间:2011-09-26

  • 博文量
    143
  • 访问量
    279415