database - ORA-00060 : deadlock detected while waiting for resource while updating multiple rows in a multi-threaded system

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:WAITING资源时检测到死锁”异常。我读到了 this但我无法理解，如果异常是由于竞争 DML 引起的，那么它也应该在第一个查询中发生，尽管概率较低，但事实并非如此。

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

Answer 1

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

正如 ibre5041 所指出的，死锁取决于检索数据的顺序。但是，简单地添加 ORDER BY 可能无济于事。具有相同执行计划的相同语句将始终以相同的顺序返回数据(在实践中，但这并不能保证!)。但在某些情况下，同一条 SQL 语句可以有不同的执行计划。例如，如果 CHUNK_SIZE 绑定(bind)变量不同，可能会导致执行计划发生变化。它可能有助于找到 SQL 语句并检查多个执行计划，并尝试修复一个计划。此查询可以帮助您查找报表和计划:

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

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

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