This discussion is archived
12 Replies Latest reply: Jan 14, 2013 2:00 PM by jschellSomeoneStoleMyAlias RSS

Rare deadlocks when using an ARRAY after closing it's connection

904299 Newbie
Currently Being Moderated
Hi,

I'm constructing an ARRAY for using it as an input parameter for a stored procedure, but before executing the procedure I close the connection which I created the ARRAY with (2):

....public Map<String, Object> doIt(SqlAttributeRecord[] inputRecords) {

........ArrayDescriptor inputDescriptor;
........ARRAY myInputArray = null;
........Connection conn = null;

........try {
(1)........conn = dataSource.getConnection();
............inputDescriptor = ArrayDescriptor.createDescriptor("ITEMS_LIST", conn);
............myInputArray = new ARRAY(inputDescriptor, conn, inputRecords);
........} catch (SQLException e) {
............//bad luck
........} finally {
............try {
................//connection gets closed
(2)............conn.close();
............} catch (SQLException e) {
................//bad luck
............}
........}

........MapSqlParameterSource paramValues = new MapSqlParameterSource();
(3)...paramValues.addValue("I_ITEMS", myInputArray, Types.ARRAY);

........//the ARRAY is still used here
(4)...Map<String, Object> result = jdbcCall.execute(paramValues);

........return result;
....}

in (3) I assign the ARRAY to the input parameters for the procedure.

It works in principle, but rarely I get deadlocks from Oracle on lines (1) and (4):

Thread 1:
Name[Thread-36]Thread ID[95]
Deadlocked on Lock[oracle.jdbc.driver.T4CConnection@76eb1327] held by thread ]http-8080-30] Thread ID[209585]
Thread stack [

oracle.jdbc.driver.PhysicalConnection.closeLogicalConnection(PhysicalConnection.java:4026)
oracle.jdbc.driver.LogicalConnection.cleanupAndClose(LogicalConnection.java:304)
oracle.jdbc.pool.OracleImplicitConnectionCache.closeCheckedOutConnection(OracleImplicitConnectionCache.java:1392)

Thread 2:
Name[http-8080-195]Thread ID[271952]
Deadlocked on Lock[oracle.jdbc.pool.OracleImplicitConnectionCache@13b92a97] held by thread ]Thread-36] Thread ID[95]
Thread stack [

oracle.jdbc.pool.OracleImplicitConnectionCache.retrieveCacheConnection(OracleImplicitConnectionCache.java:566)
oracle.jdbc.pool.OracleImplicitConnectionCache.getCacheConnection(OracleImplicitConnectionCache.java:471)
oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:357)

I use the driver 11.2.0.2.0.

Is this a correct pattern? Or does the ARRAY still need it's original connection when used later in the stored procedure call (4)?

Many Thanks
  • 1. Re: Rare deadlocks when using an ARRAY after closing it's connection
    EJP Guru
    Currently Being Moderated
    What happened when you tried it with the connection still open?
  • 2. Re: Rare deadlocks when using an ARRAY after closing it's connection
    904299 Newbie
    Currently Being Moderated
    EJP wrote:
    What happened when you tried it with the connection still open?
    The connection would never be closed and the connection pool be filled up.
    The SimpleJdbcCall in (4) opens its own, another connection.

    Edited by: user1937092 on Dec 9, 2011 12:25 AM
  • 3. Re: Rare deadlocks when using an ARRAY after closing it's connection
    gimbal2 Guru
    Currently Being Moderated
    Or does the ARRAY still need it's original connection when used later in the stored procedure call (4)?
    Seeing what is happening, I'd say yes. You are getting a deadlock from the database, which means that there must be an active connection. Even though you "close" the connection, you mention you use a connection pool. Using a connection pool connections are kept open.


    I'd say the correct design is to return the connection to the pool AFTER the procedure finishes executing. When you do that, does the deadlock problem go away?
  • 4. Re: Rare deadlocks when using an ARRAY after closing it's connection
    904299 Newbie
    Currently Being Moderated
    gimbal2 wrote:
    Or does the ARRAY still need it's original connection when used later in the stored procedure call (4)?
    Seeing what is happening, I'd say yes. You are getting a deadlock from the database, which means that there must be an active connection. Even though you "close" the connection, you mention you use a connection pool. Using a connection pool connections are kept open.


    I'd say the correct design is to return the connection to the pool AFTER the procedure finishes executing. When you do that, does the deadlock problem go away?
    Even if the connection remains open in the pool, it's Connection object is marked as closed and as soon as the ARRAY uses this (closed) Connection object again while preparing the SimpleJbdcCall, shouldn't it fail every time since the Connection object is marked as closed?

    The deadlock happens only very rarerly, and I can't reproduce it.
  • 5. Re: Rare deadlocks when using an ARRAY after closing it's connection
    gimbal2 Guru
    Currently Being Moderated
    user1937092 wrote:
    Even if the connection remains open in the pool, it's Connection object is marked as closed and as soon as the ARRAY uses this (closed) Connection object again while preparing the SimpleJbdcCall, shouldn't it fail every time since the Connection object is marked as closed?
    Normally I'd agree, but there is no real way of knowing what the driver does under the hood when an ARRAY type is in play.

    Edit:

    Hm, seeing the lines you get the deadlocks, I retract my previous statement that there must be an active connection as it seems to happen on lines that create connections. Which is even stranger: why would you get a deadlock when CREATING (or fetching) a connection? A deadlock to my knowledge is because of a lock on a row or table that is causing an oracle process to stall too long, which is not a problem yet at connection creation. I've also seen deadlock messages happen when it takes too long for a two-phase commit to complete (application server in network 1, database server in network 2 on the other side of the country). There must be something there that I don't know about yet as I cannot explain this behavior.

    Edited by: gimbal2 on Dec 9, 2011 2:51 AM
  • 6. Re: Rare deadlocks when using an ARRAY after closing it's connection
    904299 Newbie
    Currently Being Moderated
    gimbal2 wrote:
    user1937092 wrote:
    Even if the connection remains open in the pool, it's Connection object is marked as closed and as soon as the ARRAY uses this (closed) Connection object again while preparing the SimpleJbdcCall, shouldn't it fail every time since the Connection object is marked as closed?
    Normally I'd agree, but there is no real way of knowing what the driver does under the hood when an ARRAY type is in play.

    Edit:

    Hm, seeing the lines you get the deadlocks, I retract my previous statement that there must be an active connection as it seems to happen on lines that create connections. Which is even stranger: why would you get a deadlock when CREATING (or fetching) a connection? A deadlock to my knowledge is because of a lock on a row or table that is causing an oracle process to stall too long, which is not a problem yet at connection creation. I've also seen deadlock messages happen when it takes too long for a two-phase commit to complete (application server in network 1, database server in network 2 on the other side of the country). There must be something there that I don't know about yet as I cannot explain this behavior.

    Edited by: gimbal2 on Dec 9, 2011 2:51 AM
    Sorry,

    the lock doesn't occur on (4) when the stored procedure is called, only on (1):
    conn = dataSource.getConnection();

    And I don't think that there is a DB deadlock like you mentioned, but only an in-memory object deadlock in the Oracle driver.

    Edited by: user1937092 on Dec 9, 2011 3:59 AM

    Edited by: user1937092 on Dec 9, 2011 4:00 AM
  • 7. Re: Rare deadlocks when using an ARRAY after closing it's connection
    gimbal2 Guru
    Currently Being Moderated
    user1937092 wrote:
    And I don't think that there is a DB deadlock like you mentioned, but only an in-memory object deadlock in the Oracle driver.
    Actually you may be right, as I don't see an ORA error mentioned anywhere.

    Well I'm not very useful. And I'm out of ammunition too. The one thing I have left is: try a different driver. Not necessarily a newer one too, sometimes rolling back to an older minor version is the way to go.
  • 8. Re: Rare deadlocks when using an ARRAY after closing it's connection
    Joe Weinstein Expert
    Currently Being Moderated
    The issue is that a Blob is a 'live' object that keeps a reference to the connection that made it, internally
    to do it's DBMS communication. So if you 'close' the pooled connection, giving it back to be used by some
    other thread, you create the possibility that the connection is used simultaneously by two threads.
    You cannot create a Blob then dissociate it in your mind/code with the connection it came from. The
    only sure fix is to retain the connection until you are finished with the Blob.
    HTH,
    Joe
  • 9. Re: Rare deadlocks when using an ARRAY after closing it's connection
    904299 Newbie
    Currently Being Moderated
    Joe Weinstein wrote:
    The issue is that a Blob is a 'live' object that keeps a reference to the connection that made it, internally
    to do it's DBMS communication
    Even if the ARRAY uses the connection only to retrieve a data type ("ITEMS_LIST")?

    ............inputDescriptor = ArrayDescriptor.createDescriptor("ITEMS_LIST", conn);
    ............myInputArray = new ARRAY(inputDescriptor, conn, inputRecords);

    It is then filled up with records not from the database and used for input to the stored procedure.

    Thanks
  • 10. Re: Rare deadlocks when using an ARRAY after closing it's connection
    Joe Weinstein Expert
    Currently Being Moderated
    Just sayin... Any intentional or inadvertent use of any single JDBC connection an/or it's
    subobjects simultaneously by multiple threads is going to risk deadlock in driver classes.
  • 11. Re: Rare deadlocks when using an ARRAY after closing it's connection
    365116 Newbie
    Currently Being Moderated
    Hi,
    I am having the similar exception in my production. how did you resolve the issue?.

    Thank you.
  • 12. Re: Rare deadlocks when using an ARRAY after closing it's connection
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    gangsv wrote:
    Hi,
    I am having the similar exception in my production. how did you resolve the issue?.
    What part of the last three posts before yours did you not understand?

Legend

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