DML子表锁定父表问题

lanseguhun

在11gR2的concept的第9章,介绍外健无索引情况时候,图9-3下有句话,

DML on a child table does not acquire a table lock on the parent table.

但是我在delete child table一行时发现会在parent table加一个RX锁.
SQL> create table p ( x int primary key );
Table created.
SQL> create table c ( x references p );
Table created.
SQL> insert into p values ( 1 );
1 row created.
SQL> insert into p values ( 2 );
1 row created.
SQL> commit;
Commit complete.
SQL> insert into c values ( 2 );
1 row created.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> delete from c where x=2;   
1 row deleted.
SQL> select * from V$mystat where rownum=1;
       SID STATISTIC#    VALUE
---------- ---------- ----------
71     0        0
SQL> select object_name,object_id from user_objects where object_name in('P','C');
OBJECT_NAME   OBJECT_ID
----------------------    ----------
C                          74666
P                          74664  

SQL> select * from V$lock where sid=71;
ADDR   KADDR    SID TY        ID1   ID2    LMODE    REQUEST  CTIME     BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000091059638  0000000091059690    71 AE        100     0          4   0      0  0
000000009105A0E0 000000009105A138    71 TO      65927     1        3   0      0  0
00002B02F266D208 00002B02F266D268   71 TM      74664     0        3   0      0  0
00002B02F266D208 00002B02F266D268   71 TM      74666     0        3   0      0  0
000000008FEAD360 000000008FEAD3D8  71 TX     589850  1965    6   0      0  0

[ 本帖最后由 lanseguhun 于 2012-4-17 15:30 编辑 ]

Maclean Liu(刘相兵

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

lanseguhun

了解了，谢谢maclean

bal

这个论述并没有讲 为什么在对子表操作的时候，父表也存在着TM锁？  请指教

bal

SQL> select * from t1;

                                      X
---------------------------------------
                                      1
                                      2

SQL> select * from t2;

                                      Y
---------------------------------------
                                      2

session1 >delete t2
where Y=2;

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

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
070000006D991E90 070000006D991EB8        123 TM        85509          0          2          0       3021          0
070000006D991F90 070000006D991FB8        123 TM        85511          0          3          0       3021          0
070000006DA2C4F8 070000006DA2C530        123 TX       720942       3789          6          0       3021          0

此时另外一个session

session2 > drop table t1;

就会报错：
ora-00054

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME
  2   from user_indexes
  3  where table_name='T2';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
IDX_T2_01                      NORMAL                      T2

子表t2是有索引的。

[ 本帖最后由 bal 于 2012-7-19 16:15 编辑 ]