阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

使用DBMS_ROWID获取被阻塞行的rowid

213次阅读
没有评论

共计 4086 个字符,预计需要花费 11 分钟才能阅读完成。

在使用 v$session 视图在查询会话的行锁的等待事件时,视图中提供了会话等待的对象号 (ROW_WAIT_OBJ#)、文件号(ROW_WAIT_FILE#)、块号(ROW_WAIT_BLOCK#) 和行号 (ROW_WAIT_ROW#) 但是如何使用这些信息定位出会话等待的是哪一行呢?答案就是使用 DBMS_ROWID

打开两个会话同时更新同一条数据

#session 1
linuxidc@ORCL>select distinct sid from v$mystat;
 
      SID
———-
    22
 
linuxidc@ORCL>
zx@ORCL>update zx set name=’zx’ where id=1;
 
1 row updated.
 
#session 2
linuxidc@ORCL>select distinct sid from v$mystat;
 
      SID
———-
      145
       
linuxidc@ORCL>update zx set name=’zx’ where id=1;

此时 session2 会被 session1 阻塞,查询 v$session 会话 145 在等待 enq: TX – row lock contention

linuxidc@ORCL>col event for a40
linuxidc@ORCL>select SID,EVENT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=145;
 
      SID EVENT                    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
———- —————————————- ————- ————– ————— ————-
      145 enq: TX – row lock contention          99754        18    15571      7

查询 v$lock 确认会话 145 在请求会话 22 的 TX 锁

linuxidc@ORCL>select sid,type,id1,id2,lmode,request from v$lock where sid=145 or sid=22 order by 1;
 
      SID TYPE    ID1      ID2      LMODE    REQUEST
———- —— ———- ———- ———- ———-
    22 AE      100        0  4    0
    22 TM        99754      0  3    0
    22 TX      4390915      581    6    0
      145 TM          99754      0  3    0
      145 TX        4390915      581    0    6
      145 AE        100        0  4    0

使用如下语句查询会话 145 等待哪个表的哪个行

linuxidc@ORCL>col owner for a10
linuxidc@ORCL>col object_name for a10
linuxidc@ORCL>col rowid for a30
linuxidc@ORCL>select b.owner,b.object_name,dbms_rowid.rowid_create(1,s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE#,s.ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) “rowid” from v$session s,dba_objects b where s.ROW_WAIT_OBJ#=b.object_id and s.si
d=145;
OWNER      OBJECT_NAM rowid
———- ———- ——————————
ZX    ZX          AAAYWqAASAAADzTAAH
– 使用上面查询出的 rowid 查看数据,即为 session2 等待的行
linuxidc@ORCL>select * from zx.zx where rowid=’AAAYWqAASAAADzTAAH’;
 
    ID NAME
———- ——————————
    1 ZX

官方文档:http://docs.Oracle.com/cd/E11882_01/appdev.112/e40758/d_rowid.htm#ARPLS053

使用下面语句查找会话之间的阻塞关系

 SELECT (‘ 节点 ’ || a.inst_id || ‘ session ‘ || a.sid || ‘,’ || a_s.serial# ||
      ‘ 阻塞了节点 ’ || b.inst_id || ‘ session ‘ || b.sid || ‘,’ || b_s.serial#) blockinfo,
      a.inst_id,
      a_s.sid,
      a_s.schemaname,
      a_s.module,
      a_s.status,
      a_s.event,
      a.type lock_type,
      a.id1,
      a.id2,
      decode(a.lmode,
              0,
              ‘none’,
              1,
              NULL,
              2,
              ‘row-S(SS)’,
              3,
              ‘row-X(SX)’,
              4,
              ‘share(S)’,
              5,
              ‘S/Row-X(SSX)’,
              6,
              ‘exclusive(X)’) lock_mode,
      a.ctime time_hold,
      ‘ 后为被阻塞信息 ’ remark_flag,
      b.inst_id blocked_inst_id,
      b.sid blocked_sid,
      b.type blocked_lock_type,
      decode(b.request,
              0,
              ‘none’,
              1,
              NULL,
              2,
              ‘row-S(SS)’,
              3,
              ‘row-X(SX)’,
              4,
              ‘share(S)’,
              5,
              ‘S/Row-X(SSX)’,
              6,
              ‘exclusive(X)’) blocked_lock_request,
      b.ctime time_wait,
      b_s.schemaname blocked_schemaname,
      b_s.module blocked_module,
      b_s.status blocked_status,
      b_s.sql_id blocked_sql_id,
      b_s.event,
      obj.owner blocked_owner,
      obj.object_name blocked_name,
      obj.object_type blocked_object_type,
      CASE
        WHEN b_s.row_wait_obj# <> -1 THEN
          dbms_rowid.rowid_create(1,
                                  obj.data_object_id,
                                  b_s.row_wait_file#,
                                  b_s.row_wait_block#,
                                  b_s.row_wait_row#)
        ELSE
          ‘-1’
      END blocked_rowid, – 被阻塞数据的 rowid
      decode(obj.object_type,
              ‘TABLE’,
              ‘select * from ‘ || obj.owner || ‘.’ || obj.object_name ||
              ‘ where rowid=”’ ||
              dbms_rowid.rowid_create(1,
                                      obj.data_object_id,
                                      b_s.row_wait_file#,
                                      b_s.row_wait_block#,
                                      b_s.row_wait_row#) || ””,
              NULL) blocked_data_querysql
  FROM gv$lock    a,
      gv$lock    b,
      gv$session  a_s,
      gv$session  b_s,
      dba_objects obj
 WHERE a.id1 = b.id1
  AND a.id2 = b.id2
  AND a.block > 0 – 阻塞了其他人
  AND b.request > 0 –AND ((a.INST_ID=b.INST_ID AND a.SID<>b.SID) OR (a.INST_ID<>b.INST_ID))
  AND a.sid = a_s.sid
  AND a.inst_id = a_s.inst_id
  AND b.sid = b_s.sid
  AND b.inst_id = b_s.inst_id
  AND b_s.row_wait_obj# = obj.object_id(+)
 ORDER BY a.inst_id, a.sid;

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-02/140511.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4086字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中