Deadlocks while passing ARRAY to stored proc

Question

While sending ARRAY to the stord proc we are getting java level dead locks. I am attaching the thread dump.

    Found one Java-level deadlock:
    =============================
    "http-bio-8080-exec-11":
      waiting to lock monitor 0x00000000406fb2d8 (object 0x00000000fea1b130, a oracle.jdbc.driver.T4CConnection),
      which is held by "http-bio-8080-exec-4"
    "http-bio-8080-exec-4":
      waiting to lock monitor 0x00000000407d6038 (object 0x00000000fe78b680, a oracle.jdbc.driver.T4CConnection),
      which is held by "http-bio-8080-exec-11"

    Java stack information for the threads listed above:
    ===================================================
    "http-bio-8080-exec-11":
        at oracle.sql.TypeDescriptor.getName(TypeDescriptor.java:682)
        - waiting to lock <0x00000000fea1b130> (a oracle.jdbc.driver.T4CConnection)
        at oracle.jdbc.oracore.OracleTypeCOLLECTION.isInHierarchyOf(OracleTypeCOLLECTION.java:149)
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2063)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3579)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
        - locked <0x00000000fe78b680> (a oracle.jdbc.driver.T4CConnection)
        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
        - locked <0x00000000fe78b680> (a oracle.jdbc.driver.T4CConnection)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
        at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1066)
        at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1014)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)

How to avoid these kind of deadlocks.

Code : Class extends org.springframework.jdbc.object.StoredProcedure

Map result;
                Map hashMap = new HashMap();
                hashMap.put(SOME_IDS_PARAM, getJdbcTemplate().execute(new ConnectionCallback() {
                    @Override
                    public Object doInConnection(Connection con)
                            throws SQLException, DataAccessException {
                        Connection connection = new SimpleNativeJdbcExtractor().getNativeConnection(con);
                        ArrayDescriptor descriptor =   ArrayDescriptor.createDescriptor(schema + ".ARRAY_OF_NUMBER" , connection);
                        return new oracle.sql.ARRAY(descriptor, connection, someIds);
                    }
                }));
                result = super.execute(hashMap);

Even I tried with this approach:

OracleConnection connection = null;
    DataSource datasource = null;
    Map result;
    try {
        datasource = getJdbcTemplate().getDataSource();
        connection = (OracleConnection) DataSourceUtils.getConnection(datasource);
        synchronized (connection) {
            Map hashMap = new HashMap();
            hashMap.put(SOME_IDS_PARAM, getArrayOfNumberValue(someIds, schema, connection));
            result = super.execute(hashMap);
        }
    } finally {
        if (null != connection) {
            DataSourceUtils.releaseConnection(connection, datasource);
        }
    }

Array :

public ARRAY getArrayOfNumberValue(Integer[] array, String schema, OracleConnection connection) throws DataAccessResourceFailureException {
        String arrayOfNumberTypeName = schema + ARRAY_OF_NUMBER;
        ARRAY oracleArray = null;
        ArrayDescriptor descriptor = null;
        try {

            descriptor = (ArrayDescriptor) connection.getDescriptor(arrayOfNumberTypeName);
            if (null == descriptor) {
                descriptor = new ArrayDescriptor(arrayOfNumberTypeName, connection);
                connection.putDescriptor(arrayOfNumberTypeName, descriptor);
            }
            oracleArray = new ARRAY(descriptor, connection, array);

        } catch (SQLException ex) {
            throw new DataAccessResourceFailureException("SQLException " + "encountered while attempting to retrieve Oracle ARRAY", ex);
        }

        return oracleArray;
    }

I suspect that, when i check out the connection from "connection = (OracleConnection) DataSourceUtils.getConnection(datasource);". It will give you the logical connection but underlying it will make use of the "T4Connection" but it is releasing it. And again looking for the same connection.

 java.lang.Thread.State: BLOCKED (on object monitor)
    at oracle.sql.TypeDescriptor.getName(TypeDescriptor.java:682)
    - waiting to lock <0x00000000c1356fc8> (a oracle.jdbc.driver.T4CConnection)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.isInHierarchyOf(OracleTypeCOLLECTION.java:149)
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2063)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3579)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
    - locked <0x00000000c14b34f0> (a oracle.jdbc.driver.T4CConnection)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
    - locked <0x00000000c14b34f0> (a oracle.jdbc.driver.T4CConnection)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1066)
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1014)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
    at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)
    at com.intuit.platform.integration.sdx.da.procedures.subscription.serviceSubscription.LookupRealmSubscriptions.execute(LookupRealmSubscriptions.java:55)
    - locked <0x00000000fbd00bc0> (a oracle.jdbc.driver.LogicalConnection)
    at com.intuit.platform.integration.sdx.da.ServiceSubscriptionDAOImpl.getRealmServiceSubscriptions(ServiceSubscriptionDAOImpl.java:153)
    at com.intuit.platform.integration.sdx.ws.beans.ServiceSubscriptionResourceBean.filterRealmIds(ServiceSubscriptionResourceBean.java:84)

are you sharing a single Connection between multiple threads? if so, i'd recommend a Connection per thread. — jtahlborn, Dec 14 '12 at 04:21
Can you post the code that is doing 1) creating/populating the array, and 2) making the call? Thanks — Andrew Alcock, Dec 14 '12 at 04:28
What version of the oracle driver are you using? We are seeing similar behavior with 11.2.0.3. — BillMan, Mar 08 '13 at 18:11

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

The connection in the ARRAY is not the same as the connection in which the Stored Procedure is being executed. You can see this because the T4CConnection that is waiting for a lock (line 3 of the stack trace) has a different IF from the one locked earlier.

Use the answer in How to get current Connection object in Spring JDBC to get your current Connection, and then downcast it to an Oracle connection using https://stackoverflow.com/a/7879073/1395668. You should then be able to create the ARRAY valid for your current connection, and you shouldn't get the deadlock.

edited May 23 '17 at 10:24

Community

1
1

answered Dec 14 '12 at 09:53

Andrew Alcock

19,401
4
42
60

If we want to pass array to the stored procedure. We need to construct sql ARRAY object. To construct the same we need to pass the connection. – shiva.n404 Dec 14 '12 at 10:02
yes, you are right. Why its happening like this. Is this the problem with oracle driver? – shiva.n404 Dec 14 '12 at 10:09
Great - understand what you're doing now, so I've corrected the answer and (hopefully) provided the answer you need. – Andrew Alcock Dec 14 '12 at 10:56
Thanks Andrew.. I tried with the approach that you suggested, still didn't work. – shiva.n404 Dec 14 '12 at 11:16
In what way does it not work? Same deadlock as before (in which case the connection is not the right one) or another way? – Andrew Alcock Dec 14 '12 at 11:49
both the ways :( .. again dead locks – shiva.n404 Dec 14 '12 at 14:27
Can you check your **new** deadlock stack trace. Is the deadlock again on **different** T4CConnections? If so, somehow your StoredProcedure is still passing the wrong connection to the getArrayOfNumberValue(). You will need to debug why the connection is different - I am at a loss. – Andrew Alcock Dec 16 '12 at 06:42
I suspect that, when i check out the connection from "connection = (OracleConnection) DataSourceUtils.getConnection(datasource);". It will give you the logical connection but underlying it will make use of the "T4Connection" but it is releasing that. – shiva.n404 Dec 17 '12 at 09:09

Deadlocks while passing ARRAY to stored proc

1 Answers1

Linked