Oracle存储过程无法预防死锁,因无预检机制且死锁检测由后台异步触发;正确做法是显式捕获ORA-00060并实施带随机延迟、次数上限与回滚的可控重试。

Oracle存储过程本身不支持多线程,所谓“多线程并发”实际是多个数据库会话(session)同时调用同一存储过程——死锁发生在会话间资源争抢时,而非过程内部。处理核心是捕获 ORA-00060 并可控重试,不是预防或消除死锁。
为什么不能在存储过程中“预防”死锁
Oracle 没有提供死锁发生前的钩子或资源预检机制。死锁检测是 Oracle 后台进程(LMD/LCK)在事务等待超时后触发的异步行为,存储过程无法干预其发生逻辑。试图通过加锁顺序、索引优化等手段“预防”,属于应用层设计范畴,不改变存储过程内异常处理的本质需求。
常见误解包括:
- 在过程开头用
SELECT ... FOR UPDATE NOWAIT强制提前报错——这只能避免ORA-00054,对ORA-00060无效 - 用自治事务(
AUTONOMOUS_TRANSACTION)隔离逻辑——自治事务有自己的事务上下文,但无法解除主事务与其他会话的资源竞争 - 依赖
WHEN OTHERS统一捕获再判断SQLCODE——堆栈丢失,调试困难,且可能掩盖真实错误
必须显式捕获 ORA-00060 的 EXCEPTION 块
死锁唯一对应的错误码是 ORA-00060(SQLCODE = -60)。其他如 ORA-00054(资源忙)、ORA-01555(快照过旧)都不是死锁,混在一起处理会导致误判。
正确写法示例:
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- 处理唯一键冲突
NULL;
WHEN OTHERS THEN
IF SQLCODE = -60 THEN
DBMS_OUTPUT.PUT_LINE('Deadlock detected at ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
-- 进入重试逻辑(见下节)
GOTO retry_logic;
ELSE
RAISE; -- 非死锁异常必须原样抛出
END IF;
END;
关键点:
- 不使用
WHEN OTHERS THEN IF SQLCODE = -60包裹全部逻辑,否则所有异常都被吞掉 -
RAISE必须保留,否则调用方收不到非死锁错误,问题难定位 - 避免在
EXCEPTION中直接写业务更新语句,应跳转到重试入口统一处理
重试逻辑必须带随机延迟和次数上限
立即重试大概率再次死锁:两个会话同步执行相同 SQL,又按相同顺序申请资源,形成“重试共振”。Oracle 的死锁检测窗口很短(毫秒级),需人为错开重试时机。
推荐重试策略:
- 用
DBMS_RANDOM.VALUE(0, 0.1)生成 0–100 毫秒随机休眠,调用DBMS_LOCK.SLEEP - 最多重试 3 次,第 4 次直接
RAISE抛出原始ORA-00060,由调用方决定降级或告警 - 每次重试前用
ROLLBACK TO SAVEPOINT sp_start回滚到过程起点,防止部分 DML 残留
注意:SAVEPOINT 必须在事务开始后、业务逻辑前显式声明,不能依赖隐式事务边界。
容易被忽略的兼容性细节
不同 Oracle 版本对 DBMS_LOCK.SLEEP 权限、DBMS_RANDOM 初始化要求略有差异:
- Oracle 12c 及以后版本默认启用
DBMS_RANDOM,但低版本可能需先执行DBMS_RANDOM.INITIALIZE -
DBMS_LOCK包默认不授予 PUBLIC,需 DBA 显式授权给过程所属用户 - RAC 环境下,
DBMS_RANDOM.VALUE仍有效,但各节点时间戳微差不影响随机性 - 若过程被定义为
DETERMINISTIC,不可调用任何随机或休眠函数,需移除该关键字
最隐蔽的问题是:重试逻辑里忘记清除绑定变量状态或游标缓存,导致第二次执行时读到脏数据或报 ORA-01001。务必在每次重试前显式 CLOSE 游标、重置变量。











