Discussions

EJB programming & troubleshooting: ORA-02049: timeout: distributed transaction waiting for lock

  1. App server: WebSphere 6 Datasource: XA datasource We recently migrated from Weblogic to WebSphere and here is the problem we are getting now We have a Container managed entity bean and a Stateless session bean. From the session bean we are doing a findByPrimarykey on the entity bean using the localhome. We are getting ORA-02049: timeout: distributed transaction waiting for lock everytime it does a findByPrimarykey any ideas?
  2. It is possible that you WAS container is configured to on a default basis fetch data using "Select...FOR UPDATE;", and your WebLogic app server did not. If so, you suddenly have a new aspect of concurrency issues you did not experience previosly. First, use your Oracle administration tools to check which process that acutally has a lock on the data instance. If you find out that you application "locks itself", you need to either rewrite your sql execution order or do some more configuring (if possible). /Niklas
  3. Thanks Niklas for your response. Yes It looks like performing select for update. Here is the stacktrace. javax.ejb.TransactionRolledbackLocalException: ; nested exception is: java.sql.SQLException: ORA-02049: timeout: distributed transaction waiting for lock DSRA0010E: SQL State = 42000, Error Code = 2,049 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1972) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584) at com.ibm.ws.rsadapter.cci.WSResourceAdapterBase.pmiExecuteQuery(WSResourceAdapterBase.java:409) at com.ibm.ws.rsadapter.cci.WSResourceAdapterBase.executeQuery(WSResourceAdapterBase.java:342)
  4. I found this from WAS Info center. I am adding this here. It might help "If an entity bean performs a findByPrimaryKey (which by default obtains a 'Read' lock in the database), " Excerpt from Info center Database deadlocks caused by lock upgrades To avoid databse deadlocks caused by lock upgrades, you can change the access intent policy for entity beans from the default of wsPessimisticUpdate-WeakestLockAtLoad to wsPessimisticUpdate or can use an optimistic locking approach. When accessing data in a database concurrently, an application must be aware of and prepared for database locking that must occur to insure the integrity of the data. If an entity bean performs a findByPrimaryKey (which by default obtains a 'Read' lock in the database), and the entity bean is updated within the same transaction, then a lock upgrade (to 'Exclusive') occurs. If this scenario occurs on multiple threads concurrently, then a deadlock can happen. This is because multiple 'Read' locks can be obtained concurrently, but one 'Exclusive' lock can be obtained only when all other locks have been dropped. Because all transactions are attempting the lock upgrade in this scenario, this one 'Exclusive' lock can never be obtained . To avoid this problem, you can change the access intent policy for the entity bean from the default of wsPessimisticUpdate-WeakestLockAtLoad to wsPessimisticUpdate. This change in access intent enables the application to inform WebSphere and the database that the transaction will update the enterprise bean, and so an 'Update' lock is obtained immediately on the findByPrimaryKey. This avoids the lock upgrade when the update is performed later.