Wednesday, November 17, 2010

Deadlock from sorted IN clause

Problem:
Recently we see deadlock in Oracle db caused by a simple IN delete clause (the input parameters are alphabetically sorted). The SQL looks like
DELETE FROM TEST_TABLE WHERE ID IN (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10);

Stack Trace:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the BatchDeleteUserContextByCred-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.inet.ora.Ora4SQLException: [Oracle] #600 ORA-00060: deadlock detected while waiting for resource
[Oracle] #600
        at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.delete(SqlMapExecutorDelegate.java:528)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.delete(SqlMapSessionImpl.java:98)
        at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.delete(SqlMapClientImpl.java:75)

Issue Analysis:
These Global Enqueue Services Deadlock detected messages are indicating that there is row level contention and locking issue caused by the application code. These errors are exactly the same as those seen in an ORA-0060 deadlock scenario except and these messages are indicating the RAC database has detected the deadlock and forced the rollback of a transaction as a result of the deadlock scenario. There is no Oracle RAC issue here, this needs to be addressed at an application coding level.

Why deadlock:
Simply put, two or more guys do the same tasks, there will be conflict. Two delete SQL will have the same problem though they are sorted like below (no official explanation of this, but the guess is Oracle will do SELECT before delete, without sequence guaranteed. )
DELETE FROM TEST_TABLE WHERE ID IN ('A','B','C');
DELETE FROM TEST_TABLE WHERE ID IN ('A','B','C');

Why we did "batch delete using IN clause"?
  1. One by one delete has performance issue - due to redo/undo log, replication cost
  2. Huge batch delete has issue too - causes rollback segment or data-files related errors.

Solution:
  1. Move the cleanup task to DB job
  2. Use JDBC batch delete (multiple deletes with one commit, using addBatch(), executeBatch() etc)

No comments:

Post a Comment