ITPub博客

首页 > 数据库 > Oracle > 产生TX锁等待不同情形的分析

产生TX锁等待不同情形的分析

原创 Oracle 作者:sqysl 时间:2009-01-28 21:40:56 0 删除 编辑

本文给出与TX锁等待有关的几个例子。一般情况下,这些等待并不显著,除非它们运行的时间特别长,或者导致死锁(ORA-60错误)。本文给出的例子面向应用人员和DBA。例子的测试环境需要对V$视图的select权限。 
  1. 有用的sql语句
  如果遇到一个锁相关的hang的情形,可以使用下述语句,来帮助我们找出waiters和blockers:
  显示等待锁的所有会话:
  select event,p1,p2,p3 from v$session_wait
  where wait_time=0 and event='enqueue';
  显示等待TX锁的所有会话:
  select * from v$lock where type='TX' and request>0;
  显示持有TX锁的所有会话:
  select * from v$lock where type='TX' and lmode>0;
   2. 什么是TX锁
  当一个事务开始首次更改的时候,要求持有TX锁,直到事务执行一个COMMIT 或者 ROLLBACK。它利用的是 排队机制,其他的会话只能等待此事务结束。
  TX锁的名称(ID1 和 ID2)反映了活动事务ID。
   3. 例子
  使用下面的表来说明问题:
  以SCOTT/TIGER身份或者其他的测试用户连接数据库,并搭建好测试环境:
  DROP TABLE tx_eg;
  CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) )
  INITRANS 1 MAXTRANS 2;
  INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
  INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
  INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
  INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
  INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
  COMMIT;
  在本例中,要求4个会话:
  Ses#1 表TX_EG用户的第一个会话
  Ses#2 表TX_EG用户的第二个会话
  Ses#3 表TX_EG用户的第三个会话
  DBA SYSDBA用户,能访问
  例子涵盖下述情形:
   行被一个活动事务锁定
   唯一性约束或者主键约束
   块头中的事务槽不够
   同一位图索引涉及到多行
  4. 行被一个活动事务锁定
  当一个会话更新表中的行的时候,该行会被会话的事务锁定。其他用户可能会使用select查询此行,但是看到的是在更新前的行。如果另外一个会话也希望更新此行,它必须等待第一个会话提交或回滚。第二个会话以独占模式,等待第一个会话释放占用的TX锁。
  例如:
  Ses#1: update tx_eg set txt='Garbage' where num=1;
  Ses#2: update tx_eg set txt='Garbage' where num=1;
  DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
  from v$lock where type='TX';
  SID TY ID1 ID2 LMODE REQUEST
  ---------- -- ---------- ---------- ---------- ----------
  8 TX 131075 597 6 0
  10 TX 131075 597 0 6
   SID 10等待TX SID 8持有的TX锁,并且希望以独占模式占有此TX锁 (as REQUEST=6)。
  下面的查询,演示了当出现一个TX锁等待的时候,在V$SESSION_WAIT中显示一个'enqueue',P1RAW, P2 和 P3显示了实际的被等待的锁。当使用并行 服务器,event是'DFS enqueue lock acquisition',而不是'enqueue'。
  DBA: select sid,p1raw, p2, p3
  from v$session_wait
  where wait_time=0 and event='enqueue';
  SID P1RAW P2 P3
  ---------- -------- ---------- ----------
  10 54580006 131075 597
  > ~~~~ ~~ ~~~~~~ ~~~
  > type|mode id1 id2
  > T X 6 131075 597
  另一个查询显示object_id和会话等待的具体行。该信息在V$SESSION中仅仅当会话等待是行级锁才有效。该查询从Oracle 7.3开始可用。上述示例中,SID 10是等待者:
  DBA: select ROW_WAIT_OBJ#,
  ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
  from v$session
  where sid=10;
  ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
  ---------- ---------- ---------- ----------
  3058 4 2683 0
  > 等待者正在等待一个TX 锁,它锁定文件4中的2683块中的第0行,对象号是3058.
  Ses#1: rollback;
  Ses#2: rollback;
   5. 唯一性约束或者主键约束
  如果一个表有主键约束,或者唯一性约束,或者唯一性索引,那么该约束引用的列的唯一性由唯一性索引启用。如果两个会话想插入同一个键值,第二个会话必须等待,以便知道是否会产生ORA-0001错误。
  例如:
  Ses#1: ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );
  Ses#1: insert into tx_eg values (10,'New','MALE');
  Ses#2: insert into tx_eg values (10,'OtherNew',null);
  DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
  from v$lock where type='TX';
  SID TY ID1 ID2 LMODE REQUEST
  ---------- -- ---------- ---------- ---------- ----------
  8 TX 196625 39 6 0
  10 TX 262155 65 6 0
  10 TX 196625 39 0 4
  SID 10等待SID 8持有的TX锁,并想将此锁置于共享模式(REQUEST=4)。SID 10事务本身持有一个TX锁。
  Ses#1: commit;
  Ses#2: ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
  Ses#2: rollback;
  6. 块中的事务槽不够
  Oracle利用每个数据块中的头部信息,来标识哪些事务锁定了哪些行,这就是'interested transaction list'
  一个对象中的事务的ITL数量,由属性INITRANS 和 MAXTRANS控制。INITRANS是初始事务槽数量。MAXTRANS是允许的最多的事务槽数量。每个想要修改块的事务,都需要占用块的一个'ITL'。
  MAXTRANS决定了在一个块中,最大活动的并发事务数。
  INITRANS 为最小的并发事务数。如果要求的事务数介于INITRANS和MAXTRANS,ITL会自动扩展,前提是在块的头部还有足够的剩余空间,用于存放扩展ITL的信息。
  如果没有可用的'ITL',那么会话就会以模式4等待一个活动事务的TX锁。
  例如:
  Ses#1: update tx_eg set txt='Garbage' where num=1;
  Ses#2: update tx_eg set txt='Different' where num=2;
  Ses#3: update tx_eg set txt='Different' where num=3;
  DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
  from v$lock where type='TX';
  SID TY ID1 ID2 LMODE REQUEST
  ---------- -- ---------- ---------- ---------- ----------
  8 TX 655367 560 6 0
  10 TX 655367 560 0 4
  12 TX 458799 551 6 0
  SID 10等待SID 8持有的TX锁,并想将此锁置于共享模式(REQUEST=4)。
  Ses#1: commit;
  Ses#2: commit;
  Ses#3: commit;
  Ses#1: ALTER TABLE tx_eg MAXTRANS 3;
  Ses#1: update tx_eg set txt='First' where num=1;
  Ses#2: update tx_eg set txt='Second' where num=2;
  Ses#3: update tx_eg set txt='Different' where num=3;
  三行都可以更新,因为有可用的空间用于ITL列表进行增长,以满足三个事务的要求。
  Ses#1: commit;
  Ses#2: commit;
  Ses#3: commit;
  从9.2开始,可以使用v$segment_statistics来检查ITL等待:
  SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
  FROM v$segment_statistics t
  WHERE t.STATISTIC_NAME = 'ITL waits'
  AND t.VALUE > 0;
  OWNER OBJECT_NAME OBJECT_TYPE STATISTIC_NAME VALUE
  ------- ------------ ------------ --------------- -----
  HR TX_EG TABLE ITL waits 1
  必要时,可以增大INITTRANS 和 MAXTRANS
   7. 同一位图索引涉及到多行
  位图的键值对应一个范围内的ROWID。每个位图索引的入口可能对应实际表中的多行。
  如果两个会话希望更新同一个位图索引片段对应的行,那么第二个会话就会以共享模式等待TX锁,直到第一个会话提交或者回滚。
  例如:
  Ses#1: CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );
  Ses#1: update tx_eg set sex='FEMALE' where num=3;
  Ses#2: update tx_eg set sex='FEMALE' where num=4;
  DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
  from v$lock where type='TX';
  SID TY ID1 ID2 LMODE REQUEST
  ---------- -- ---------- ---------- ---------- ----------
  8 TX 262151 62 6 0
  10 TX 327680 60 6 0
  10 TX 262151 62 0 4
  SID 10等待SID 8持有的TX锁,并想将此锁置于共享模式(REQUEST=4)。
  Ses#1: commit;
  Ses#2: commit;
   8. 其他情形
  其他情形比较少见。

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

请登录后发表评论 登录
全部评论
上世纪90年代初至今,一直默默深耕于数据库领域,擅长数据库优化、数据库分析诊断、数据库规划设计等。曾供职于能源、金融、电信等行业,任多家知名大型企业首席DBA及数据库架构师等职位。

注册时间:2008-06-27

  • 博文量
    333
  • 访问量
    545335