当前位置： 首页 > 基础 > 正文

外键:主表与子表DML操作，TM锁申请模式

huangtingzhong 发布于 2014-11-26 22:05基础抢沙发

    下面是测试在不同版本，在主表与子表上进行DML操作时，在主表与子表分别获取TM锁的情况。主键存在的表称为主表，外键存在的表称为子表。分别测试了9i,10g,11g三个测试的情况。

1 创建对象

所有的对象都创建在HTZ用户下，需要在3个版本的数据库都创建。

drop user htz cascade;
create user htz identified by oracle;
grant dba to htz;
create table htz.htz as select * from dba_objects where object_id is not null and data_object_id is not null and object_id<100;
create table htz.htz1 as select * from dba_objects where object_id is not null and data_object_id is not null and object_id<1000;
alter table htz.htz1 add constraint pk_htz1 primary key(object_id)
alter table htz add constraint fk_htz1 foreign key(object_id) references htz1(object_id);

查询对象的object_id，在整个实验过程中会用到

orcl1123.htz.pw > select object_name,to_char(object_id,'xxxxxxxxxx') from dba_objects where object_name in ('HTZ','HTZ1');
OBJECT_NAME          TO_CHAR(OBJ
-------------------- -----------
HTZ1                       127d0
HTZ                        127cf
orcl9208.htz.pw > select object_name,to_char(object_id,'xxxxxxxxxx') from dba_objects where object_name in ('HTZ','HTZ1');
OBJECT_NAME          TO_CHAR(OBJ
-------------------- -----------
HTZ                         764f
HTZ1                        7650
orcl1024.htz.pw > col object_name for a20
orcl1024.htz.pw > select object_name,to_char(object_id,'xxxxxxxxxx') from dba_objects where object_name in ('HTZ','HTZ1');
OBJECT_NAME          TO_CHAR(OBJ
-------------------- -----------
HTZ1                        ca5f
HTZ                         ca5e

2 测试子表有索引，但是外键不在索引第一列

窗口一：

create index ind_htz on htz(data_object_id,object_id);

窗口二：

手动在子表上获取TM3级别的锁，需要在3个数据库中同时执行。

lock table htz.htz  in row exclusive mode nowait;

查询数据库锁模式

orcl1123.htz.pw > select sid,type,id1,id2,lmode,request from v$lock where type='TM';
       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
        31 TM      75727          0          3          0
orcl1024.htz.pw > select sid,type,id1,id2,lmode,request from v$lock where type='TM';
       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       145 TM      51806          0          3          0
orcl9208.htz.pw > select sid,type,id1,id2,lmode,request from v$lock where type='TM';
       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
        10 TM      30287          0          3          0
可以看到都占用了3级别的TM锁

窗口一：

conn / as sysdba
oradebug setmypid
oradebug event 10704 trace name context forever,level 12;
oradebug tracefile_name;
delete htz.htz1 where object_id=652;

三个版本的delete语句都被HANG了。

查看一下TRACE文件中的锁获取情况

[root@orcl1123 # ]grep "TM" /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_15543.trc
ksqgtl *** TM-000127cf-00000000 mode=4 flags=0x401 timeout=21474836 ***在HTZ上面获取4级别的锁
ksqcmi: TM,127cf,0 mode=4 timeout=21474836
[root@orcl1024 # ]grep 'TM' /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_15600.trc
ksqgtl *** TM-0000ca5e-00000000 mode=4 flags=0x401 timeout=21474836 ***在HTZ上面获取4级别的锁
ksqcmi: TM,ca5e,0 mode=4 timeout=21474836
[root@orcl9208 # ]grep "TM" orcl9208_ora_15596.trc
ksqcmi: TM,764f,0 mode=4 timeout=21474836 ****在HTZ上面获取4级别的锁

在三个版本中，在子表上面都去申请TM 4的级

退出窗口二，窗口一中的delete正常执行，再次查看锁获取情况

[root@orcl1123 # ]grep "TM" /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_15543.trc
ksqgtl *** TM-000127cf-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqcmi: TM,127cf,0 mode=4 timeout=21474836
ksqrcl: TM,127cf,0
ksqcmi: TM,127cf,0 mode=0 timeout=0
ksqgtl *** TM-000127d0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127cf-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127cf,0
ksqcmi: TM,127cf,0 mode=0 timeout=0
[root@orcl1024 # ]grep 'TM' /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_15600.trc
ksqgtl *** TM-0000ca5e-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqcmi: TM,ca5e,0 mode=4 timeout=21474836
ksqrcl: TM,ca5e,0
ksqcmi: TM,ca5e,0 mode=0 timeout=0
ksqgtl *** TM-0000ca5f-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000ca5e-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqrcl: TM,ca5e,0
ksqcmi: TM,ca5e,0 mode=0 timeout=0

[root@orcl9208 # ]grep "TM" orcl9208_ora_15596.trc
ksqcmi: TM,764f,0 mode=4 timeout=21474836
ksqcmi: TM,764f,0 mode=0 timeout=0
ksqcmi: TM,764f,0 mode=0 timeout=0

可以看到在子表外键列不在索引第一列时：DELETE主表记录获取锁为：子表申请TM 4，成功后释放，在主表申请TM3，再子表申请TM 4，DELETE成功后获取子表TM 4锁，在COMMIT后释放主表的TM 3的锁。

3 测试子表外键列有索引，并在第一列

会话二：
 create index htz.ind_htz on htz.htz(object_id,data_object_id);
会话一
oradebug setmypid
oradebug event 10704 trace name context forever,level 12;
oradebug tracefile_name;
delete htz.htz1 where object_id=651;
3个数据库都正常的执行

orcl1123.htz.pw > oradebug setmypid
Statement processed.
orcl1123.htz.pw > oradebug event 10704 trace name context forever,level 12;
Statement processed.
orcl1123.htz.pw > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_18403.trc
orcl1123.htz.pw > delete htz.htz1 where object_id=651;

1 row deleted.

[root@orcl1123 #]grep "TM" /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_18403.trc
ksqgtl *** TM-000127cf-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127d0-00000000 mode=3 flags=0x401 timeout=21474836 ***
[root@orcl9208 # ]grep "TM" /oracle/app/oracle/admin/orcl9208/udump/orcl9208_ora_18406.trc
ksqcmi: TM,764f,0 mode=2 timeout=2147483
[root@orcl1024 # ]grep 'TM' /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_18492.trc
ksqgtl *** TM-0000ca5e-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqcmi: TM,ca5e,0 mode=2 timeout=21474836
ksqgtl *** TM-0000ca5f-00000000 mode=3 flags=0x401 timeout=21474836 ***
查询锁的情况
orcl1123.htz.pw > select sid,type,id1,id2,lmode,request from v$lock where type='TM';
       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
         1 TM      75727          0          3          0
         1 TM      75728          0          3          0
        27 TM      75727          0          3          0
orcl9208.htz.pw > select sid,type,id1,id2,lmode,request from v$lock where type='TM';
       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
         9 TM      30287          0          2          0
         9 TM      30288          0          3          0
        10 TM      30287          0          3          0
orcl1024.htz.pw > select sid,type,id1,id2,lmode,request from v$lock where type='TM';
       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       145 TM      51806          0          3          0
       158 TM      51806          0          2          0

       158 TM      51807          0          3          0

从上面的测试可以看到：子表外键列有索引的时候，主表的DELETE操作，在子表获取TM 3的锁，10g,9i是TM 2的锁，在主表获取TM 3的锁，在DELETE成功后，并没有立即释放TM锁，需要等commit后，才释放锁。

4 测试子表外键列无索引

drop index htz.ind_htz;
lock table htz.htz  in row exclusive mode nowait; 
会话1
在个会话同时HANG住
[root@orcl1123 #]grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_19364.trc
ksqgtl *** TM-000127cf-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqcmi: TM,127cf,0 mode=4 timeout=2147483
[root@orcl1024 # ]grep 'TM' /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_19462.trc
ksqgtl *** TM-0000ca5e-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqcmi: TM,ca5e,0 mode=4 timeout=21474836

[root@orcl9208 # ]grep 'TM' orcl9208_ora_19370.trc
ksqcmi: TM,764f,0 mode=4 timeout=21474836
都获取4级别的锁

退出LOCK TABLE会话
[root@orcl1123 #]grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_19364.trc
ksqgtl *** TM-000127cf-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqcmi: TM,127cf,0 mode=4 timeout=21474836
ksqrcl: TM,127cf,0
ksqcmi: TM,127cf,0 mode=0 timeout=0
ksqgtl *** TM-000127d0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127cf-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127cf,0
ksqcmi: TM,127cf,0 mode=0 timeout=0

[root@orcl9208 # ]grep 'TM' orcl9208_ora_19370.trc
ksqcmi: TM,764f,0 mode=4 timeout=21474836
ksqcmi: TM,764f,0 mode=0 timeout=0
ksqcmi: TM,764f,0 mode=0 timeout=0

[root@orcl1024 # ]grep 'TM' /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_19462.trc
ksqgtl *** TM-0000ca5e-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqcmi: TM,ca5e,0 mode=4 timeout=21474836
ksqrcl: TM,ca5e,0
ksqcmi: TM,ca5e,0 mode=0 timeout=0
ksqgtl *** TM-0000ca5f-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000ca5e-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqrcl: TM,ca5e,0
ksqcmi: TM,ca5e,0 mode=0 timeout=0

整个测试结果跟测试1结果一样

5 测试子表的delete操作

oradebug setmypid
oradebug event 10704 trace name context forever,level 12;
oradebug tracefile_name;
delete htz.htz where object_id=2;

1 row deleted.
跟是否有索引无影响
[root@orcl1123 #]grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_20307.trc
ksqgtl *** TM-000127cf-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127d0-00000000 mode=3 flags=0x401 timeout=21474836 ***

[root@orcl9208 # ]grep 'TM' orcl9208_ora_20494.trc
ksqcmi: TM,7650,0 mode=0 timeout=0
ksqcmi: TM,764f,0 mode=0 timeout=0

[root@orcl1024 # ]grep 'TM' /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_20716.trc
ksqgtl *** TM-0000ca5e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000ca5f-00000000 mode=2 flags=0x401 timeout=21474836 ***

可以看到：在子表上成功申请TM3级别的锁后，在主表上升级TM 3(11G)TM2(10g)的锁，DELETE语句执行完后，并没有释放。

6 INSERT语句

下面所有的测试只在11G版本中，没有在其它的版本中测试

insert主表
orcl1123.htz.pw > select object_name,to_char(object_id,'xxxxxxxxxx') from dba_objects where object_name in ('HTZ','HTZ1');

OBJECT_NAME                    TO_CHAR(OBJ
------------------------------ -----------
HTZ1                                 127e0
HTZ                                  127df

orcl1123.htz.pw > conn / as sysdba
Connected.
orcl1123.htz.pw > oradebug setmypid
Statement processed.
orcl1123.htz.pw > oradebug event 10704 trace name context forever,level 12;
Statement processed.
orcl1123.htz.pw > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9770.trc

orcl1123.htz.pw > insert into htz.htz1 (object_id) values(100000);
1 row created.
orcl1123.htz.pw > commit;
Commit complete.
orcl1123.htz.pw > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9770.trc
orcl1123.htz.pw > !grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9770.trc
ksqgtl *** TM-000127df-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127e0,0
ksqrcl: TM,127df,0

在子表成功申请TM 3级别锁后，在主表再升级TM 3级别的锁。

7 子表INSERT语句

orcl1123.htz.pw > oradebug setmypid
Statement processed.
orcl1123.htz.pw > oradebug event 10704 trace name context forever,level 12;
Statement processed.
orcl1123.htz.pw > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9964.trc
orcl1123.htz.pw > insert into htz.htz (object_id) values(100000);
1 row created.
orcl1123.htz.pw > commit;
Commit complete.
orcl1123.htz.pw > !grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9964.trc
ksqgtl *** TM-000127df-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127e0,0
ksqrcl: TM,127df,0

在子表成功申请TM 3级别锁后，在主表再升级TM 3级别的锁。

8 UPDATE语句

更新主表
orcl1123.htz.pw > oradebug setmypid
Statement processed.
orcl1123.htz.pw > oradebug event 10704 trace name context forever,level 12;
Statement processed.
orcl1123.htz.pw > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_10491.trc
orcl1123.htz.pw > update htz.htz1 set object_id='23000' where object_id='555';
1 row updated.
orcl1123.htz.pw > commit;

Commit complete.

orcl1123.htz.pw > !grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_10491.trc
ksqgtl *** TM-000127df-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127df,0
ksqcmi: TM,127df,0 mode=0 timeout=0
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127df-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127df,0
ksqcmi: TM,127df,0 mode=0 timeout=0
ksqrcl: TM,127e0,0
跟DELETE主表一样的锁模式
非主键列
orcl1123.htz.pw > update htz.htz1 set owner='HTZ' where object_id=2;
1 row updated.
orcl1123.htz.pw > commit;
Commit complete.
orcl1123.htz.pw > !grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_11669.trc
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127e0,0
更新非主键列时，只升级主表上面的锁，子表不影响

子表有索引的情况 
orcl1123.htz.pw > create index htz.htz_4 on htz.htz(object_id);
Index created.
orcl1123.htz.pw > update htz.htz1 set object_id=333333 where object_id=333;
1 row updated.
orcl1123.htz.pw > commit;
Commit complete.
[oracle@www.htz.pw sql]$grep  'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_11805.trc
ksqgtl *** TM-000127df-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127e0,0
ksqrcl: TM,127df,0
跟DELETE主表一样的锁模式

更新子表外键列 
orcl1123.htz.pw > update htz.htz set object_id=3 where object_id=2;
1 row updated.
orcl1123.htz.pw > commit;
Commit complete.

orcl1123.htz.pw > !grep 'TM' /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_10838.trc
ksqgtl *** TM-000127df-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127e0,0
ksqcmi: TM,127e0,0 mode=0 timeout=0
ksqrcl: TM,127df,0
ksqcmi: TM,127df,0 mode=0 timeout=0
ksqgtl *** TM-000127df-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000127e0-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TM,127e0,0
ksqrcl: TM,127df,0

在更新子表外键列中：先申请子表TM3级别的锁，再升级主表的TM3级别的锁，2个都释放，再升级2个表的TM3级别的锁。

赏

您可能还会对这些文章感兴趣！

外键:主表与子表DML操作，TM锁申请模式：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter