为什么子表外键列需要建立索引?（中）

 

上篇（http://space.itpub.net/17203031/viewspace-701832）中，我们讨论了基本外键连带锁结构。

 

3、无索引状态下，引起的并发阻塞问题

 

这里面就要用到我们准备的两个会话实验环境了。为了实现完全的目的，我们设计比较完全的实验策略。分别在会话1中进行主表、子表dml操作，同时在会话2中主子表的DML操作。注意，本实验并不涉及外键约束的实验，也不涉及到相同数据行、主键修改内容。我们只研究并发操作问题。下面是我们发现错误和问题的实验场景。

 

说明：这种组合实验方式大约有78种，由于篇幅的原因，这里只进行一次成功无问题实验和展示错误问题实验。具体78种试验的实验报告可以从笔者下载文件列表中下载。无索引状态下，引起问题的场景有四个。

 

ü        无问题场景

 

会话1进行主表的插入操作，同时会话2也进行主表插入操作。

 

 

--sid1

SQL> insert into master values (4,'dk');

1 row inserted

 

--sid2

SQL> insert into master values (6,null);

1 row inserted

 

此时锁情况如下：

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          0

        18 TM        75189          0          3          0          0

        18 TM        75191          0          3          0          0

       135 TX       589834       1174          6          0          0

        18 TX       524305       1418          6          0          0

 

6 rows selected

 

 

两个会话135和18都会对主子表加lmode=3的共享锁，但是由于共享锁特性，相互不阻塞。

 

ü        问题实验场景1

 

sid1进行主表insert操作，同时sid2进行主表的delete操作。

 

--sid1

SQL> insert into master values (4,'dk');

1 row inserted

 

--sid2

SQL> delete master where id=3;

 

delete master where id=3

 

ORA-01013: 用户请求取消当前的操作

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          1

        18 TM        75189          0          3          0          0

        18 TM        75191          0          0          4          0

       135 TX       589834       1174          6          0          0

        18 TX       196622       1220          6          0          0

 

6 rows selected

 

 

此时，不涉及到对detail表的操作。但是sid2的delete操作却被阻塞。我们从锁lock视图中，可以看到情况：

 

会话1（sid=135）在insert主表的时候，在主子表上加入了lmode=3的共享锁。同时加入了对数据行的独占锁。当会话2进行操作的时候，给父表（object=75189）加入了共享锁，与会话1没有冲突。但是，会话2会尝试对子表detail表加入lmode=4的高级别锁。这个与会话1加入的共享锁冲突，所以被阻塞。

 

此时如果有其他会话还要进行此类型的操作，也是会被阻塞。

 

ü        问题实验场景2

 

当sid1对子表进行insert操作，sid2会话对主表进行delete操作的时候，会引起阻塞。

 

--sid1操作

SQL> insert into detail values (4,2,'d');

1 row inserted

 

--sid2操作

SQL> delete master where id=3;

 

delete master where id=3

 

ORA-01013: 用户请求取消当前的操作

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          1

        18 TM        75189          0          3          0          0

        18 TM        75191          0          0          4          0

       135 TX       262170       1001          6          0          0

 

 

与上面的实验情况相似，sid2在进行主表删除的时候，额外希望在子表上添加lmode=4级别的锁结构。与sid1原先加入的共享锁不兼容，所以被阻塞。

 

ü        问题实验场景3

 

sid1进行子表update操作的时候，sid2尝试对主表进行删除操作，引起阻塞。

 

--sid1操作

SQL> update detail set details='dkl' where did=3;

1 row updated

 

--sid2操作

SQL> delete master where id=4;

delete master where id=4

 

ORA-01013: 用户请求取消当前的操作

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

       135 TM        75191          0          3          0          1

        18 TM        75189          0          3          0          0

        18 TM        75191          0          0          4          0

       135 TX       393236       1219          6          0          0

 

 

sid1（sid=135）进行子表修改的时候，只是对子表进行共享级别的锁定（共享锁）。sid2进行delete主表记录的时候，在主表上加共享锁，又尝试在子表上加lmode=4的级别锁顶，这样引起了阻塞。

 

ü        问题实验场景4

 

sid1进行子表删除操作，sid2进行主表delete操作，同样引起阻塞问题。

 

 

--sid1

SQL> delete detail where did=3;

1 row deleted

 

--sid2

SQL> delete master where id=3;

 

delete master where id=3

 

ORA-01013: 用户请求取消当前的操作

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          1

        18 TM        75189          0          3          0          0

        18 TM        75191          0          0          4          0

       135 TX       327713       1218          6          0          0

 

 

会话1（sid=135）进行子表删除的时候，对主子表都加入了lmode=3的共享锁结构。会话2在进行delete主表操作的时候，尝试在子表上加入lmode=4级别锁，结果被阻塞。

 

观察总结四个问题场景，我们发现了问题的所在：对主表删除delete操作时，Oracle的行为问题。

 

当Oracle对主表进行delete操作的时候，在子表索引列上没有索引时，会检查子表当前的锁情况，如果没有其他锁结构，就尝试加入一个lmode=3的锁结构。如果有其他锁，就升级请求锁的级别，引起操作阻塞。

 

我们知道，在Oracle并发情况下，多会话情况众多，而且有外键的情况会引起连带的主子表同时锁定的场景。所以，在没有外键列索引的时候，对表的并发DML操作非常容易引起阻塞现象，进而影响系统整体的并行度。

 

 

下面，我们尝试加索引之后，情况如何。

 

PS：系列总索引：

 

《为什么子表外键列需要建立索引?（上）》

http://space.itpub.net/17203031/viewspace-701832

 

《为什么子表外键列需要建立索引?（中）》

http://space.itpub.net/17203031/viewspace-701833

 

《为什么子表外键列需要建立索引?（下）》

http://space.itpub.net/17203031/viewspace-701834

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

转载于:http://blog.itpub.net/17203031/viewspace-701833/