熟悉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语句。