主表DML操作时，子表外键 创建索引/不创建索引，分别 获取什么类型的锁

Question

一直对oracle的锁 问题不太清楚，现在想详细的研究下这个问题。

环境：   有主外键关系的两张表

情况一：  子表外键没索引

当父表对 主键做 DML操作时、oracle会锁住子表(SSX,LMODE=5，并且锁 获取之后  立即释放)，此时子表 禁止 DML操作

情况二：子表外键有索引

当父表对 主键做DML操作时，oracle也会锁住子表(SX,LMODE=4)，但是子表 可以进行DML操作。

我想重现上面两种情况，问题是 SSX锁 不容易捕捉，大家有没 好的测试方法，请大家帮忙

谢谢大家了

以下附上官方文档对以上的描述：

No Index on the Foreign Key [url=]Figure 21-8[/url] illustrates the locking mechanisms used byOracle when no index is defined on the foreign key and when rows are beingupdated or deleted in the parent table. Inserts into the parent table do not requireany locks on the child table.

Index on the Foreign Key[url=]Figure21-9[/url] illustrates the locking mechanisms used when an index is defined onthe foreign key, and new rows are inserted, updated, or deleted in the childtable.Indexed foreign keys cause a row share table lock(also sometimes called a subshare table lock, SS). Thisprevents other transactions from exclusive locking the whole table, but it doesnot block DML on the parent or the child table.This situation is preferable ifthere is any update or delete activity on the parent table while updateactivity is taking place on the child table. Inserts, updates, and deletes onthe parent table do not require any locks on the child table, although updatesand deletes will wait for row-level locks on the indexes of the child table toclear.

操作, 索引, 类型, oracle, 测试

Answer 1

Thanks for sharing. I didn't realize the foreign key was created on delete cascade. That's an unusual case. In our databases, I don't think we ever do that.

Answer 2

您好， 以下内容是 warehouse版主发的帖子：

子表添加外键是 指定 on delete cascade

http://www.itpub.net/thread-1357284-1-1.html

外键引起的阻塞：

session 1:

SQL> create table p(id int primary key ) tablespace users;

表已创建。

SQL> create table r(id int references p on delete cascade) tablespace users;

表已创建。

SQL> insert into p values(1);

已创建 1 行。

SQL> insert into p values(2);

已创建 1 行。

SQL> insert into p values(3);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select distinct sid from v$mystat;

       SID

----------

       159

SQL> select * from p;

        ID

----------

         1

         2

         3

SQL> delete from p where id=1;

已删除 1 行。

SQL>

--=======================

session 3:

SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in

  2  (159,128) order by sid;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------- -- ---------- ---------- ---------- ---------- ---------- ----------

       159 TM      13017          0          3          0          9          0

       159 TX     589854        449          6          0          9          0

       159 TM      13020          0          3          0          9          0

SQL> col object_name format a10

SQL> select object_id,object_name from dba_objects where object_id in (13017,130

20);

OBJECT_ID OBJECT_NAM

---------- ----------

     13017 P

     13020 R

--=========================

很明显session 1中的语句delete from p where id=1;在字表r上加了锁，mode为3；

--=========================

session 2：

SQL> select distinct sid from v$mystat;

       SID

----------

       128

SQL> delete from p where id=2;

等待、被session 1阻塞了

--=========================

session 3:

SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in

  2  (159,128) order by sid;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------- -- ---------- ---------- ---------- ---------- ---------- ----------

       128 TM      13017          0          3          0          9          0

       128 TM      13020          0          0          5          9          0

       159 TX     589854        449          6          0         90          0

       159 TM      13020          0          3          0         90          1

       159 TM      13017          0          3          0         90          0

SQL>

很明显session 2中的语句delete from p where id=2;请求锁mode为5被

session 1中的语句delete from p where id=1加在r上的锁mode为3阻塞了;

由于mode 3对应的RX(row exclusive)和mode 5对应的SRX(share row exclusive)不能兼容，因此

session 2被阻塞了。

Answer 3

Can you give me the exact SQLs in your second test? How do you update the parent table if the value is not updated in the child first? Do you set a different, irrelevant, column to a different value?

Answer 4

黄老师您好，今天抽时间把当时我的测试步骤 写出来，有改正之处望指教

方法一：

由于LMODE=5的 SSX锁 是获取之后自动释放，不易捕捉。所以 创建锁、捕获锁的时候都采用 循环

父表 :father

子表: childen

锁结果表 : lock_more

脚本一 创建锁。 采用循环，目的：把DML的事务  时间放大

[oracle@db ~]$ vi ct_lock.sql

declare

begin

for i in 4..100000 loop

update father set deptno=deptno*1 ;

end loop;

end;

/

脚本二： 抓取锁，同样采用循环，不停的 执行脚本 chk_lock

[oracle@db ~]$ vi chk_lock.sql

declare

v1 lock_more%rowtype;

cursor c1 is select lk.sid,lk_obj.object_id,obj.object_name,DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive') lmode

from v$locked_object lk_obj,dba_objects obj,v$lock lk

where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and obj.object_id in (53053,53055);

begin

open c1;

loop

fetch c1 into v1;

exit when c1%notfound;

insert into lock_more values(v1.locker_sid,v1.locked_objno,v1.locked_obj,v1.lock_mode);

commit;

end loop;

end;

/

脚本一执行之后采用for循环 依次执行 脚本二。目的就是想把 LMODE=5的SSX锁 捕获到。

结论一： 我试了自己知道的所有方法，但是还是不能捕获到 SSX的锁。

方法二：

受 版主 warehouse 启发：不容易直接捕获到 session1对 子表添加的 SSX锁，但是可以 间接的通过其他方法 捕获到 session2 的  请求的 锁类型

测试步骤：

session1 对父表做 update操作，不提交

session2 对子表 做update操作，此时 session2 hang 住

查询v$locked_object视图，查到 session2 正在请求 LMODE=5的锁。

测试步骤 同上。

Answer 5

谢谢您。这段时间有点忙，我抽空把自己的 测试方案 写出来

望指正

Answer 6

It's always a good idea to give us the source when you quote somebody. In your case, you may be quoting from 10gR2 documentation:

http://docs.oracle.com/cd/B19306 ... b14220/data_int.htm

I found a 10gR2 database trying to reproduce mode 5 SSX lock but couldn't reproduce. Not sure what kind of DML is needed. Basically, you use event 10704 to capture all enqueue locks acquired and released by the session. This is very useful when the lock comes and goes too fast. My code to reproduce is:

create table parent (x int primary key);

create table child (x int, foreign key (x) references parent);

insert into parent values (123);

insert into child values (123);

commit;

alter session set events '10704 trace name context forever, level 10';

SQL> delete from parent where x = 123;

delete from parent where x = 123

*

ERROR at line 1:

ORA-02292: integrity constraint (YHUANG.SYS_C0022786) violated - child record found

The trace file shows:

$ egrep 'TM|TX' ctmsp_ora_7891.trc

ksqgtl *** TM-000181a2-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-000181a4-00000000 mode=4 flags=0x401 timeout=21474836 ***

ksqrcl: TM,181a4,0

ksqcmi: TM,181a4,0 mode=0 timeout=0

ksqgtl *** TX-00030018-00001b26 mode=6 flags=0x401 timeout=0 ***

ksqgtl *** TM-000181a4-00000000 mode=4 flags=0x401 timeout=21474836 ***

ksqrcl: TM,181a4,0

ksqcmi: TM,181a4,0 mode=0 timeout=0

ksqrcl: TX,30018,1b26

ksqrcl: TM,181a2,0

ksqcmi: TM,181a2,0 mode=0 timeout=0

$ grep 'mode=5' ctmsp_ora_7891.trc

$

ksqgtl is to get a lock, and ksqrcl to release it, ksqcmi to change mode. I don't see TM lock taken in mode=5.