This discussion is archived
8 Replies Latest reply: Sep 7, 2012 5:23 PM by dsurber RSS

Deadlock in Oracle.sql.ARRAY type

931157 Newbie
Currently Being Moderated
We've come across the deadlock situation below when running multiple J2EE MDB instances that are trying to write to the DB:

[deadlocked thread] [ACTIVE] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)':
Thread '[ACTIVE] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)'' is waiting to acquire lock 'oracle.jdbc.driver.T4CConnection@90106ee' that is held by thread '[ACTIVE] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (self-tuning)''

Stack trace:
oracle.sql.ARRAY.toBytes(ARRAY.java:673)
oracle.jdbc.driver.OraclePreparedStatement.setArrayCritical(OraclePreparedStatement.java:5985)
oracle.jdbc.driver.OraclePreparedStatement.setARRAYInternal(OraclePreparedStatement.java:5944)
oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8782)
oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8278)
oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8868)
oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:240)
weblogic.jdbc.wrapper.PreparedStatement.setObject(PreparedStatement.java:287)
org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:356)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:127)
org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:298)
org.springframework.jdbc.object.BatchSqlUpdate$1.setValues(BatchSqlUpdate.java:192)
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:892)
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:883)
org.springframework.jdbc.object.BatchSqlUpdate.flush(BatchSqlUpdate.java:184)
com.csfb.fao.rds.rfi.common.dao.storedprocs.SaveEarlyExceptionBatchStoredProc.execute(SaveEarlyExceptionBatchStoredProc.java:93)
com.csfb.fao.rds.rfi.common.dao.EarlyExceptionDAOImpl.saveEarlyExceptionBatch(EarlyExceptionDAOImpl.java:34)
com.csfb.fao.rds.rfi.application.rulesengine.RulesEngine.saveEarlyExceptions(RulesEngine.java:302)
com.csfb.fao.rds.rfi.application.rulesengine.RulesEngine.executeRules(RulesEngine.java:209)
com.csfb.fao.rds.rfi.application.rulesengine.RulesEngine.onMessage(RulesEngine.java:97)
com.csfb.fao.rds.feeds.process.BaseWorkerMDB.onMessage(BaseWorkerMDB.java:518)
weblogic.ejb.container.internal.MDListener.execute(MDListener.java:466)
weblogic.ejb.container.internal.MDListener.transactionalOnMessage(MDListener.java:371)
weblogic.ejb.container.internal.MDListener.onMessage(MDListener.java:327)
weblogic.jms.client.JMSSession.onMessage(JMSSession.java:4547)
weblogic.jms.client.JMSSession.execute(JMSSession.java:4233)
weblogic.jms.client.JMSSession.executeMessage(JMSSession.java:3709)
weblogic.jms.client.JMSSession.access$000(JMSSession.java:114)
weblogic.jms.client.JMSSession$UseForRunnable.run(JMSSession.java:5058)
weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:516)
weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

and...

[deadlocked thread] [ACTIVE] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (self-tuning)':
Thread '[ACTIVE] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (self-tuning)'' is waiting to acquire lock 'oracle.jdbc.driver.T4CConnection@b48b568' that is held by thread '[ACTIVE] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)''

Stack trace:
oracle.sql.ARRAY.toBytes(ARRAY.java:673)
oracle.jdbc.driver.OraclePreparedStatement.setArrayCritical(OraclePreparedStatement.java:5985)
oracle.jdbc.driver.OraclePreparedStatement.setARRAYInternal(OraclePreparedStatement.java:5944)
oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8782)
oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8278)
oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8868)
oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:240)
weblogic.jdbc.wrapper.PreparedStatement.setObject(PreparedStatement.java:287)
org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:356)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:127)
org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:298)
org.springframework.jdbc.object.BatchSqlUpdate$1.setValues(BatchSqlUpdate.java:192)
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:892)
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:883)
org.springframework.jdbc.object.BatchSqlUpdate.flush(BatchSqlUpdate.java:184)
com.csfb.fao.rds.rfi.common.dao.storedprocs.SaveEarlyExceptionBatchStoredProc.execute(SaveEarlyExceptionBatchStoredProc.java:93)
com.csfb.fao.rds.rfi.common.dao.EarlyExceptionDAOImpl.saveEarlyExceptionBatch(EarlyExceptionDAOImpl.java:34)
com.csfb.fao.rds.rfi.application.rulesengine.RulesEngine.saveEarlyExceptions(RulesEngine.java:302)
com.csfb.fao.rds.rfi.application.rulesengine.RulesEngine.executeRules(RulesEngine.java:209)
com.csfb.fao.rds.rfi.application.rulesengine.RulesEngine.onMessage(RulesEngine.java:97)
com.csfb.fao.rds.feeds.process.BaseWorkerMDB.onMessage(BaseWorkerMDB.java:518)
weblogic.ejb.container.internal.MDListener.execute(MDListener.java:466)
weblogic.ejb.container.internal.MDListener.transactionalOnMessage(MDListener.java:371)
weblogic.ejb.container.internal.MDListener.onMessage(MDListener.java:327)
weblogic.jms.client.JMSSession.onMessage(JMSSession.java:4547)
weblogic.jms.client.JMSSession.execute(JMSSession.java:4233)
weblogic.jms.client.JMSSession.executeMessage(JMSSession.java:3709)
weblogic.jms.client.JMSSession.access$000(JMSSession.java:114)
weblogic.jms.client.JMSSession$UseForRunnable.run(JMSSession.java:5058)
weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:516)
weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

Looking at the ARRAY.toBytes() method:
      public byte[] toBytes()
        throws SQLException
      {
        synchronized (getInternalConnection())
        {
          return this.descriptor.toBytes(this, this.enableBuffering);
        }
      }
..., it synchronizes on the following method (getInternalConnection() -> getPhysicalConnection()):
      oracle.jdbc.internal.OracleConnection getPhysicalConnection()
      {
        if (this.physicalConnection == null)
        {
          try
          {
            this.physicalConnection = ((oracle.jdbc.internal.OracleConnection)new OracleDriver().defaultConnection());
          }
          catch (SQLException localSQLException)
          {
          }
    
        }
    
        return this.physicalConnection;
      }
defaultConnection() does the following:
      public Connection defaultConnection()
        throws SQLException
      {
        if ((defaultConn == null) || (defaultConn.isClosed()))
        {
          synchronized (OracleDriver.class)
          {
            if ((defaultConn == null) || (defaultConn.isClosed()))
            {
              defaultConn = connect("jdbc:oracle:kprb:", new Properties());
            }
          }
        }
    
        return defaultConn;
      }
So there's synchronizations on the connection instance and OracleDriver.class object... I can't see how this can deadlock. To get to the point of needing the lock on OracleDriver.class, the thread would already have the lock on the connection instance.... clearly I'm missing something.

We are creating the ARRAY type using the following code:
     public ARRAY getOracleLongArray(DataSource ds, List<Long> vals, String typeName)
               throws SQLException {
          oracle.jdbc.OracleConnection conn = (oracle.jdbc.OracleConnection) ds
                    .getConnection();
          Object[] data = vals.toArray(new Object[0]);

          ARRAY mddArray = conn.createARRAY(typeName, data);
          conn.close(); // Close the extra connection made here.
          return mddArray;
     }
Thanks

Edited by: 928154 on 17-Apr-2012 05:17
  • 1. Re: Deadlock in Oracle.sql.ARRAY type
    Joe Weinstein Expert
    Currently Being Moderated
    Arrays are 'live' objects, not simple things. They are associated with the connection that made them.
    The problem will be solved when you ensure that only one thread at a time uses a given JDBC
    connection or any of it's subobjects. You can't pass Array objects around like strings.
  • 2. Re: Deadlock in Oracle.sql.ARRAY type
    931157 Newbie
    Currently Being Moderated
    The issue is that the ARRAY type (or at least the descriptor) doesn't return the connection to the pool once it has been used.

    We very quickly run out of connections to our db.

    There is a free() method on the ARRAY type but there doesn't appear to be any code attached...
    How do we get the instances to release their connections?
  • 3. Re: Deadlock in Oracle.sql.ARRAY type
    gimbal2 Guru
    Currently Being Moderated
    928154 wrote:
    The issue is that the ARRAY type (or at least the descriptor) doesn't return the connection to the pool once it has been used.
    That seems backward to me. The ARRAY is not responsible for returning the connection to the pool, you are. If connections are leaked, your application code is doing it.
  • 4. Re: Deadlock in Oracle.sql.ARRAY type
    931157 Newbie
    Currently Being Moderated
    Agreed.
    We were using a Connection pool but passing DataSources - every time we created a New ARRAY object it was getting a different Connection object and thus creating a new db connection.
  • 5. Re: Deadlock in Oracle.sql.ARRAY type
    957332 Newbie
    Currently Being Moderated
    I am getting similar issue. I am using connection pool but since we need to pass connection when create ARRAY, we are running out of connections and resources waiting on each other getting threads to deadlock/wait.
    Did you find any answer on how to release connections to pool?

    Appreciate the help.
  • 6. Re: Deadlock in Oracle.sql.ARRAY type
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    I am getting similar issue.
    >
    Then you should create your own thread and post the issue there. As you can see this thread has already been marked ANSWERED.

    Post the code you are using and describe the problem you are having, the results you expect to get and the actual results you are getting.

    Also provide the JDK version, JDBC jar version, Oracle DB version (full 4 digit version number) and post the code you are using.
  • 7. Re: Deadlock in Oracle.sql.ARRAY type
    dsurber Explorer
    Currently Being Moderated
    The fundamental problem is that ARRAYs are live objects. The belong to a specific connection and hold a reference to that connection. Best practice is to only use ARRAYs with the connection that created them. You can get away with passing ARRAYs from one connection to another if the connections have exactly the same schema and if you assure that the thread doing the passing is the only thread accessing either of the two connections. In general this is hard to accomplish and rather pointless. So use ARRAYs only with the connection that created them.

    When you send an ARRAY created on one connection to another connection you can and frequently will get this deadlock.
  • 8. Re: Deadlock in Oracle.sql.ARRAY type
    dsurber Explorer
    Currently Being Moderated
    Same for STRUCTs, BLOBs, CLOBs, and BFILEs. These are live objects and should only be used with the connection that created them.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points