TX锁模式与场景

1. 介绍

以前写过一篇关于Oracle锁概念介绍性文章,参考:Oracle锁概念与常见锁分析
后面打算分几篇文章,详细介绍一些Oracle常见锁的场景。本篇介绍enqueue的TX锁场景。
TX锁一般与业务结合紧密,在业务发生一些问题时会经常碰到,所以如果不对这些场景了熟于胸,那发生故障时就很难快速判断与处理。

2. 场景一:业务更新同一行记录

会话一:

SQL> select distinct sid from v$mystat;

       SID
----------
       243

SQL> update t1 set object_name = 'a' where object_id = 50;

 1 row updated.

会话二:

SQL> select distinct sid from v$mystat;

       SID
----------
         3

SQL> update t1 set object_name = 'a' where object_id = 50;

会话二挂住

  • 查询锁等待情况
  • set linesize 180
    column event format a30
    column blocking_instance format 9999 heading 'Blocker|Instance'
    column blocking_session format 9999 heading 'Blocker|Session'
    select s.sid,
           s.EVENT,
           s.P1,
           s.P2,
           s.P3,
           s.blocking_instance,
           s.blocking_session,
           l.TYPE,
           l.ID1,
           l.ID2,
           l.LMODE,
           l.REQUEST
      from v$lock l, v$session s
     where l.SID = s.SID
       and s.sid in (243, 3)
       and l.type = 'TX'
      order by s.sid;
    
                                                                                Blocker Blocker
           SID EVENT                                  P1            P2         P3 Instance Session TY        ID1        ID2      LMODE    REQUEST
    ---------- ------------------------------ ----------    ---------- ---------- -------- ------- -- ---------- ---------- ---------- ----------
             3 enq: TX - row lock contention  1415053318 --+    655360       2303        1     243 TX     655360       2303          0          6
           243 SQL*Net message from client    1650815232   |         1          0                  TX     655360       2303          6          0
                                                           |
                                                           |-  1415053318转成十六进制为:54580006 (P1RAW)
                                                               5458转为ASCII码为:TX   0006为请求的锁模式
    

    或者

    select l1.sid  as "Waiter",
           l1.TYPE,
           l1.id1,
           l1.id2,
           l1.REQUEST,
           l2.sid as "Blocker",
           l2.LMODE as "Blocker_LMode"
      from v$lock l1,
           v$lock l2
    where l1.type = 'TX'
      and l2.type = 'TX'
      and l1.id1 = l2.id1
      and l1.id2 = l2.id2 
      and l1.request > 0
      and l1.lmode = 0
      and l2.request = 0
      and l2.LMODE > 0;
    
        Waiter TY        ID1        ID2    REQUEST    Blocker Blocker_LMode
    ---------- -- ---------- ---------- ---------- ---------- -------------
             3 TX     655360       2303          6        243             6
    

    关键点:请求的锁模式是 6,阻塞者持有的锁模式是6

    3. 场景二:位图索引问题

    环境准备:

    SQL> create table t1 as select object_id, object_name, status from dba_objects;
    
    Table created.
    
    SQL> create bitmap index idx_status on t1(status);
    
    Index created.
    

    会话一

    SQL> select distinct sid from v$mystat;
    
           SID
    ----------
           243
    
    SQL> update t1 set status = 'FALSE' where object_id = 100;
    
     1 row updated.
    

    会话二

    SQL> select distinct sid from v$mystat;
    
           SID
    ----------
             3
    
    SQL> update t1 set status = 'FALSE' where object_id = 101;
    

    会话二挂起

    set linesize 180
    column event format a30
    column blocking_instance format 9999 heading 'Blocker|Instance'
    column blocking_session format 9999 heading 'Blocker|Session'
    select s.sid,
           s.EVENT,
           s.P1,
           s.P2,
           s.P3,
           s.blocking_instance,
           s.blocking_session,
           l.TYPE,
           l.ID1,
           l.ID2,
           l.LMODE,
           l.REQUEST
      from v$lock l, v$session s
     where l.SID = s.SID
       and s.sid in (243, 3)
       and l.type = 'TX'
      order by s.sid;
    
                                                                                Blocker Blocker
           SID EVENT                                  P1         P2         P3 Instance Session TY        ID1        ID2      LMODE    REQUEST
    ---------- ------------------------------ ---------- ---------- ---------- -------- ------- -- ---------- ---------- ---------- ----------
             3 enq: TX - row lock contention  1415053316     196626        986        1     243 TX     655381       2307          6          0
             3 enq: TX - row lock contention  1415053316     196626        986        1     243 TX     196626---+    986          0          4
           243 SQL*Net message from client    1650815232          1          0                  TX     196626   |    986          6          0
                                                                                                                |
                                                                                                                |--196626转成十六进制:30012
                                                                                                                   根据ID1定义,高16位是USN,低16位是SLOT,就知道是3号回滚段,12号槽位
    

    关键点:TX锁持有者锁模式是6,请求的锁模式是4

    4. 场景三:ITL争用问题

    环境:

    SQL> create table test(a int) pctfree 0 initrans 1;
    
    Table created.
    
    begin
      for i in 1..2000 loop
        insert into test values(i);
      end loop;
    end;
    /
    commit;
    

    查看数据分布情况

    select file#, block#, count(1) from (
      select dbms_rowid.rowid_relative_fno(rowid) as file#,
             dbms_rowid.rowid_block_number(rowid) as block#
        from test )
    group by file#, block#;
    
         FILE#     BLOCK#   COUNT(1)
    ---------- ---------- ----------
             1      95595        532
             1      95594        734
             1      95593        734
    
    select a, dbms_rowid.rowid_relative_fno(rowid) as file#,
              dbms_rowid.rowid_block_number(rowid) as block#
      from test
     where rownum < 10;
    
             A      FILE#     BLOCK#
    ---------- ---------- ----------
             1          1      95593
             2          1      95593
             3          1      95593
             4          1      95593
             5          1      95593
             6          1      95593
             7          1      95593
             8          1      95593
             9          1      95593
    
     9 rows selected.
    

    会话1:

    SQL> update test set a = 10001 where a = 1;
    
     1 row updated.
    

    会话2:

    SQL> update test set a = 10002 where a = 2;
    
     1 row updated.
    

    会话3:

    SQL> update test set a = 10003 where a = 3;
    

    会话3挂起

    set linesize 180
    column event format a30
    column blocking_instance format 9999 heading 'Blocker|Instance'
    column blocking_session format 9999 heading 'Blocker|Session'
    select s.sid,
           s.EVENT,
           s.P1,
           s.P2,
           s.P3,
           s.blocking_instance,
           s.blocking_session,
           l.TYPE,
           l.ID1,
           l.ID2,
           l.LMODE,
           l.REQUEST
      from v$lock l, v$session s
     where l.SID = s.SID
       and l.type = 'TX'
      order by s.sid;
    
                                                                                Blocker Blocker
           SID EVENT                                  P1         P2         P3 Instance Session TY        ID1        ID2      LMODE    REQUEST
    ---------- ------------------------------ ---------- ---------- ---------- -------- ------- -- ---------- ---------- ---------- ----------
            65 SQL*Net message from client    1650815232          1          0                  TX     131090       1836          6          0
           426 SQL*Net message from client    1650815232          1          0                  TX     655385       5428          6          0
           427 enq: TX - allocate ITL entry   1415053316     655385       5428        1     426 TX     655385       5428          0          4
    

    挂起的会话,请求的TX锁模式是4,并且等待事件是enq: TX – allocate ITL entry。
    查看ITL信息:

    SQL> alter system checkpoint;        
    
    System altered.
    
    SQL> oradebug setmypid
    Statement processed.
    SQL> alter system dump datafile 1 block 95593;
    
    System altered.
    
    SQL> oradebug tracefile_name
    /data/oracle/diag/rdbms/t12c/t12c/trace/t12c_ora_28312.trc
    
     Object id on Block? Y
     seg/obj: 0x16dae  csc: 0x00.414373  itc: 2  flg: -  typ: 1 - DATA
         fsl: 0  fnx: 0x0 ver: 0x01
     
     Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x0002.012.0000072c  0x010002f7.016a.15  ----    1  fsc 0x0000.00000000
    0x02   0x000a.019.00001534  0x01001052.0566.04  ----    1  fsc 0x0000.00000000
    

    5. 场景4:对主键或唯一键INSERT或UPDATE相同记录

    环境准备:

    SQL> create table t1 as select * from dba_objects;
    
    Table created.
    
    SQL> create unique index idx_t1_object_id on t1(object_id);
    
    Index created.
    

    会话一:

    SQL> select distinct sid from v$mystat;
    
           SID
    ----------
           243
    
    SQL> insert into t1(object_id, object_name) values(100000, 'a');
    
     1 row created.
    

    会话二:
    “`
    SQL> select distinct sid from v$mystat;

    SID
    ———-
    3

    SQL> insert into t1(object_id, object_name) values(100000, ‘b’);
    “`
    会话二挂起

    set linesize 180
    column event format a30
    column blocking_instance format 9999 heading 'Blocker|Instance'
    column blocking_session format 9999 heading 'Blocker|Session'
    select s.sid,
           s.EVENT,
           s.P1,
           s.P2,
           s.P3,
           s.blocking_instance,
           s.blocking_session,
           l.TYPE,
           l.ID1,
           l.ID2,
           l.LMODE,
           l.REQUEST
      from v$lock l, v$session s
     where l.SID = s.SID
       and l.type = 'TX'
      order by s.sid;
    
                                                                                Blocker Blocker
           SID EVENT                                  P1         P2         P3 Instance Session TY        ID1        ID2      LMODE    REQUEST
    ---------- ------------------------------ ---------- ---------- ---------- -------- ------- -- ---------- ---------- ---------- ----------
             3 enq: TX - row lock contention  1415053316     655382       2397        1     243 TX     655382       2397          0          4
             3 enq: TX - row lock contention  1415053316     655382       2397        1     243 TX     589835       1068          6          0
           243 SQL*Net message from client    1650815232          1          0                  TX     655382       2397          6          0
    

    关键点:请求的TX锁模式是4

    关于紫砂壶

    感悟技术人生
    此条目发表在Oracle性能问题分类目录,贴了, 标签。将固定链接加入收藏夹。