我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
外键:主表与子表DML操作，TM锁申请模式
下面是测试在不同版本，在主表与子表上进行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级别的锁。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
