ORA-30006 resource busy acquire with WAIT timeout expired 报错的一般分析路径

问题描述

• OceanBase 数据库 Oracle 模式下遇到 ORA-30006 resource busy acquire with WAIT timeout expired 报错表示资源繁忙，无法获取，超时后等待失败。通常发生在一个会话尝试获取锁定资源时，但资源已被其他会话占用。此报错与以下情况可能有关。

  • 数据库中存在长时间运行的事务或查询，导致资源繁忙。

  • 锁定资源的事务即是持有锁的事务阻塞在某个操作上，导致资源无法释放。

  • 应用程序并发性不足，导致多个会话尝试同时获取同一资源。

• 举例 OceanBase 数据库 Oracle 模式下遇到 ORA-30006 resource busy acquire with WAIT timeout expired 报错的一般分析路径： 由于业务需要游标查询语句中必须使用 FOR UPDATE 选项，以便在打开游标时锁定游标结果集在表中所对应的数据行。有锁冲突，如业务有死锁的时，会导致事务回滚。

  语法如下：

  SELECT . . . FROM ... FOR UPDATE [OF column[, column]...] [NOWAIT]
  

  如果另一个会话已结果集中的行加了锁，那么 SELECT FOR UPDATE 操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上 NOWAIT 子句时，如果这些行被另一个会话锁定，客户端报错。

  ORA-30006: resource busy; acquire with WAIT timeout expired 
  

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

问题原因

在 OceanBase 数据库中有锁冲突，对于客户如果有业务有死锁的时候，会导致事务回滚。

解决方法

• 解决 OceanBase 数据库 Oracle 模式下遇到 ORA-30006 resource busy acquire with WAIT timeout expired 报错的常用方法。

  • 确认数据库中没有长时间运行的事务或查询，命令如下。

    obclient> SELECT * FROM __all_virtual_trans_lock_stat ORDER BY ctx_create_time limit 8\G;
    

  • 确认锁定资源事务即是持有锁的事务阻塞在某个操作上，导致资源无法释放，并解决问题，命令如下。

    SELECT * FROM __all_virtual_trans_lock_stat WHERE trans_id like '%xxxx%'\G;
    

    确认持有锁事务 session_id 后，停止对应事务的会话，kill 相关进程。

    [admin@hostname]$ kill session_id
    

  • 优化应用程序并发性，以避免多个会话同时获取同一资源。

  • 修改等待事务超时时间，以便在超时前获取资源，命令如下。

    obclient> SET @@ob_trx_timeout = 100000000;
    

    或：

    obclient> SET @@global.ob_trx_timeout = 100000000;
    

    说明

    修改事务超时时间，参见《OceanBase 数据库参考指南》中的《系统配置项》章节。

• 针对业务需要游标查询语句中必须使用 FOR UPDATE 选项导致ORA-30006 resource busy acquire with WAIT timeout expired 报错的解决方法。

  采用 FOR UPDATE 声明游标，则可在 DELETE 和 UPDATE 语句中使用 WHERE CURRENT OF cursor_name 子句修改或删除游标结果集合当前行对应的数据库表中的数据行。示例如下。

  obclient> SELECT empname, salary FROM emp WHERE deptno=4;
  

  输出结果如下。

  +---------+-----------+
  | EMPNAME | SALARY    |
  +---------+-----------+
  | Karen     |  10000  |
  | Guy       |  10000  |
  +---------+-----------+
  

  采用FOR UPDATE 声明游标，存储过程如下。

  obclient> DECLARE
   V_deptno emp.deptno%TYPE :=4;
   CURSOR c_emp IS SELECT empname, salary
   FROM emp WHERE deptno=V_deptno FOR UPDATE NOWAIT;
   BEGIN
   FOR emp_record IN c_emp LOOP
       IF emp_record.salary < 12000 THEN
       UPDATE emp SET salary=12000 WHERE CURRENT OF c_emp;
       END IF;
       END LOOP;
       END;
   /
  

  执行如下语句。

  obclient> SELECT empname, salary FROM emp WHERE deptno=4;
  

  输出结果如下。

  +---------+-----------+
  | EMPNAME | SALARY    |
  +---------+-----------+
  | Karen   |  12000    |
  | Guy     |  12000    |
  +---------+-----------+