关于 子表外键 创建索引/不创建索引的造成表锁的问题.

iyoume2008

刘大，你好，请指出我的 有误之处
硬件环境：Redhat 5.4-32bit
ORACLE: 10.2.0.2-32bit

ORACLE 官方文档上有介绍：有主外键关系的两张表，当子表外键上没有索引时，对主表做更新、删除操作，会锁住子表！
但是我的测试结果不是这样，以下附上我的测试 方案及 测试结果 ，请刘大指出我的失误之处。 非常感谢

创建测试环境
TEST @ prod >create table father as  select * from scott.dept;
  
Table created.
  
TEST @ prod >create table childen as  select * from scott.emp;
  
Table created.
  
TEST @ prod >alter table father add  constraint pk_deptno primary key (deptno);
  
Table altered.
  
TEST @ prod >alter table childen add  constraint fk_deptno foreign key (deptno) references father(deptno);
  
Table altered.
  
测试一：子表的外键 没有 索引！！！

父表插入数据，观察该对象是否被锁
  
TEST @ prod >insert into father  values(50,'beijing','china');
  
1 row created.
  
TEST @ prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id
  
  
OBJECT_ID OBJECT_NAM SESSION_ID  LOCKED_MODE
  
--------- ---------- ---------- -----------
  
     52786 CHILDEN           159           2
  
52785  FATHER            159           3
  
  
结论：INSERT操作会同时锁定 父表、子表
  

子表插入数据，观察该对象是否被锁
  
TEST @ prod >insert into childen  values(1111,'test','test',8888,'28-SEP-81',8,0,50);
  
  1 row created.
  
TEST @ prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  

OBJECT_ID OBJECT_NAM SESSION_ID  LOCKED_MODE
  
--------- ---------- ----------  -----------
  
     52786 CHILDEN           159           3
  
52785  FATHER            159           2
  
结论：子表插入数据，会同时锁定  父表、子表
  


父表更新数据，观察该对象是否被锁
  
TEST @  prod >update father set deptno=deptno*1 ;
  
  
5 rows  updated.
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52785 FATHER            159           3   
  
  
结论：Update操作，只会锁定父表不会锁定子表
  

子表更新数据，观察该对象是否被锁
  
TEST @  prod >update childen set deptno=deptno*1 ;
  
  
14  rows updated.
  
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52786 CHILDEN           159           3
  
52785 FATHER             159           2
  
  
结论：在子表执行 update操作，会同时锁定  父表、子表
  





父表删除数据,观察该对象是否被锁
  
TEST @  prod >delete from father where deptno=50;
  
  
1 row  deleted.
  
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
52785 FATHER             159           3
  
  
结论：在父表执行Delete操作不提交，只会锁定父表不会锁定子表
  



子表删除数据，观察对象是否被锁
  
TEST @  prod >delete from childen where deptno=50;
  
  
0 rows  deleted.
  
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52786 CHILDEN           159           3
  
52785 FATHER             159           2
  
  
结论：在子表执行delete操作不提交，会同时锁定  父表、子表
  


测试二：
子表的外键字段添加索引
  
TEST @  prod >create index ind_deptno on childen(deptno);
  
  
Index  created.
  
  
TEST @  prod >analyze table childen compute statistics;
  
  
Table  analyzed.
  












父表插入数据，观察该对象是否被锁？？？
  
TEST @ prod >insert into father  values(50,'beijing','china');
  
  
1 row created.
  
TEST @ prod >col object_id for 99999
  
TEST @ prod >col object_name for a10
  
TEST @ prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id
  
  
OBJECT_ID OBJECT_NAM SESSION_ID  LOCKED_MODE
  
--------- ---------- ----------  -----------
  
     52786 CHILDEN           159           2
  
52785  FATHER            159           3
  
  
结论：INSERT操作会同时锁定 父表、子表
  





子表插入数据，观察该对象是否被锁
  
TEST @ prod >insert into childen  values(1111,'test','test',8888,'28-SEP-81',8,0,50);
  
  
1 row created.
  
  
TEST @ prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID OBJECT_NAM SESSION_ID  LOCKED_MODE
  
--------- ---------- ----------  -----------
  
     52786 CHILDEN           159           3
  
52785  FATHER            159           2
  
  
结论：子表插入数据，会同时锁定  父表、子表
  


父表更新数据，观察该对象是否被锁？？？？
  
TEST @  prod >update father set deptno=deptno*1 ;
  
  
5 rows  updated.
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52785 FATHER            159           3   
  
  
结论：Update操作不提交 ，只会锁定父表不会锁定子表
  







子表更新数据，观察该对象是否被锁
  
TEST @  prod >update childen set deptno=deptno*1 ;
  
  
14  rows updated.
  
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52786 CHILDEN           159           3
  
52785 FATHER             159           2
  
  
结论：在子表执行 update操作，会同时锁定  父表、子表
  



父表删除数据,观察该对象是否被锁
  
TEST @  prod >delete from father where deptno=50;
  
  
1 row  deleted.
  
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52786 CHILDEN           159           2
  
52785 FATHER             159           3
  
  
结论：在父表执行 delete操作，会同时锁定  父表、子表
  


子表删除数据，观察对象是否被锁
  
TEST @  prod >delete from childen where deptno=50;
  
  
0 rows  deleted.
  
  
TEST @  prod >select  lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from  v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and  lock1.object_id=obj.object_id;
  
  
OBJECT_ID  OBJECT_NAM SESSION_ID LOCKED_MODE
  
---------  ---------- ---------- -----------
  
    52786 CHILDEN           159           3
  
52785 FATHER             159           2
  
  
结论：在子表执行 delete操作，会同时锁定  父表、子表
  



结论：

子表外键 未创建索引
        INSERT                UPDATE                DELETE
父表        锁父表、子表        锁父表                锁父表
子表        锁父表、子表        锁父表、子表        锁父表、子表


子表外键 创建索引
        INSERT                UPDATE                DELETE
父表        锁父表、子表        锁父表                锁父表、子表
子表        锁父表、子表        锁父表、子表        锁父表、子表


我得出的结论和官方文档上的结论不一致，望刘大指正我的错误之处

谢谢了

Maclean Liu(刘相兵

http://t.askmaclean.com/thread-734-1-1.html


TM - DML (Table) enqueue - when a session wants to lock a table, a TM enqueue is requested.
     If a session deletes a row in the parent-table (DEPT) and a referential constraint
     (foreign key) is created without an index on the child-table (EMP), or if the session
      is updating the column(s) that the foreign key references to then a share lock (level 4)
     is taken on the child table. If another session tries to do changes to the child-table
     they have to wait (because they want the enqueue in row exclusive mode, and that is not
     compatible with the share mode). If an index is created on the child-table?s foreign key-column
     then no share-lock is required on the child-table.




这里的 Table lock是指Share (S)(TM lmode=4)  share sub exclusive (SSX)  (TM lmode=5) Exclusive (X) (TM lmode=6) 这类会 block DML 的 表锁


Row-exclusive(RX) 锁 无论是否有外键的表，发生DML时 都会有  TM 3  Row-exclusive(RX) 锁。

session a:
create table test1(t1 int);
insert into test1 values(1);

session B:


SQL> select * from v$lock where type in ('TX','TM');

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009CD043F0 000000009CD04418        159 TM      57029          0          3          0          7          0
000000009CD5E428 000000009CD5E5B0        159 TX          4        223          6          0          7          0



外键与 TM enqueue lock 的主要问题是 在早期版本中(9i之前) 当 子表child table上 的外键没有索引时 ， 若发生 父表 parent table 上记录被delete 或 update时 ， 会在child table上加 share lock (即文档中想要强调的 table lock)， 这会 阻塞 child table 上的DML。



但是从 9i以后的当 子表child table上 的外键没有索引时， 父表parent table上的delete 、update 只在 实际这个DML执行的过程中要求share (TM lmode=4) lock，而不会在整个事务中 都要求保持 child table上的 share lock。



The update/delete of the parent will still attemp to lock the child but only for an instant.  If
the child has outstanding transactions against it, the update/delete will have to wait.  Other
transactions will become blocked by the update/delete itself (potentially).  Hence the problem
still exists, just in a lesser form.

The child table lock is taken for the duration of the parent update/delete NOT the entire
transaction in 9i.  Lessens the problem but does not remove it.


illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.

Oracle no longer requires a share lock on unindexed foreign keys when doing an update or delete on the primary key. It still obtains the table-level share lock, but then releases it immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained and released once for each row.

In previous releases, a share lock of the entire child table was required until the transaction containing the DELETE statement for the parent table was committed. If the foreign key specifies ON DELETE CASCADE, then the DELETE statement resulted in a table-level share-subexclusive lock on the child table. A share lock of the entire child table was also required for an UPDATE statement on the parent table that affected any columns referenced by the child table. Share locks allow reading only. Therefore, no INSERT, UPDATE, or DELETE statements could be issued on the child table until the transaction containing the UPDATE or DELETE was committed. Queries were allowed on the child table.

INSERT, UPDATE, and DELETE statements on the child table do not acquire any locks on the parent table, although INSERT and UPDATE statements wait for a row-lock on the index of the parent table to clear.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

drop table t1 cascade constraints;
drop table t2 cascade constraints;


create table t1 ( x int primary key );
create table t2 ( y references t1 );


insert into t1 values ( 1 );
insert into t1 values ( 2 );
commit;

set echo off
PROMPT goto another window, execute:
PROMPT insert into t2 values ( 1 );
PROMPT then come back here and hit enter
set echo on
pause

set echo off
PROMPT the following update will BLOCK as it
PROMPT tries to lock the child (cannot because
PROMPT of the other session).
update t1 set x = 3 where x = 2;


SQL> select * from v$lock where type in ('TX','TM');

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009CD043F0 000000009CD04418        159 TM      57042          0          3          0          9          0
000000009CD71DC8 000000009CD71F50        159 TX         73        225          6          0          9          0


SQL> select object_name from dba_objects where object_id=57042;

OBJECT_NAME
-------------------------------------
T1

iyoume2008

Maclean Liu(刘相兵 发表于 2014-5-8 10:58
http://t.askmaclean.com/thread-734-1-1.html

谢谢你，我继续 研究这个问题

iyoume2008

Maclean Liu(刘相兵 发表于 2014-5-8 10:58
http://t.askmaclean.com/thread-734-1-1.html

非常感谢，现在我已经非常清楚了。感谢刘大