Oracle锁阻塞问题排查步骤

最新推荐文章于 2024-05-08 09:25:04 发布

何加加

最新推荐文章于 2024-05-08 09:25:04 发布

阅读量777

点赞数

文章标签： oracle 数据库 database

本文链接：https://blog.csdn.net/weixin_42538000/article/details/121080219

版权

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>

何加加

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
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&g..
复制链接

扫一扫