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

即使脚本同时在同一表上执行更新,他们也在由WHERE子句确定的表的不同记录上执行更新,它们之间没有记录的重叠.

这会导致错误吗?

无论在桌子上执行更新的位置,此错误是否会发生?.

我是否应该始终避免在表上进行并发更新?

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

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

预先感谢.

推荐答案

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

过去,我通过对平行性进行工程化的方式来解决这一问题,以至于不同的实例正在处理工作负载的某些部分,而这些工作负载不太可能影响彼此靠近的块;例如,要更新大表格,而不是使用MOD(n,10)之类的东西来设置并行从属,我会使用TRUNC(n/10),这意味着每个从属都可以在连续的数据集上工作.

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

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

其他推荐答案

我最近在遇到类似问题的挣扎.事实证明,数据库缺少外国密钥上的索引.这导致Oracle锁定了比要求更多的记录,这在高并发期间迅速导致了死锁.

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

其他推荐答案

我也遇到了这个问题.我不知道实际发生的事情的技术细节.但是,在我的情况下根本原因是Oracle数据库中存在级联删除设置,而我的JPA/Hibernate代码也试图进行级联删除呼叫.因此,我的建议是确保您确切知道发生了什么.

本文地址:https://www.itbaoku.cn/post/597644.html

问题描述

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.