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

原创于 2025-09-16 12:16:18 发布 · 1.4k 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

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

358 篇文章

订阅专栏

在这里插入图片描述
好的，我们来详细解析一个与Oracle数据库并发控制密切相关的经典错误：ORA-00060。

ORA-00060: 死锁检测等待资源

1. 错误信息结构组成

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

ORA-00060: 死锁检测等待资源

或英文版：

ORA-00060: deadlock detected while waiting for resource

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

ORA-00060：错误代码，唯一标识这种错误类型。
“死锁检测等待资源”：错误描述，明确指出问题的本质——数据库检测到了会话之间的死锁情况。

2. 官方正式解释

原因

ORA-00060错误发生在Oracle数据库的死锁检测机制发现两个或多个会话之间存在循环等待条件时。每个会话都持有另一个会话所需的资源，同时等待对方释放自己需要的资源，从而形成无法自行解决的僵局。

Oracle会定期运行内部死锁检测算法。一旦检测到死锁，它会选择一个会话作为"牺牲者"，回滚该会话的当前语句，释放其持有的锁，并抛出ORA-00060错误，从而打破死锁让其他会话可以继续工作。

可能场景

不同顺序的DML操作：两个会话以相反的顺序更新相同的多张表。
- 会话1: 更新表A → 试图更新表B
- 会话2: 更新表B → 试图更新表A
位图索引并发操作：多个会话同时对有位图索引的表进行DML操作。
外键约束无索引：存在外键约束但缺少索引，导致表级锁而非行级锁。
应用程序设计缺陷：应用程序逻辑导致资源以冲突的顺序被请求。
递归SQL操作：一个操作触发了递归SQL，这些SQL与另一个会话的操作冲突。

3. 问题诊断与分析过程

定位原因

当遇到ORA-00060错误时，诊断的核心是分析死锁的详细信息，确定哪些资源和会话参与了死锁。

检查警报日志（Alert Log）：警报日志会记录死锁发生的摘要信息，但详细情况需要查看跟踪文件。
```
-- 找到警报日志的位置
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
```
分析跟踪文件：Oracle会自动生成详细的跟踪文件（.trc），其中包含死锁的完整信息，包括参与会话、SQL语句、等待资源等。
使用数据字典查询：可以查询相关视图了解当前的锁情况。

诊断SQL查询

查询死锁历史信息

SELECT snap_id, begin_interval_time, instance_number, deadlocks
FROM dba_hist_snapshot
WHERE deadlocks > 0
ORDER BY snap_id DESC;

查看当前锁等待链（用于预防性分析）

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.program             AS blocking_program,
    b.sql_id              AS blocking_sql_id
FROM
    v$session w
JOIN
    v$session b ON w.blocking_session = b.sid
WHERE
    w.blocking_session IS NOT NULL
ORDER BY
    w.seconds_in_wait DESC;

查询详细的锁信息

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    l.type,
    l.id1,
    l.id2,
    l.lmode,
    l.request,
    l.ctime,
    o.owner,
    o.object_name,
    o.object_type
FROM
    v$lock l
JOIN
    v$session s ON l.sid = s.sid
LEFT JOIN
    dba_objects o ON l.id1 = o.object_id
WHERE
    l.type IN ('TM', 'TX')
ORDER BY
    l.ctime DESC;

4. 解决方案

短期解决方案

自动处理：Oracle已经自动选择了牺牲者会话并回滚了其当前操作。需要：
- 通知用户他们的操作被回滚了
- 让应用程序重新执行被回滚的操作
- 检查数据一致性（如果需要）
分析死锁原因：使用跟踪文件信息分析根本原因，防止复发。

长期解决方案

应用程序设计优化：
- 统一访问顺序：确保所有应用程序以相同的顺序访问资源。
```
// 正确：总是先访问表A，再访问表B
update TableA set ... where ...;
update TableB set ... where ...;

// 错误：不同的访问顺序会导致死锁风险
// 会话1: A -> B
// 会话2: B -> A
```
- 缩短事务：减少事务持续时间和锁持有时间。
- 使用NOWAIT或WAIT：在适当的地方使用SELECT ... FOR UPDATE NOWAIT或SELECT ... FOR UPDATE WAIT n。
- 批量处理优化：对于大批量操作，使用批量处理技术。

数据库设计优化：

外键索引：为所有外键列创建索引，避免表级锁。

-- 检查缺少索引的外键
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = 'R'
AND (table_name, constraint_name) NOT IN (
    SELECT table_name, constraint_name
    FROM user_constraints cons
    WHERE constraint_type = 'R'
    AND EXISTS (
        SELECT 1
        FROM user_ind_columns cols
        WHERE cols.table_name = cons.table_name
        AND cols.column_name IN (
            SELECT cols.column_name
            FROM user_cons_columns cols
            WHERE cols.constraint_name = cons.constraint_name
        )
    )
);

避免位图索引：在高并发DML环境中避免使用位图索引。
适当分区：使用分区技术减少热点块争用。

监控和预警：

设置死锁监控脚本
建立死锁发生时的预警机制

-- 死锁监控查询
SELECT name, value
FROM v$sysstat
WHERE name = 'enqueue deadlocks';

应用程序中的死锁处理

在应用程序中实现适当的死锁处理逻辑：

// Java伪代码示例
int maxRetries = 3;
int retryCount = 0;
boolean success = false;

while (!success && retryCount < maxRetries) {
    try {
        // 执行数据库操作
        executeDatabaseTransaction();
        success = true;
    } catch (SQLException e) {
        if (e.getErrorCode() == 60) { // ORA-00060
            retryCount++;
            if (retryCount >= maxRetries) {
                throw new RuntimeException("操作失败，超过最大重试次数", e);
            }
            // 等待一段时间后重试
            Thread.sleep(1000 * retryCount);
        } else {
            throw e; // 其他异常直接抛出
        }
    }
}

5. 通俗易懂的解释

可以把ORA-00060错误想象成：两辆汽车在一条狭窄的单行道上迎面相遇，每辆车都需要对方后退才能通过，但谁也不愿意先退让，结果双方都无法前进，形成了交通僵局。

详细比喻：

Oracle数据库：就像城市交通系统。
数据行/表：就像道路上的不同车道或路口。
锁：就像汽车占用的道路空间。
会话：就像行驶中的汽车。
ORA-00060错误：相当于交通警察（Oracle）发现了两辆迎面相遇的汽车（死锁），警察要求其中一辆车（牺牲者）倒车退让（回滚操作），以便另一辆车可以通过（继续工作），并对退让的司机说：“你遇到了交通死锁，请倒车让行”。

为什么会发生？