【原创】如何找到Oracle中哪条记录被锁

时间：2024-03-20 19:35:37

通常有这种情况，某个表或者准确的说是表的某条记录被锁（TX锁），在业务层面排查之余，一般都会想知道是哪条记录被锁，每次被锁的是否是同一条记录？还是每次都不同？通过记录可以找到这条记录可以在哪个模块、哪个业务流程中被操作到，有助于定位问题。
但是思前想后，好像不大好找，oracle的锁机制不同于一些数据库，oracle没有一个集中式的锁管理器，oracle的记录锁(行级排他锁存在于数据块上)，只有事务到达那一行的时候才能知道这行是否被锁。举个例子，现在科技大厦的物业需要统计C座每层公司的门是不是都上锁了，那么有两种办法：

1.物业有一张清单，每次某个公司上锁都要告诉物业一声：我的门锁了！于是物业记录一笔，当公司开门后，再通知物业：我开锁了！物业于是把之前记录清掉。

2.物业派业务员每层去查看，公司的门是不是上锁。

很显然，第一种方式可能最直观。方便，不足的是需要额外的资源去维护这个清单（锁管理器），随着行级锁数量的升级，这个锁管理器的开销会越来越大，对于这种数据库来说，锁就是一种稀缺资源。
oracle采用的方式是第二种，就是没有一个锁管理器，事务想锁定哪条记录，直接到达该记录，然后上锁，不过上锁之前都要检查该记录是不是被锁定了(数据块的事务槽记录着事务、锁信息)，如果被锁，则事务被阻塞，直到锁被释放。所以，对于oracle来说，1个锁和1亿个锁的开销是一样的。所以锁对于oracle来说算不得什么稀缺资源，所以oracle的事务默认是不自动提交的。
所以，在oracle的内存结构、数据字典和V$视图中都无法得到哪条记录被锁。
如果想知道哪条记录被锁，怎么办呢？很遗憾，我没有找到很好的办法，我遍历了ask tom的网站，tom也说没什么好办法，除非dump数据文件来分析。
不过我这里却想到一个比较笨但是感觉也算巧妙的方法，既然只有事务到达那一行的时候才能知道是否被锁，那么我可以使用一个游标遍历所有的行，然后尝试将该行以no wait的方式锁定，如果能锁定则ok，否则no wait会报错，那么我就可以捕获这个异常，知道是哪条记录被锁：

--记录锁探测器

--author:zhangxsh

--2013.5.13

create or replace procedure PR_LOCK_DETECT as

  can_not_lock exception;

  pragma exception_init(can_not_lock, -54);

  id number(14);

begin

  --注意：以主键的形式遍历

  for rec in (select n_hzbh from t_hztj_aj) loop

    begin

      id := rec.n_hzbh;

      select n_hzbh

        into id

        from t_hztj_aj w

       where w.n_hzbh = rec.n_hzbh

         for update nowait;

      rollback;

    exception

      when can_not_lock then

        dbms_output.put_line('记录：' || id || '被锁');

    end;

  end loop;

end;

测试：锁定9条记录：

select * from t_hztj_aj where rownum<10 for update

探测结果：

记录：10010000000088被锁

记录：10010000000217被锁

记录：10010000046875被锁

记录：10010000046883被锁

记录：10010000052098被锁

记录：10070000000295被锁

记录：10070000000570被锁

记录：10070000001005被锁

记录：10070000013248被锁

方法的不足：

1.由于for update会尝试在记录上加锁，如果锁定成功则会操作数据块使数据块变脏，生产环境下会产生额外的redo log，这个日志的量我没有测试，不过应该不大。

2.for update相当于一次轻量级的全表更新，表如果很大，则会产生相当量的块变脏情况，除了产生1的情况外，则oracle不会自动清除事务信息，下次select查询到来时会重启动事务清理操作，会瞬间产生大量的redo。

通过测试发现，执行这个存储过程，大致相当于将全表更新一遍，
执行存储过程探测时，生成的redo 量为33760000，而执行
update t_hztj_aj set n_bh=n_bh；生成的redo为44670000左右。
所以该探测的过程的代价约为全表更新代价的75%左右。由该例子也可以看出，不一定只要insert、update/delete语句才产生redo，select语句一样可以产生redo，而且数量还很大。所以每执行一遍这个存储过程基本等同于将全表更新一遍，对于生产来说，会产生大量的日志，除非迫不得已，否则最好不要使用。
同时，select * from tab for update也最好少用，测试发现这个操作会产生12000000左右的redo。
以上测试基于6W条数据左右的表进行。

这个帖子描述了TX锁和TM锁的一些区别和联系，tom说的很经典也很清楚
https://forums.oracle.com/forums/thread.jspa?messageID=2442603&tstart=0#2442603

You cannot actually in truth see ANY ROW LOCKS in Oracle - they are not manifested in a memory structure to be queried up.

--只存在于数据块上，因为没有一种内存结构或者机制去单独记录锁信息

相关文章