Commons Dbcp
  1. Commons Dbcp
  2. DBCP-163

[dbcp] Use setQueryTimeout in combination with validationQuery to recover from network problems

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 1.1
    • Fix Version/s: 1.3
    • Labels:
      None
    • Environment:

      Operating System: Linux
      Platform: Other

      Description

      When the network connection between tomcat 4.1.29 and database (experienced with
      sybase ASE 12.5) is lost, the 'Connection' hangs indefinitely.

      It hangs in the line of code "con = DatasourceObject.getConnection();"

      None of the other options in the connection pooling stuff - add info: used
      SharedPoolDatasourceFactory in dbcp - like maxWait / validationQuery
      /removeAbandoned/ removeAbandonedTimeout etc doesnt timeout the connection.

      Analysis: When the network connection to the database is fine (normal scenario)-
      the dbcp gets a connection from the datasource (connection pool)- sets three
      params - autocommit, readonly and isolation level for the connection object and
      returns this improved connection object to the calling class.

      Now say the network connection is lost, and the unaware application requests for
      a connection from the pool, gets the connection object and then tries to set the
      first of three params (autocommit- mentioned above) where it hangs.

      None of the timeout params work in this scenario. Setting a validationQuery
      tries its validation only after getting the connection object which never happens.

      Soln(possibility): after getting the raw connection object - run a validation
      query (or any call to database)with a stmt.setQueryTimeout() set to say 3-5 secs

      • or say configurable by user - and then catch the exception (noroutetohost
        exception) to throw it back.

        Activity

        Hide
        Phil Steitz added a comment -

        As of DBCP 1.2.2, the property initialization calls (automCommit, etc) in PoolableConnectionFactory's activate method were changed to check the value of the property before invoking the setter. This should eliminate the first network access attempt. The validationQueryTimeout property added with DBCP-226, used with testOnBorrow=true and a validation query should take care of this issue. If the Oracle driver does not throw SQLException per the spec when a statement timeout occurs, there is nothing DBCP can do about that.

        Please reopen if code as of r756628, with testOnBorrow=true and validationQueryTimeout set to a positive number does not resolve the problem.

        Show
        Phil Steitz added a comment - As of DBCP 1.2.2, the property initialization calls (automCommit, etc) in PoolableConnectionFactory's activate method were changed to check the value of the property before invoking the setter. This should eliminate the first network access attempt. The validationQueryTimeout property added with DBCP-226 , used with testOnBorrow=true and a validation query should take care of this issue. If the Oracle driver does not throw SQLException per the spec when a statement timeout occurs, there is nothing DBCP can do about that. Please reopen if code as of r756628, with testOnBorrow=true and validationQueryTimeout set to a positive number does not resolve the problem.
        Hide
        mtkopone added a comment -

        I'm pretty sure no-one is going to do anything about these, since this bug has
        been open since 2004. However, having recently been bitten by this same problem,
        I'd like to comment that at least on Oracle the proposed solution wouldn't help.
        This is because - in its infinite wisdom - the oracle driver calls cancel() on
        the statement once the statement timeout occurs. This cancel()-method then
        attempts another database roundtrip. So we are stuck on that for another eternity.

        Show
        mtkopone added a comment - I'm pretty sure no-one is going to do anything about these, since this bug has been open since 2004. However, having recently been bitten by this same problem, I'd like to comment that at least on Oracle the proposed solution wouldn't help. This is because - in its infinite wisdom - the oracle driver calls cancel() on the statement once the statement timeout occurs. This cancel()-method then attempts another database roundtrip. So we are stuck on that for another eternity.

          People

          • Assignee:
            Unassigned
            Reporter:
            cryptic
          • Votes:
            5 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development