WHCSRL 技术网

Oracle锁阻塞问题排查步骤

 1、查看当前会话持有锁和等待锁的情况:
 select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,DECODE (BLOCK, 0, '', 'blocker') blocker,DECODE (request, 0, '', 'waiter') waiter from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request>0) order by blocker;
 
 4717持有TX锁没有释放,导致其他会话无法提交
   INST_ID        SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK BLOCKER               WAITER
---------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ---------- --------------------- ------------------
         1       4717 TX        2293793       1056          6          0      11554          1 blocker
         1       7079 TX        2293793       1056          0          6       1828          0                       waiter
         1       2358 TX        2293793       1056          0          6         60          0                       waiter
         1       2368 TX        2293793       1056          0          6       1405          0                       waiter
         1       3308 TX        2293793       1056          0          6       1288          0                       waiter
         1       3310 TX        2293793       1056          0          6       1745          0                       waiter
         1       3784 TX        2293793       1056          0          6       1648          0                       waiter
         1       4253 TX        2293793       1056          0          6       2068          0                       waiter
         1       4715 TX        2293793       1056          0          6        991          0                       waiter
         1       5183 TX        2293793       1056          0          6       1152          0                       waiter
         1       6136 TX        2293793       1056          0          6        928          0                       waiter
         1       6605 TX        2293793       1056          0          6        620          0                       waiter
         1       7075 TX        2293793       1056          0          6       1938          0                       waiter
         1         11 TX        2293793       1056          0          6        288          0                       waiter
         1        955 TX        2293793       1056          0          6        208          0                       waiter
         
2、查看4717 会话内容:
SQL> select sid,serial#,BLOCKING_SESSION,LOGON_TIME,CLIENT_INFO,sql_id,prev_sql_id from v$session where sid=4717;

       SID    SERIAL# BLOCKING_SESSION LOGON_TIME          CLIENT_INFO                                                                                                                                           SQL_ID                                   PREV_SQL_ID
---------- ---------- ---------------- ------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------- ---------------------------------------
      4717      59863                  2021-10-30 12:55:43                                                                                                                                                       2hrwpbzt38zcz

SQL> select sql_text from v$sql where sql_id='2hrwpbzt38zcz';

SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------
update tpaws_lock_info   set is_lock= 'N',   lock_timeout= sysdate,   updated_by = 'sysadmin',   updated_date = sysdate   where lock_name = :1  and is_lock= 'Y'

21 rows selected.

SQL>

3、查看4717会话到底持有了那些锁:

SQL> select * from v$lock where sid=4717;

ADDR             KADDR                   SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
000000097BBCC278 000000097BBCC2D0       4717 AE            100          0          4          0      12219          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89205          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89163          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89221          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89222          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89224          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89223          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89067          0          3          0      11897          0
00007F4C437A92E0 00007F4C437A9340       4717 TM          89151          0          3          0      11897          0
0000000966621218 0000000966621290       4717 TX        2293793       1056          6          0      11897          1


ID1,ID2:  ID1,ID2的取值含义根据type的取值而有所不同,对于TM 锁ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息,ID2 值为0;
对于TX 锁,这两个字段构成该事务在回滚段中的位置。


4、查看其他等待锁的会话,在申请什么锁:
SQL>  select * from v$lock where request=6 and type='TX';

ADDR             KADDR                   SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
000000097BBCD4F0 000000097BBCD548       5191 TX        2293793       1056          0          6        676          0
000000097BBCDA48 000000097BBCDAA0       6136 TX        2293793       1056          0          6       1649          0
000000097BBD7EF0 000000097BBD7F48        483 TX        2293793       1056          0          6        569          0
000000097BBD7470 000000097BBD74C8       3781 TX        2293793       1056          0          6        510          0
000000097BBD1388 000000097BBD13E0       4715 TX        2293793       1056          0          6       1712          0
000000097BBD52E8 000000097BBD5340        955 TX        2293793       1056          0          6        929          0
000000097BBD27C0 000000097BBD2818       5183 TX        2293793       1056          0          6       1873          0
000000097BBD78D0 000000097BBD7928       2358 TX        2293793       1056          0          6        781          0
000000097BBCECC0 000000097BBCED18         11 TX        2293793       1056          0          6       1009          0
000000097BBD18C8 000000097BBD1920       6605 TX        2293793       1056          0          6       1341          0
000000097BBCCED0 000000097BBCCF28        479 TX        2293793       1056          0          6        389          0
000000097BBD1468 000000097BBD14C0       4250 TX        2293793       1056          0          6         87          0
000000097BBCBD38 000000097BBCBD90       1416 TX        2293793       1056          0          6        328          0
000000097BBD7E10 000000097BBD7E68       5662 TX        2293793       1056          0          6        450          0

如上可以看到都要获取回滚段中ID1=2293793,ID2=1056 这个事务对应内容的TX锁

5、再看看回滚段中这个记录是啥内容呢:
把id1转换十六进制,高2字节代表回滚段编号,低两字节是回滚段槽位号

sys@PROD@137> SELECT TO_CHAR(2293793,'XXXXXXXX') FROM DUAL;

TO_CHAR(2293793,'XXXXXXXX')
---------------------------
   230021

SQL> select to_number('23','XX') USN#,to_number('0021','xxxx') wrap# from dual;

      USN#      WRAP#
---------- ----------
        35         33

SQL>  select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
        32         21       1038
         7          8       8439
        27         26       1049
        28          0       1131
         4         15       8487
        39         29        450
        36         23       1162
        40          5        339
         6         25       8872
        41         10        307
        29         21       1075
        26         15        955
         5         21       8682
        35         33       1056
        38         33       1212

15 rows selected.

 
可以看到这个事务,是13:01分开始的

SQL> select * from v$transaction where XIDSQN=1056;

ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS                                           START_TIME                                                   START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR             FLAG SPACE     RECURSIVE NOUNDO    PTX
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --------- --------- --------- ---------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DATE           DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID            PRV_XID          PTX_XID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
0000000966621218         35         33       1056          3       1146        150          1 ACTIVE                                           10/30/21 13:01:04                                              15100675                0          2            3         1145          150           46 0000000973383848       3587 NO        NO        NO        NO

         0          0          0          0          0          0          0          0          2         18         87          0         17          0 2021-10-30 13:01:04          0          0   15100675   0 2300210020040000 0000000000000000 0000000000000000

UBAFIL:回滚段文件号;---3

UBABLK:数据块号;---1146

UBASQN:回滚序列号;

UBAREC:回滚记录号。

这些数据就对应了数据块中的Uba。

Dump出数据块和回滚块后具体分析:

SQL> oradebug setmypid;
Statement processed.
SQL> alter system dump datafile 3 block 1146;

System altered.

SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/njybjdbsnbx1/njybjdbsnbx1/trace/njybjdbsnbx1_ora_11908.trc

在trace文件njybjdbsnbx1_ora_11908.trc中找到如下内容:

UNDO BLK:
xid: 0x0023.021.00000420  seq: 0x96  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f68

*-----------------------------
* Rec #0x1  slt: 0x21  objn: 89067(0x00015beb)  objd: 89067  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00c00479
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c00479.0096.3d
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0140037f  hdba: 0x0140037a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 1 ckix: 0
ncol: 8 nnew: 4 size: 0
col  2: [ 1]  59
col  3: [ 7]  78 79 0a 1e 0e 02 0f
col  6: [ 8]  73 79 73 61 64 6d 69 6e
col  7: [ 7]  78 79 0a 1e 0e 02 05

End dump data blocks tsn: 2 file#: 3 minblk 1146 maxblk 1146

89067 是对应的对象名字
SQL>  select object_id,object_name from  dba_objects  where object_id=89067


 OBJECT_ID OBJECT_NAME
---------- ----------------------------------------
     89067 TPAWS_LOCK_INFO

如下代表对应的列字段:

col  2: [ 1]  59  
col  3: [ 7]  78 79 0a 1e 0e 02 0f
col  6: [ 8]  73 79 73 61 64 6d 69 6e
col  7: [ 7]  78 79 0a 1e 0e 02 05

使用如下的方法看这些记录的具体内容:
SQL> set serveroutput on;
SQL> declare n varchar2(100);
  2  begin
  3  dbms_stats.convert_raw_value('59',n);
  4  dbms_output.put_line(n);
  5  end;
  6  /
Y

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> declare n DATE;
  2  begin
  3  dbms_stats.convert_raw_value('78790a1e0e020f',n);
  4  dbms_output.put_line(n);
  5  end;
  6  /
2021-10-30 13:01:14

PL/SQL procedure successfully completed.


SQL> set serveroutput on;
SQL> declare n varchar2(100);
  2  begin
  3  dbms_stats.convert_raw_value('73797361646d696e',n);
  4  dbms_output.put_line(n);
  5  end;
  6  /
sysadmin

PL/SQL procedure successfully completed.

SQL>

SQL> set serveroutput on;
SQL> declare n date;
  2  begin
  3  dbms_stats.convert_raw_value('78790a1e0e0205',n);
  4  dbms_output.put_line(n);
  5  end;
  6  /
2021-10-30 13:01:04

PL/SQL procedure successfully completed.

SQL>
 

推荐阅读