Oracle数据库 ORA-00054 错误分析和解决

Oracle数据库ORA-00054错误解析

原创于 2025-09-16 12:10:43 发布 · 1.2k 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

AI辅助_ORA-错误合集专栏收录该内容

358 篇文章

订阅专栏

在这里插入图片描述
好的，我们来详细解析一个与Oracle数据库锁机制密切相关的常见错误：ORA-00054。

ORA-00054: 资源正忙，但指定以 NOWAIT 方式获取资源

1. 错误信息结构组成

ORA-00054错误信息的标准格式如下：

ORA-00054: 资源正忙，但指定以 NOWAIT 方式获取资源

或英文版：

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

这个错误信息由两部分组成：

ORA-00054：错误代码，唯一标识这种错误类型。
“资源正忙，但指定以 NOWAIT 方式获取资源”：错误描述，明确指出问题的本质——会话尝试获取一个已被其他会话持有的资源，但指定了NOWAIT选项，因此不愿等待而立即返回错误。

2. 官方正式解释

原因

ORA-00054错误发生在以下两种情况：

会话明确使用NOWAIT子句请求一个资源（如锁），但该资源已被其他会话以不兼容模式持有。
会话使用WAIT子句指定了超时时间，但在该时间内未能获得所需资源。

这是一种主动的、非阻塞的并发控制机制。应用程序通过指定NOWAIT，表明它不愿意等待资源释放，而是希望立即知道资源是否可用，以便采取替代操作。

可能场景

显式使用NOWAIT子句：

SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE NOWAIT;

DDL操作遇到锁定对象：尝试对正在被使用的对象执行DDL操作（如ALTER TABLE、DROP TABLE等）。

使用WAIT超时：

SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE WAIT 5; -- 等待5秒

应用程序设计：应用程序使用NOWAIT策略来处理潜在的锁冲突，实现更细粒度的并发控制。
高并发环境：在高度并发的OLTP系统中，热点数据经常被访问和修改。

3. 问题诊断与分析过程

定位原因

当遇到ORA-00054错误时，诊断的核心是找出是什么资源被谁占用，以及为什么占用者持有该资源。

检查错误上下文：确定是哪个SQL语句触发了错误，以及是否明确使用了NOWAIT或WAIT子句。
识别被阻塞的会话：找到发出请求的会话。
识别阻塞会话：找到持有所需资源的会话。

诊断SQL查询

查询当前被阻塞的会话及其阻塞者（最常用）

SELECT
    w.sid                 AS waiting_sid,
    w.serial#             AS waiting_serial,
    w.username            AS waiting_user,
    w.event               AS wait_event,
    w.seconds_in_wait     AS sec_in_wait,
    w.sql_id              AS waiting_sql_id,
    b.sid                 AS blocking_sid,
    b.serial#             AS blocking_serial,
    b.username            AS blocking_user,
    b.status              AS blocking_status,
    b.machine             AS blocking_machine,
    b.program             AS blocking_program,
    b.sql_id              AS blocking_sql_id,
    sq.sql_text           AS blocking_sql_text
FROM
    v$session w
JOIN
    v$session b ON w.blocking_session = b.sid
LEFT JOIN
    v$sql sq ON b.sql_id = sq.sql_id
WHERE
    w.blocking_session IS NOT NULL
ORDER BY
    w.seconds_in_wait DESC;

查询具体的锁信息和被锁对象

SELECT
    lo.session_id        AS sid,
    s.serial#,
    s.username,
    s.program,
    s.osuser,
    lo.oracle_username,
    lo.locked_mode,
    do.object_name,
    do.object_type,
    do.owner
FROM
    v$locked_object lo
JOIN
    dba_objects do ON lo.object_id = do.object_id
JOIN
    v$session s ON lo.session_id = s.sid
ORDER BY
    lo.session_id, do.object_name;

查询当前所有锁的详细信息

SELECT 
    l.sid,
    s.serial#,
    s.username,
    s.program,
    l.type,
    l.id1,
    l.id2,
    l.lmode,
    l.request,
    l.ctime,
    l.block
FROM 
    v$lock l
JOIN 
    v$session s ON l.sid = s.sid
WHERE 
    l.type != 'TM'  -- 排除表锁，专注于更具体的锁
ORDER BY 
    l.sid, l.type;

查找特定对象的锁等待情况

SELECT 
    o.object_name,
    o.object_type,
    l.session_id,
    s.serial#,
    s.username,
    s.program,
    l.locked_mode
FROM 
    v$locked_object l
JOIN 
    dba_objects o ON l.object_id = o.object_id
JOIN 
    v$session s ON l.session_id = s.session_id
WHERE 
    o.object_name = 'YOUR_TABLE_NAME'  -- 替换为实际的表名
ORDER BY 
    o.object_name, l.session_id;

4. 解决方案

短期解决方案（紧急处理）

提交或回滚阻塞会话：最直接的解决办法是让持有锁的会话提交（COMMIT）或回滚（ROLLBACK）其事务。

-- 1. 首先使用诊断SQL找到blocking_sid和blocking_serial
-- 2. 联系该会话的用户，请求其提交或回滚事务

-- 3. 如果无法联系用户或情况紧急，DBA可以强制终止阻塞会话
ALTER SYSTEM KILL SESSION '<blocking_sid>, <blocking_serial>';

-- 或者使用IMMEDIATE选项强制终止
ALTER SYSTEM KILL SESSION '<sid>, <serial#' IMMEDIATE;

重试机制：在应用程序中实现重试逻辑，当遇到ORA-00054时等待一段时间后重试操作。

BEGIN
    FOR retry IN 1..3 LOOP  -- 最多重试3次
        BEGIN
            SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE NOWAIT;
            EXIT;  -- 成功则退出循环
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE = -54 THEN  -- ORA-00054
                    DBMS_LOCK.SLEEP(2);  -- 等待2秒后重试
                ELSE
                    RAISE;  -- 其他错误直接抛出
                END IF;
        END;
    END LOOP;
END;

长期解决方案（根本解决）

优化应用程序设计：
- 缩短事务：让事务尽可能短小，尽快提交或回滚，减少锁持有时间。
- 访问顺序：确保所有应用程序以相同的顺序访问资源，避免死锁。
- 使用适当的隔离级别：根据业务需求选择合适的事务隔离级别。
- 批量处理优化：对于大批量操作，考虑使用批量处理技术，减少锁竞争。

使用SELECT FOR UPDATE SKIP LOCKED：

-- 跳过已被锁定的行，只处理可用行
SELECT * FROM employees 
WHERE department_id = 10 
AND ROWNUM <= 10 
FOR UPDATE SKIP LOCKED;

数据库设计优化：
- 分区技术：使用分区表将数据分散，减少热点块争用。
- 索引优化：确保有合适的索引，减少需要锁定的数据量。
- 避免全表扫描：优化查询避免不必要的全表扫描，减少锁范围。

监控和预警：

设置监控脚本，定期检查锁等待情况。
建立预警机制，当锁等待时间过长时发出警报。

-- 监控长时间锁等待的脚本
SELECT sid, serial#, username, event, seconds_in_wait, blocking_session
FROM v$session 
WHERE event LIKE 'enq%' 
AND seconds_in_wait > 60  -- 等待超过60秒
ORDER BY seconds_in_wait DESC;

5. 通俗易懂的解释

可以把ORA-00054错误想象成：你想使用一个公共卫生间，发现门锁着（资源被占用）。你敲了敲门，里面有人回应"正在使用！“。因为你很着急或者明确表示不愿意等待（NOWAIT），所以你立即离开并说"算了，我找别的”（立即返回错误）。

详细比喻：

数据库表/行：那个卫生间。
锁（Lock）：卫生间的门锁。
阻塞会话（Blocking Session）：卫生间里面正在使用的人。
被阻塞会话（Waiting Session）：想使用卫生间的你。
NOWAIT选项：你敲敲门，但明确表示"如果不马上能用，我就走"。
ORA-00054错误：里面的人回应"正在使用"，你立即放弃并离开。

为什么会发生？

里面的人正在使用卫生间（事务未提交）。
你明确表示不愿意等待（使用了NOWAIT）。
或者你愿意等几分钟，但里面的人超过这个时间还没出来（WAIT超时）。

与类似错误的区别：

ORA-00051：你愿意等待，但在外面等了非常长的时间（比如15分钟），最终放弃（等待超时）。
ORA-00060：deadlock（死锁）——你和另一个等待的人都在互相瞪眼，并且你们都一只手抓着同一个卫生间的门把手，另一只手抓着另一个卫生间的门把手，谁也不松手，形成了一个僵局。

怎么解决？

短期：敲门请里面的人快点（终止阻塞会话）。
长期：
- 建议大家上厕所要快进快出（优化应用程序，缩短事务）。
- 多建几个卫生间（从设计上减少资源争用）。
- 建立排队系统但允许人们选择是否等待（使用SKIP LOCKED）。
- 在门口放个计时器（实施监控和预警）。

应用程序如何处理？
在代码中，你可以这样处理ORA-00054：

// Java伪代码示例
for (int retry = 0; retry < maxRetries; retry++) {
    try {
        // 尝试获取锁，但不等待
        executeQuery("SELECT * FROM table FOR UPDATE NOWAIT");
        break; // 成功则退出循环
    } catch (SQLException e) {
        if (e.getErrorCode() == 54) { // ORA-00054
            Thread.sleep(2000); // 等待2秒后重试
        } else {
            throw e; // 其他错误直接抛出
        }
    }
}