Oracle 19c查询被锁定行时生成错误ROWID问题咨询

根因说明 #

你当前查询得到的ROWID不正确，核心原因是v$session视图的ROW_WAIT_*系列字段，仅会记录正处于行锁等待状态的会话对应的等待资源信息。对于已经持有行锁、处于闲置/执行其他操作的持锁会话，这些字段不会存储它所持有的行锁对应的块号、行号，直接使用持锁会话的该类字段生成ROWID必然出错。

完整解决方案 #

第一步：获取持锁会话的基础关联信息 #

先通过以下查询拿到持锁用户、终端信息、锁持有时长、被锁对象信息：

select 
  obj.owner as 锁对象属主,
  obj.object_name as 锁对象名,
  obj.object_type as 锁对象类型,
  lck.session_id as 持锁会话ID,
  sess.username as 数据库用户名,
  sess.osuser as 操作系统用户名,
  sess.machine as 终端机器名,
  sess.terminal as 终端名,
  sess.program as 客户端程序名,
  sess.logon_time as 会话登录时间,
  round((sysdate - sess.logon_time)*24*60,2) as 会话已存在分钟数,
  decode(lck.locked_mode,
    0,'无锁',1,'空锁',2,'行共享',3,'行排他',4,'共享',5,'共享行排他',6,'全表排他',
    lck.locked_mode) as 锁模式
from v$locked_object lck
left join dba_objects obj on lck.object_id = obj.object_id
left join v$session sess on lck.session_id = sess.sid
order by sess.logon_time desc;

第二步：精准定位被锁的行记录 #

分两种场景选择对应方法：

场景1：当前有业务会话正在等待该锁（即业务侧已经出现请求卡住/报错）

此时等待锁的会话的ROW_WAIT_*字段是准确的，直接关联等待会话的信息生成正确ROWID即可：

select
  -- 持锁会话信息
  holder_sess.sid as 持锁会话ID,
  holder_sess.username as 持锁数据库用户,
  holder_sess.machine as 持锁终端机器,
  -- 被锁行信息
  wait_sess.row_wait_obj# as 被锁对象ID,
  obj.owner as 被锁表属主,
  obj.object_name as 被锁表名,
  dbms_rowid.rowid_create(
    1, 
    wait_sess.row_wait_obj#, 
    wait_sess.row_wait_file#, 
    wait_sess.row_wait_block#, 
    wait_sess.row_wait_row#
  ) as 被锁行正确ROWID
from v$lock holder_lock
join v$session holder_sess on holder_lock.sid = holder_sess.sid
join v$lock wait_lock on holder_lock.id1 = wait_lock.id1 and holder_lock.id2 = wait_lock.id2
join v$session wait_sess on wait_lock.sid = wait_sess.sid
left join dba_objects obj on wait_sess.row_wait_obj# = obj.object_id
where holder_lock.type = 'TX' 
  and holder_lock.lmode > 0 -- 持锁方
  and wait_lock.request > 0 -- 等待方
  and wait_sess.row_wait_obj# > -1;

拿到ROWID后，直接通过select * from 被锁表名 where rowid = '上一步得到的ROWID'即可查询到被锁的具体记录内容。

场景2：当前无等待会话，需要主动扫表查出所有被锁行

如果当前没有会话在等待锁，可以通过for update skip locked特性快速筛选出被锁的行，该语法会跳过已经被加锁的记录，通过集合取反即可得到所有被锁行的ROWID：

-- 替换语句中的@表属主@、@表名@为你实际的被锁表信息
select rowid from @表属主@.@表名@
minus
select /*+ rowid */ rowid from @表属主@.@表名@ for update skip locked;

注意：如果表数据量极大，建议在语句中增加业务常用的过滤条件缩小扫描范围，避免对业务造成性能影响。

19c 特殊注意事项 #

如果你的数据库是多租户（CDB+PDB）架构，必须确保当前会话连接到了被锁对象所在的PDB执行查询，不要在CDB根容器下查询，否则dba_objects中拿到的object_id是CDB全局ID，不是PDB内的对象ID，会导致生成的ROWID错误。

内容的提问来源于stack exchange，提问作者bluefox