ORA-00060:在多线程系统中更新多行时等待资源时检测到死锁

Question

早些时候，我使用了这个查询，逐个更新了100行。

String query="update table set status=? where  rownum< ?";
stmt = conn.prepareStatement(query);
for(int i=0; i < CHUNK_SIZE;i++){
    stmt.setString(1, "STATUS_IN_PROGRESS");
    stmt.setInt(2, 2);
    stmt.executeUpdate();
    }

现在，我更改了这个查询，以便一次更新所有行。

String query="update table set status=? where  rownum< ?";
stmt = conn.prepareStatement(query);
stmt.setString(1, "STATUS_IN_PROGRESS");
stmt.setInt(2, CHUNK_SIZE);

但后面的一个给出了"ORA-00060:等待资源时检测到死锁"异常。我读过这篇文章，但我不明白，如果异常是因为竞争的DML，那么它也应该发生在第一个查询中，尽管概率较低，但事实并非如此。

此外，我们通常在数据库级别进行频繁的更新，所以这应该是一个频繁的问题，但事实并非如此。

Answer 1

正如Alex Poole所建议的，您肯定想要查找跟踪文件。每个死锁都会在数据库上创建一个单独的跟踪文件。该文件列出了所有相关的对象和SQL语句。不要以为你知道是哪些语句导致了死锁，死锁有几种奇怪的发生方式。

正如ibre5041所指出的，死锁取决于检索数据的顺序。然而，简单地添加一个ORDER BY可能没有帮助。具有相同执行计划的同一语句将始终以相同的顺序返回数据（在实践中，但这并不能保证！）。但在某些情况下，同一条SQL语句可能有不同的执行计划。例如，如果CHUNK_SIZE绑定变量不同，则可能导致执行计划发生更改。查找SQL语句、检查多个执行计划并尝试修复一个计划可能会有所帮助。此查询可以帮助您查找报表和计划：

select sql_id, plan_hash_value from gv$sql where lower(sql_text) like '%table_name%';

自动提交可以解释为什么第一个版本没有导致错误。死锁需要两个事务，并且其中至少有一个事务必须执行了工作，然后尝试执行更多的工作。一个交易必须持有一个锁并要求另一个锁。如果事务是在每一行之后提交的，那么就不可能发生死锁。然而，我并不提倡单行处理。

解决方案通常是模式修复（在外键上添加索引，将位图索引转换为b-tree索引）、控制对表的访问（序列化访问或至少确保语句按相同顺序处理），以及作为最后手段使用异常处理。