Commons Dbcp
  1. Commons Dbcp
  2. DBCP-357

Connection validationQuery mechanism should be replaced by new method connection#isValid()

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.4, 1.4.1
    • Fix Version/s: 2.0
    • Labels:

      Description

      Hello,
      Current method of connection validation relies on validationQuery.
      This method has a rather big performance impact on the DB (CPU, for example 3% with SELECT 1 FROM DUAL even with Oracle 10G FAST DUAL) and make an additional query for each borrow (when testOnBorrow is true).
      Wouldn't it be better to use new JDBC 4 method isValid which relies on Driver check ? (Oracle would for example use its internal method ping)

      Thank you
      Philippe
      http://www.ubik-ingenierie.com

        Issue Links

          Activity

          Hide
          Phil Steitz added a comment -

          Looks like a good enhancement for 2.0. We should probably make it configurable, though, whether to rely on the driver implementation or actually fire the validation query.

          Show
          Phil Steitz added a comment - Looks like a good enhancement for 2.0. We should probably make it configurable, though, whether to rely on the driver implementation or actually fire the validation query.
          Hide
          Tim Walters added a comment -

          Driver check is not always implemented.

          Validation query should use a prepared statement instead of normal statement.
          This will reduce the CPU usage required for a validation check significantly as database wont need to re-parse query each time.

          Show
          Tim Walters added a comment - Driver check is not always implemented. Validation query should use a prepared statement instead of normal statement. This will reduce the CPU usage required for a validation check significantly as database wont need to re-parse query each time.
          Hide
          Mark Thomas added a comment -

          I have switched to a prepared statement for the validation query. There is 10-15% improvement in the time taken to validate a connection.

          I've checked the JDBC 4.1 specification and implementing isValid() correctly is mandatory. While this means the validation query configuration option could be dropped entirely, I intend to make the following change:
          If one or more of the testXxx properties is true, test the connection at the appropriate point(s) using isValid() unless validationQuery is set in which case use that rather than isValid.

          Show
          Mark Thomas added a comment - I have switched to a prepared statement for the validation query. There is 10-15% improvement in the time taken to validate a connection. I've checked the JDBC 4.1 specification and implementing isValid() correctly is mandatory. While this means the validation query configuration option could be dropped entirely, I intend to make the following change: If one or more of the testXxx properties is true, test the connection at the appropriate point(s) using isValid() unless validationQuery is set in which case use that rather than isValid.
          Hide
          Bernd Eckenfels added a comment - - edited

          I think it is good to have different validators. And JDBC4 isValid() can be one of them.

          However I advice against using this as the only/default method. For Oracle drivers for example the isValid() method does internally use a simple select statement (and it does actually NOT use a prepared statement). If a timeout is specified it is even worse as it kicks of a background thread (on each isValid(int) invocation). The Oracle implementation is therefore so bad (as of today), it is much better to use the sql method (with PS).

          BTW: there might be some possibility for optimizations if you have a reset statement (like rollback() and setAutocommit()). If those are sent to the DB they can replace the valid check in some conditions.

          Show
          Bernd Eckenfels added a comment - - edited I think it is good to have different validators. And JDBC4 isValid() can be one of them. However I advice against using this as the only/default method. For Oracle drivers for example the isValid() method does internally use a simple select statement (and it does actually NOT use a prepared statement). If a timeout is specified it is even worse as it kicks of a background thread (on each isValid(int) invocation). The Oracle implementation is therefore so bad (as of today), it is much better to use the sql method (with PS). BTW: there might be some possibility for optimizations if you have a reset statement (like rollback() and setAutocommit()). If those are sent to the DB they can replace the valid check in some conditions.
          Hide
          Mark Thomas added a comment -

          The observed behavior with Oracle is not consistent with the description above. Neither is it consistent with the information provided by the originator of this bug report. Using the same test as I used for looking at the performance of PreparedStatements for validation, a validation query of "SELECT 1 FROM DUAL" executed as a PreparedStatement is 7-8 times slower than using Connection.isValid(). On this basis the choice of Connection.isValid() as the default validation looks to be a reasonable one.

          The documentation has been updated to make the new default behavior clear and users remain free to specify their own validation query if they wish.

          Show
          Mark Thomas added a comment - The observed behavior with Oracle is not consistent with the description above. Neither is it consistent with the information provided by the originator of this bug report. Using the same test as I used for looking at the performance of PreparedStatements for validation, a validation query of "SELECT 1 FROM DUAL" executed as a PreparedStatement is 7-8 times slower than using Connection.isValid(). On this basis the choice of Connection.isValid() as the default validation looks to be a reasonable one. The documentation has been updated to make the new default behavior clear and users remain free to specify their own validation query if they wish.
          Hide
          Bernd Eckenfels added a comment -

          Yes you are right, it is actually DB version dependend. If the DB is newer than versionNumber >= 10102 then the OCI piggyback ping is used, otherwise the SELECT 'X' from DUAL. So for most recent systems I guess the isValid() is fine. However if you specify a timeout it will always fall back to the select method (and the additional new Thread()) - at least with the Thin divers.

          Show
          Bernd Eckenfels added a comment - Yes you are right, it is actually DB version dependend. If the DB is newer than versionNumber >= 10102 then the OCI piggyback ping is used, otherwise the SELECT 'X' from DUAL. So for most recent systems I guess the isValid() is fine. However if you specify a timeout it will always fall back to the select method (and the additional new Thread()) - at least with the Thin divers.

            People

            • Assignee:
              Unassigned
              Reporter:
              Philippe Mouawad
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development