关于子表外键未键索引时，修改主表主键导致子表锁定问题疑问

xubin_xin

最近阅读oracle官方文档关于锁机制中有一下一段话
Locks and Unindexed Foreign Keys

When both of the following conditions are true, the database acquires a full table lock on the child table:

• No index exists on the foreign key column of the child table.
• A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.
  
  
  按照翻译理解，当子表的外键没有创建索引并且修改主表的主键（删除一行，或者修改主键属性），或者merge行到主表中都会引起子表锁定
  于是我就自己做了一个试验，
  创建了
  p（id nubmer primary key,pname varchar2(20)）（主表）
  c(cid number reference to p(id),cname varchar2(20))（子表）
  并分别按照正常规则插入部分数据
  
  
  试验开始：
  我想通过修改主表的主键来达到锁定字表的目的
  当我修改主表的时候得到的违法外键约束提示
  SQL> update p set id=20 where id=2;update p set id=20 where id=2*ERROR at line 1:ORA-02292: integrity constraint (HR.FK_CID) violated - child record found出现以上结果也是可以理解的
  但是疑问就是官方文档中出现的情况到底时在什么情况下出现的，希望各位高手帮忙解答
  
  

xubin_xin

没有人有相同的疑问吗？

Locks and Unindexed Foreign Keys
When both of the following conditions are true, the database acquires a full table lock on the child table:
No index exists on the foreign key column of the child table.
A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.
Suppose that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id. Figure 9-3 shows a session modifying the primary key attributes of department 60 in the departments table.
Figure 9-3 Locking Mechanisms with Unindexed Foreign Key

Description of "Figure 9-3 Locking Mechanisms with Unindexed Foreign Key"

In Figure 9-3, the database acquires a full table lock on employees during the primary key modification of department 60. This lock enables other sessions to query but not update the employees table. For example, employee phone numbers cannot be updated. The table lock on employees releases immediately after the primary key modification on the departments table completes. If multiple rows in departments undergo primary key modifications, then a table lock on employees is obtained and released once for each row that is modified in departments.

TX__wangbeng

http://www.itpub.net/thread-1800578-1-1.html，更新父表，确实锁住了子表。可以参考这个模拟
Inserts into the parent table do not acquire table locks on the child table.
但是这句好想插入也锁住了子表。

xubin_xin

---------------------------------------------------------------------------------------------
实验环境：
---------------------------------------------------------------------------------------------
create table emp(emp_id number primary key,dept_id number);   --子表
create table dept (dept_id number primary key);               --主表
alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);
insert into dept values(10);
insert into dept values(20);
insert into dept values(30);
insert into emp values(1,10);
insert into emp values(2,20);
insert into emp values(3,30);
commit;
----------------------------------------------------------------------------------------------

session 1
--HR USER--
select distinct(sid) from v$mystat;    14
session 2
select distinct(sid) from v$mystat;    68

----------------------------------------------------------------------------------------------
测试一（子表外键未建索引）
session1
delete from emp where dept_id=10;   --with no commit


session2
delete from dept where dept_id=30;  --status hang


查看当前锁
select * from v$lock where sid in(14,68)and type in('TX','TM');





分析：sid为 14的回话（session 1）同时对78052 78054 两张表加了LMODE=3的SX锁，block=1
说明阻碍了一个回话进程

session1做commit  锁定解除
session2提示ora-02292 违反外键约束条件
------------------------------------------------------------------------------------------
测试二（子表外键创建索引）
create index exp_fk1 on emp(dept_id);
delete from emp where dept_id=20;  --with no commit
delete from dept where dept_id=10;  --删除成功




____________________________________以上是我的实验内容___________________________________
按照实验结果来看，子表外键未建索引情况下确实引起的主表的锁定，但是为什么只是一个LMODE=3的锁呢？这个锁应该不会有这么大的能力
第二次实验，子表外键创建索引，这次主表操作正常，但是从截图来看仍然有LMODE=3的锁，所以我判定锁住主表的关键锁不是此锁

求解释？？到底是那个锁？我认为更像是一个表级别独占锁，但是臣妾找不到啊！！

developer2002

其实官方文档解释的已经非常清楚。
1.在外键没有索引的情况下，修改主表的主键（delete or update），会在子表上加表锁。
2.在存在外键索引的情况下，修改主表的主键（delete or update），不会在子表上加表锁，此时允许你在子表上进行dml，实际是锁定在索引的叶子节点上。
所以，实验时，你只需要在主表上进行对主键的操作，然后去看 v$lock视图即可，无须另起一个会话在子表上进行操作。oracle官方文档表达的也是这个意思。仅是在加与不加索引，对主表主键操作的情况下，观察此时在子表上的锁情况，而没要求你一定要在子表上此时在进行操作。

xubin_xin

developer2002 发表于 2013-7-26 16:42
其实官方文档解释的已经非常清楚。
1.在外键没有索引的情况下，修改主表的主键（delete or update），会在 ...

言之有理，我今天也翻阅了大量的细力度锁的文档，之前基础太薄弱

xubin_xin

developer2002 发表于 2013-7-26 16:42
其实官方文档解释的已经非常清楚。
1.在外键没有索引的情况下，修改主表的主键（delete or update），会在 ...

这位兄台，还是同样的问题，按照官方文档的环境，我就是达不到效果。如果你在父表上删除或者修改在子表有references的纪录会报ora－02292，如果修改父表的一条未被子表的reference的纪录，子表不会加任何锁，我已经实验过了。
这个看官方文档我也能看懂，我希望你可以自己做个实验看看啊