Unanswered: ORA-02049: timeout: distributed transaction waiting for lock
In our application we are facing an issue in oracle 10g. When our application is stress tested, it is found that Row Lock contention happens in a specific table. When it is tested with more users, it is found that more users are waiting to update the same table, probably a row which is found to be locked and as a result multiple users has to wait for long time to update the same table, the last user in the queue has to wait for the longest time, and hence a time out is resulted in the transaction.
This is the exception :
java.sql.SQLException: ORA-02049: timeout: distributed transaction waiting for lock
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java: 743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStateme nt.java:207)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4C Statement.java:946)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:1168)
at oracle.jdbc.driver.OracleStatement.executeUpdateIn ternal(OracleStatement.java:1614)
at oracle.jdbc.driver.OracleStatement.executeUpdate(O racleStatement.java:1579)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecu teUpdate(WSJdbcStatement.java:1235)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeU pdate(WSJdbcStatement.java:738)
One way to resolve this is to make the method as synchronized, which has the logic to update the table, so one user will access the method at a time, in this way we can prevent the table in being used by multiple users. But this is not a good design decision, is there any other way of resolving this issue. Please help me.
They way to fix this is to have your application make the change then commit. A row lock will only occur when a row has been updated and another user also want to update the SAME row. This is an application error, not an oracle error.
You do not need a parachute to skydive. You only need a parachute to skydive twice.