对外键的一点探究
来源:互联网 发布:怎样查淘宝号注册时间 编辑:程序博客网 时间:2024/06/06 00:26
熟悉oracle的人都知道,如果外键列上没有创建索引,往往会造成表锁定的问题。下面通过实例来简单探究一下外键是如何影响锁的。
首先来看一下数据库的版本
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE11.2.0.3.0ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL 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 65538511621 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 65538511621 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:sxtm:sxdelete tm:s无updatetm: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 65537111652 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 65538811653 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操作操作前对从表加锁操作后对从表加锁inserttm:sxtm:sxupdatetm:sx 对应外键的TX:Stm:sxdeletetm:sx 对应外键的TX:Stm:sx到这里,我想大家肯定已经明白了为什么需要在外键上添加索引。
当然,我们仅仅就部分情况进行了测试,其他的情况下,如delete on cascade等,有兴趣的同学可以自行研究。
从该实例也可以看出,对从表的操作会在主表上添加TM:SX锁,因此可能阻塞主表的ddl语句。
- 对外键的一点探究
- 对外键属性的注入
- 关于Java字符串的一点探究
- 对HashSet的add()方法的一点探究
- 关于JDBC中ResultSet接口的一点细节探究
- mysql命名行下对外键的增加删除操作
- MYSql对外键约束及字段的一些操作
- 数据导入时对外键约束的处理
- CSDN对外部门的联系方式
- viscose脚本语言的对外宣布
- dll的对外函数接口
- 获取本地的对外ip
- 对外接口暴露的问题
- C++类实例占用的内存结构,及虚函数表的一点探究
- C/C++_关于rand()和srand()的调用方式的一点探究
- 综合 对外包的种种看法
- 服务商对外包方的期望
- Kernel32.dll对外提供的方法
- opencv的cvcopy问题,疑惑!!!!
- 金额大小写转换正则
- 解决导入extjs、jquery 文件时,$使用冲突问题
- 总结计时方式
- C#中两个整数相除得到带小数点的结果
- 对外键的一点探究
- 关于Qt使用SQL模型类数据修改后不能写入数据库的问题
- 自绘CListCtrl类
- 表单 申请权限 卡控
- 苹果新手Mac OS X 使用笔记--------四种应用程序类型
- Beginning ARC in iOS 5 Tutorial Part 1
- 每日一算法:八枚银币
- Excel 多列变一列(亲自实践)
- UML之用例图