12 Replies Latest reply: Jan 14, 2013 4:00 PM by jschellSomeoneStoleMyAlias RSS

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

    904299

      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
          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
            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
              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
                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
                  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
                    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
                      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-Oracle
                        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
                          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-Oracle
                            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
                              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
                                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?