CODEWITHSUNDEEP
databaseoracle-database
prakhar3agrwal
prakhar3agrwal

Reputation: 346

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

Earlier I used this query where I was updating 100 rows one by one.

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();
    }

Now I changed this query to update all the rows at once.

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

But the later one is giving " ORA-00060: deadlock detected while waiting for resource" exception. I read about this but I am not able to understand that if the exception is because of competing DMLs then it should have happened in the first query also though with a lower probability but that was non the case.

Also we normally do frequent updates at DB level so it should be a frequent problem but this is not the case.

Upvotes: 2

Views: 4853

Answers (1)

Jon Heller
Jon Heller

Reputation: 36832

As Alex Poole suggested, you definitely want to look for the trace file. Every deadlock creates a separate trace file on the database. The file lists all the related objects and SQL statements. Don't assume you know which statements are causing the deadlock, there are several weird ways that deadlocks can happen.

As ibre5041 pointed out, deadlocks depend on the order in which data is retrieved. However, simply adding an ORDER BY likely won't help. The same statement with the same execution plan will always return data in the same order (in practice, but this is not guaranteed!). But the same SQL statement can have different execution plans in some cases. For example, if the CHUNK_SIZE bind variable is different that could cause a change in the execution plan. It might help to find the SQL statements and check for multiple execution plans, and try to fix one plan. This query can help you find statements and plans:

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

Auto-commit might explain why the first version did not cause errors. Deadlocks require two transactions and at least one of them must have performed work and then tried to do more work. A transaction must hold a lock and ask for another one. If the transaction is committed after every single row there is no way for a deadlock to occur. However, I'm not advocating single-row processing.

The solution is usually either a schema fix (add an index on a foreign key, convert a bitmap index to a b-tree index), control access to a table (serialize access or at least make sure the statements process in the same order), and as a last resort use exception handling.

Upvotes: 4

Related Questions