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

最新推荐文章于 2023-06-04 22:42:59 发布

cuiqiong6888

最新推荐文章于 2023-06-04 22:42:59 发布

阅读量212

点赞数

文章标签：数据库

通过主外键关联的两张表，除非永远不对主表的主键字段作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/53956/viewspace-2127265/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-2127265/

cuiqiong6888

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
外键没有索引哪些DML操作会被阻塞

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

扫一扫