ITPub博客

首页 > 数据库 > Oracle > 基于v$lock.lmode分析父子表外键列是否索引持锁模式区别之系列六

基于v$lock.lmode分析父子表外键列是否索引持锁模式区别之系列六

原创 Oracle 作者:wisdomone1 时间:2015-10-29 17:00:50 0 删除 编辑

结论

1,oracle lock锁机制非常复杂,v$lock.lmode之间的区别以及适用场景还要研究


2,本文基于3个并发会话进行测试,第1个会话操作基于子表DML操作,第2个会话操作基于父表的DML操作,第3个会话基于子表的DML操作(包含INSERT,UPDATE,DELETE)


3,需要3个并发会话,基于子表外键列是否构建索引的行为区别
  对于子表的DML操作影响有所不同
  A,如构建索引,则第3个会话操作仅与操作父表DML对应记录的子表记录DML才会阻塞,其它子表的DML正常继续
  B,如不构建索引,则第3个会话的任何DML操作皆会受到阻塞




4,再细化分解下,因为是父子表,通过外键把父子表记录关联起来,以保障数据一致性,所以不管你是先更新父表或子表记录不提交,而更新对应的子表或父表记录对应的事务则会等待


5,未构建子表外键索引时,V$LOCK会同时对父子表持行级排它锁,并且会请求共享行级排它锁ssx
   相反如果构建子表外键索引时,v$lock也会同时对父子表持行级排它锁,不会请求任何锁


6, lmode值为0,即none锁,此模式锁将在另一文章进行测试与学习


7, 再直白一点讲,如子表外键列构建索引,到时对于子表的并发操作会更好,否则会更差,所以一定要为子表外键列构建索引


8,而且如不为子表外键列构建索引,删除父表DML,会导致子表全表扫描,否则仅会对子表进行索引扫描即可


9,还有一个问题,某个会话因为DML持的锁会不会因为并行会话的增加或变化,会导致某个会话DML持锁模式发生变化呢,这个还没有完全想明白,将在另一文章进行测试


10,v$lock.block的列含义及适用场景,同上,也要专门测试,加深理解   


测试


1,创建父表
SQL> create table t_parent(a int,b int,c int);


Table created.


2,创建子表
SQL> create table t_child(a int,b int);


Table created.


3,创建主键约束
SQL> alter table t_parent add constraint pk_t_parent primary key(a);


Table altered.


4,创建子键外键约束
SQL> alter table t_child add constraint fk_t_child foreign key(a) references t_parent(a);


Table altered.


5,父子表记录
SQL> select * from t_parent order by 1;


         A          B          C
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3


SQL> select * from t_child order by 1;


         A          B
---------- ----------
         1          1
         2          2




6,测试子表外键列是否构建索引,对于OLTP DML并行的影响
未建子表外键列索引


会话1


SQL> select sid from v$mystat where rownum=1;


       SID
----------
       234


删除子表1条记录
SQL> delete from t_child where a=1;


1 row deleted.






会话2


SQL> select sid from v$mystat where rownum=1;


       SID
----------
        55


插入主表1条记录
SQL> insert into t_parent values(4,4,4);


1 row created.


删除主表1条记录
SQL> delete from t_parent where a=4;


HANG住




监控会话
SQL> select sid,type,id1,id2,
  2         decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,
       ctime,block   
       decode(request,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') request,
  4         ctime,block   
  5  from v$lock where  type in ('TM','TX') and sid in (234,55);


       SID TYPE        ID1        ID2 LMODE      REQUEST         CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       234 TM        74751          0 row-x      none              238          0
       234 TM        74752          0 row-x      none              240          1
        55 TM        74751          0 row-x      none              226          0
        55 TM        74752          0 row-x      ssx               226          1 
       234 TX     15007761       3602 x          none              239          0
        55 TX     13434883       6598 x          none              227          0


6 rows selected.


SQL> select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where type in ('TM','TX');


TYPE                 NAME            ID1_TAG                   ID2_TAG                        IS_USE DESCRIPTION
-------------------- --------------- ------------------------- ------------------------------ ------ --------------------------------------------------
TM                   DML             object #                  table/partition                YES    Synchronizes accesses to an object
TX                   Transaction     usn<<16 | slot            sequence                       YES    Lock held by a transaction to allow other transact
                                                                                                     ions to wait for it


SQL> select object_id,object_name from dba_objects where object_id in (74751,74752);


 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     74751 T_PARENT
     74752 T_CHILD






会话3
SQL> insert into t_child values(1,1);


HANG住


SQL> insert into t_child values(3,3);


HANG住


SQL> delete from t_child where a=1;


HANG住


SQL> delete from t_child where a=2;


HANG住


SQL> update t_child set a=2 where a=1;


HANG住


SQL> update t_child set a=1 where a=2;


hang住


总结:
  1,子表外键列未建索引时,如果1个会话删除子表1条记录时,且另一会话删除主表1条记录时,而随后的会话任何对于子表的DML皆会HANG掉
     可见子表t_child不仅持行级排它锁,且会请求共享行级排它锁,而且t_child还是blocker就是它还是阻塞者,也就是说它占有了别人
     需要的锁资源;这样主表操作1条记录时,对子表任何的DML全会造成阻塞






建子表外键列索引


SQL> create index idx_t_child on t_child(a);


Index created.


会话1
SQL> delete from t_child where a=1;


1 row deleted.


会话2
SQL> insert into t_parent values(4,4,4);


1 row created.


SQL> delete from t_parent where a=4;


1 row deleted.


未HANG住




监控会话
SQL> select sid,type,id1,id2,
  2         decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,
  3         decode(request,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') request,
  4         ctime,block   
  5  from v$lock where  type in ('TM','TX') and sid in (234,55);


       SID TYPE        ID1        ID2 LMODE      REQUEST         CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       234 TM        74751          0 row-x      none              233          0
       234 TM        74752          0 row-x      none              234          0
        55 TM        74751          0 row-x      none              224          0
        55 TM        74752          0 row-x      none              224          0
       234 TX     13697029       5850 x          none              234          0
        55 TX     18939931       3110 x          none              224          0


6 rows selected.


会话3
SQL> insert into t_child values(1,1);


1 row created.
未HANG住


SQL> insert into t_child values(3,3);


1 row created.


未hang住


SQL> delete from t_child where a=1;
HANG住


SQL> delete from t_child where a=2;


1 row deleted.
未HANG住


SQL> update t_child set a=2 where a=1;
hang掉


SQL> update t_child set a=1 where a=2;


1 row updated.
未hang住


总结:
  1,子表外键列构建索引时,如果1个会话删除子表1条记录时,且另一会话删除主表1条记录时,而随后的会话任何对于子表的DML仅会HANG住与会话1更新相同行的DML操作
     可见对于主表操作,只会对于主键列对应的子表记录对应的DML造成阴塞


个人简介:


8年oracle从业经验,具备丰富的oracle技能,目前在国内北京某专业oracle服务公司从事高级技术顾问。
   
   服务过的客户:
          中国电信
          中国移动
          中国联通
          中国电通
          国家电网
          四川达州商业银行
          湖南老百姓大药房
          山西省公安厅
          中国邮政
          北京302医院     
          河北廊坊新奥集团公司
  
 项目经验:
           中国电信3G项目AAA系统数据库部署及优化
           中国联通CRM数据库性能优化
           中国移动10086电商平台数据库部署及优化
           湖南老百姓大药房ERR数据库sql优化项目
           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
           北京高铁信号监控系统RAC数据库部署及优化
           河南宇通客车数据库性能优化
           中国电信电商平台核心采购模块表模型设计及优化
           中国邮政储蓄系统数据库性能优化及sql优化
           北京302医院数据库迁移实施
           河北廊坊新奥data guard部署及优化
           山西公安厅身份证审计数据库系统故障评估
         
 联系方式:
          手机:18201115468
          qq   :   305076427
          qq微博: wisdomone1
          新浪微博:wisdomone9
          qq群:275813900    
          itpub博客名称:wisdomone1    http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1818443/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2149
  • 访问量
    11889279