database ORA-00060: 等待资源时检测到死锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3074827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00060: deadlock detected while waiting for resource
提问by wowrt
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,
我有一系列脚本作为 nohup 在托管 oracle 10g 的 AIX 服务器上并行运行。这些脚本是由其他人编写的，旨在并发执行。所有脚本都在表上执行更新。我收到错误，
ORA-00060: deadlock detected while waiting for resource
ORA-00060: 等待资源时检测到死锁
As I googled for this, I found, http://www.dba-oracle.com/t_deadly_perpetual_embrace_locks.htm
当我搜索这个时，我发现， 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 WHEREclause with no overlaps of records between them.
即使脚本同时在同一个表上执行更新，它们也会对由WHERE子句确定的表的不同记录执行更新，它们之间没有记录重叠。
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 completedafter the above quoted error.
奇怪的是我也在 nohup.out 日志上发现
PL/SQL successfully completed了上面引用的错误之后。
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.
这是否意味着 oracle 已从死锁中恢复并成功完成更新，还是我应该连续重新运行这些脚本？欢迎任何帮助。
Thanks in advance.
提前致谢。
采纳答案by Jeffrey Kemp
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.
您不仅可以在行锁上获得死锁，例如，请参阅this。脚本可能会竞争其他资源，例如索引块。
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.
过去，我通过以这样一种方式设计并行性来解决这个问题，即不同的实例正在处理不太可能影响彼此靠近的块的部分工作负载；例如，对于大表的更新MOD(n,10)，我不会使用类似的方法设置并行从站，而是使用TRUNC(n/10)这意味着每个从站处理一组连续的数据。
There are, of course, much better ways of splitting up a job for parallelism, e.g. DBMS_PARALLEL_EXECUTE.
当然，还有更好的方法来拆分作业以实现并行化，例如DBMS_PARALLEL_EXECUTE。
Not sure why you're getting "PL/SQL successfully completed", perhaps your scripts are handling the exception?
不知道为什么你会得到“PL/SQL 成功完成”，也许你的脚本正在处理异常？
回答by benvolioT
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.
我最近正在努力解决类似的问题。结果是数据库缺少外键索引。这导致 Oracle 锁定的记录比所需的多得多，这在高并发期间很快导致死锁。
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
这是一篇出色的文章，其中包含许多关于如何修复死锁的详细信息、建议和详细信息：http: //www.oratechinfo.co.uk/deadlocks.html#unindex_fk
回答by DaShaun
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.
我也遇到了这个问题。我不知道实际发生的事情的技术细节。但是，在我的情况下，根本原因是 Oracle 数据库中设置了级联删除，而我的 JPA/Hibernate 代码也在尝试执行级联删除调用。所以我的建议是确保你确切地知道发生了什么。
回答by Daniel Segura
I was testing a function that had multiple UPDATEstatements within IF-ELSEblocks.
我正在测试一个UPDATE在IF-ELSE块内有多个语句的函数。
I was testing all possible paths, so I reset the tables to their previous values with 'manual' UPDATEstatements each time before running the function again.
我正在测试所有可能的路径，因此UPDATE每次在再次运行该函数之前，我都使用“手动”语句将表重置为以前的值。
I noticed that the issue would happen just after those UPDATEstatements;
我注意到这个问题会在这些UPDATE陈述之后发生；
I added a COMMIT;after the UPDATEstatement I used to reset the tables and that solved the problem.
我COMMIT;在UPDATE用于重置表格的语句之后添加了一个，这解决了问题。
So, caution, the problem was not the function itself...
所以，请注意，问题不在于功能本身......
