`
897371388
  • 浏览: 530108 次
文章分类
社区版块
存档分类
最新评论

对外键的一点探究

 
阅读更多

熟悉oracle的人都知道，如果外键列上没有创建索引，往往会造成表锁定的问题。下面通过实例来简单探究一下外键是如何影响锁的。

首先来看一下数据库的版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
创建表P_TAB(ID NUMBER)为主表，F_TAB(ID number)为从表，从表上不存在索引。

主表insert 操作

向从表插入条记录，没有提交

SQL> insert into f_tab values(1);

已创建 1 行。
查看锁情况： 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    458775	 1494	       0
       243 TM  SX	     84541	    0	       0
向从表插入记录，会在主表和从表上同时添加TM:SX锁。

向主表插入一条记录，未提交

SQL> insert into p_tab values(3);

已创建 1 行。
查看锁情况： 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 TX  X	    131098	 1686	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84543	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    458775	 1494	       0
       243 TM  SX	     84541	    0	       0
向主表中插入记录，会在主表和从表上添加TM:SX锁，从表的insert（update，delete）不会阻塞主表的添加操作。

回滚所有的操作，测试主表的delete操作。

在从表中插入一条记录

SQL> insert into f_tab values(1);

已创建 1 行。
查看锁的情况 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    655385	11621	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TM  SX	     84541	    0	       0
在主表中删除一条根本不存在的记录 

SQL> delete from p_tab where id=5;
此时用户进程被阻塞，查看锁使用情况： 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TM	    S	     84543	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    655385	11621	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       1
       243 TM  SX	     84541	    0	       0

从表提交后

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0

由此，对主表的删除操作，会在从表上添加TM:S锁，因此其会被从表的dml语句阻塞，同样在操作期间其也会阻塞从表的dml语句，删除操作完成后，主表是否对从表的tm锁，等待提交或者回滚。

对于主表的更新操作，同样会在从表上添加TM:S锁，并且在操作完成后释放对从表的tm锁，等待回滚或者提交。

主表dml操作 操作前对从表加锁 操作后对从表加锁
insert tm:sx tm:sx
delete tm:s 无
update tm:s 无

下面再来看一下，如果从表外键字段包含索引的情况：

首先在从表上插入一条记录，不进行提交

SQL> insert into f_tab values(1);

已创建 1 行。
锁的情况 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    196619	 1657	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84541	    0	       0
       243 TM  SX	     84543	    0	       0
在主表中插入一条记录： 

SQL> insert into p_tab values(3);
锁的情况： 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TX  X	    655371	11652	       0
	 4 TM  SX	     84541	    0	       0
	 4 TM  SX	     84543	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    196619	 1657	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84541	    0	       0
       243 TM  SX	     84543	    0	       0
情况与没有创建索引的情况相同。

在从表中插入一条记录，同时在主表中删除一条根本不存在的记录

SQL> delete from p_tab where id=3;

已删除0行。
主表没有被阻塞，查看锁的使用情况： 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84543	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    327710	 1664	       0
       243 TM  SX	     84541	    0	       0
在主表中，删除一条在从表中存在，但是没有被dml操作的记录 

SQL> delete from p_tab where id=1;
delete from p_tab where id=1
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FTAB) - 已找到子记录
在主表中，删除一条在从表中存在，并且正被从表插入的记录

从表情况：

SQL> insert into f_tab values(2);

已创建 1 行。

SQL> select * from f_tab;

	ID
----------
	 2
	 2
	 1
主表情况： 

SQL> delete from p_tab where id=2;
锁的情况： 

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 TX  X	    655388	11653	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84543	    0	       0
	 4 TX	    S	    327710	 1664	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    327710	 1664	       1
       243 TM  SX	     84541	    0	       0
从这里可以看出，对于从中索引的外键，当主表进行删除操作时，会在从表上添加TM:SM锁，如果此时主表的删除记录对应的外键上具有dml操作，则会因为主表需要获取TX:S锁，而被阻塞。在从表dml提交之后，如果不违反外键约束则正常操作，并在从表上维持TM:Sx锁，如果违法外键约束，则操作失败。更新操作具有相似规律。

总结如下：

主表dml操作 操作前对从表加锁 操作后对从表加锁
insert tm:sx tm:sx
update tm:sx 对应外键的TX:S tm:sx
delete tm:sx 对应外键的TX:S tm:sx

到这里，我想大家肯定已经明白了为什么需要在外键上添加索引。

当然，我们仅仅就部分情况进行了测试，其他的情况下，如delete on cascade等，有兴趣的同学可以自行研究。

从该实例也可以看出，对从表的操作会在主表上添加TM:SX锁，因此可能阻塞主表的ddl语句。













分享到：
| MapReduce
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics