行锁问题

最新推荐文章于 2024-11-01 04:00:00 发布

原创最新推荐文章于 2024-11-01 04:00:00 发布 · 125 阅读

0 ·

CC 4.0 BY-SA版权

背景：后管系统前端页面页面修改信息，一直卡在进度条不动。查看后管日志，发现报错事务隔离。于是新插入一条记录，然后修改成功。因此怀疑是行锁问题。

查询行锁

-- 查询行锁相关信息
select t2.username,
       t2.sid,
       t2.serial#,
       t3.object_name,
       t2.OSUSER,
       t2.MACHINE,
       t2.PROGRAM,
       t2.LOGON_TIME,
       t2.COMMAND,
       t2.LOCKWAIT,
       t2.SADDR,
       t2.PADDR,
       t2.TADDR,
       t2.SQL_ADDRESS,
       t1.LOCKED_MODE
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
 order by t2.logon_time;

解除行锁

alter system kill session ‘sid,seial#’;

若存在大量行锁，可采用游标批量删除

declare curosr mycur is
    select  t2.sid, t2.serial# from v$locked_object t1, v$session t2, dba_objects t3
        where t1.session_id = t2.sid and t1.object_id = t3.object_id
        
 begin
    for cur in mycur
    loop
        execute immediate ('alter system kill session '''||cur.sid || ','|| cur.serial# ||''' ');
    end loop;
 end;