sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle主表与子表DML操作时 TM锁申请模式

时间:2016-11-02 20:37   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 外键:主表与子表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,在整个实验过程中会用到
oracleplus.net > 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;

查询数据库锁模式
oracleplus.net > 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    REQUESOracleoracleplus.netT
---------- -- ---------- ---------- ---------- ----------
       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个数据库都正常的执行

oracleplus.net > oradebug setmypid
Statement processed.
oracleplus.net > oradebug event 10704 trace name context forever,level 12;
Statement processed.
oracleplus.net > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_18403.trc
oracleplus.net > 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 ***
查询锁的情况
oracleplus.net > 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主表
oracleplus.net > 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

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

oracleplus.net > insert into htz.htz1 (object_id) values(100000);
1 row created.
oracleplus.net > commit;
Commit complete.
oracleplus.net > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9770.trc
oracleplus.net > !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语句

oracleplus.net > oradebug setmypid
Statement processed.
oracleplus.net > oradebug event 10704 trace name context forever,level 12;
Statement processed.
oracleplus.net > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_9964.trc
oracleplus.net > insert into htz.htz (object_id) values(100000);
1 row created.
oracleplus.net > commit;
Commit complete.
oracleplus.net > !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语句

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

Commit complete.

oracleplus.net > !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主表一样的锁模式
非主键列
oracleplus.net > update htz.htz1 set owner='HTZ' where object_id=2;
1 row updated.
oracleplus.net > commit;
Commit complete.
oracleplus.net > !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
更新非主键列时,只升级主表上面的锁,子表不影响

子表有索引的情况
oracleplus.net > create index htz.htz_4 on htz.htz(object_id);
Index created.
oracleplus.net > update htz.htz1 set object_id=333333 where object_id=333;
1 row updated.
oracleplus.net > 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主表一样的锁模式

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

oracleplus.net > !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级别的锁。

本文固定链接: http://www.htz.pw/2014/11/26/%e5%a4%96%e9%94%ae%e4%b8%bb%e8%a1%a8%e4%b8%8e%e5%ad%90%e8%a1%a8dml%e6%93%8d%e4%bd%9c%ef%bc%8ctm%e9%94%81%e7%94%b3%e8%af%b7%e6%a8%a1%e5%bc%8f.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle主表与子表DML操作时 TM锁申请模式

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1144.html

Oracle研究中心

关键词:

Oralce TM锁申请模式

Oralce

Oracle锁模式申请规则