ORA-00060: 在等待资源时检测到死锁[英] ORA-00060: deadlock detected while waiting for resource

本文是小编为大家收集整理的关于ORA-00060: 在等待资源时检测到死锁的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。


我有一系列脚本并联在托管Oracle 10G的AIX服务器上作为NOHUP.这些脚本是由其他人编写的,应同时执行.所有脚本都在表上执行更新.我遇到了错误,

ORA-00060:检测到僵局 等待资源

当我谷歌搜索时,我发现, http://wwwww.dba-oracle.com/t_dead _deadeadly_deadeadly_perpetal_embrace_embrace_locks.hocks.hops_locks.htm





奇怪的是,我也在nohup.out日志上找到 PL/SQL successfully completed上述引用错误之后.

这是否意味着Oracle已从僵局中恢复并成功完成更新,还是我应该连续重新运行这些脚本? 欢迎任何帮助.



您不仅可以在行锁上获得死锁,例如请参阅 this .脚本可能正在争夺其他资源,例如索引块.


当然,有更好的方法来分配并行性的工作,例如 dbms_paralle_execute .

不确定为什么要获得" PL/SQL成功完成",也许您的脚本正在处理例外?



这是一篇出色的文章,有很多细节,建议和有关如何解决僵局的细节: http://wwwww.oratechinfo.co.uk/deadlock/deadlocks.html#unindindectemex_fk





I have a series of scripts running in parallel as a nohup on an AIX server hosting oracle 10g. These scripts are written by somebody else and are meant to be executed concurrently. All the scripts are performing updates on a table. I am getting the error,

ORA-00060: deadlock detected while waiting for resource

As I googled for this, I found, http://www.dba-oracle.com/t_deadly_perpetual_embrace_locks.htm

Even though the scripts are performing updation on the same table concurrently, they are performing updates on different records of the table determined by the WHERE clause with no overlaps of records between them.

So would this have caused the error?.

Will this error happen regardless of where the updates are performed on a table?.

Should I avoid concurrent updates on a table at all times?.

Strangely I also found on the nohup.out log, PL/SQL successfully completed after the above quoted error.

Does this mean that oracle has recovered from the deadlock and completed the updates successfully or Should I rerun those scripts serially? Any help would be welcome.

Thanks in advance.


You can get deadlocks on more than just row locks, e.g. see this. The scripts may be competing for other resources, such as index blocks.

I've gotten around this in the past by engineering the parallelism in such a way that different instances are working on portions of the workload that are less likely to affect blocks that are close to each other; for example, for an update of a large table, instead of setting up the parallel slaves using something like MOD(n,10), I'd use TRUNC(n/10) which mean that each slave worked on a contiguous set of data.

There are, of course, much better ways of splitting up a job for parallelism, e.g. DBMS_PARALLEL_EXECUTE.

Not sure why you're getting "PL/SQL successfully completed", perhaps your scripts are handling the exception?


I was recently struggling with a similar problem. It turned out that the database was missing indexes on foreign keys. That caused Oracle to lock many more records than required which quickly led to a deadlock during high concurrency.

Here is an excellent article with lots of good detail, suggestions, and details about how to fix a deadlock: http://www.oratechinfo.co.uk/deadlocks.html#unindex_fk


I ran into this issue as well. I don't know the technical details of what was actually happening. However, in my situation, the root cause was that there was cascading deletes setup in the Oracle database and my JPA/Hibernate code was also trying to do the cascading delete calls. So my advice is to make sure that you know exactly what is happening.