Uploaded image for project: 'Commons DBCP'
  1. Commons DBCP
  2. DBCP-585

Connection level JMX queries result in concurrent access to connection objects, causing errors

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 2.9.0
    • 2.10.0
    • None

    Description

      As we expose Connection objects over JMX, they may be accessed by multiple threads concurrently;
      a) an application thread that borrows the Connection and uses it business as usual,
      b) another thread simultaneously performing a JMX query, which in turn calls getters on the same connection object via the MBean interface.

      Also, calls to Connection object getters are mostly delegated to the underlying vendor-specific connection provided by the JDBC driver. For example, when we make the JMX query to get the "schema" attribute of the JMX connection object, this is translated into a "java.sql.Connection.getSchema()", and passed to the vendor-specific Connection object by DBCP. In the case of Postgres, for example, this is further translated to a query "select current_schema()" and sent to the server.

      Hence, querying connections over JMX result in concurrent access by multiple threads to the underlying Connection provided by the vendors, to the point that these two threads may be running queries simultaneously on the same connection.

      However, this is not supported by any of the major database vendors. Vendor links on Connection objects not being threadsafe:

      • Postgres

        The PostgreSQL™ JDBC driver is not thread safe. The PostgreSQL server is not threaded. Each connection creates a new process on the server; as such any concurrent requests to the process would have to be serialized. The driver makes no guarantees that methods on connections are synchronized. It will be up to the caller to synchronize calls to the driver.

      • Oracle 

        Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously.

      • Microsoft SQL Server

        SQLServerConnection is not thread safe, however multiple statements created from a single connection can be processing simultaneously in concurrent threads.

      Another interesting point to note, also to do justice to previous committers who have put this feature in place, is that this was not always the case. In the following links, you may see the same links to the older versions of the same pages. In the past, all vendors indicated that Connection is fully thread-safe; Postgres, Oracle, MSSQL Server.

      Hence, it was once safe to expose Connection objects via JMX given the thread-safety guarantees for the underlying vendor connection were in place. But as Vendors dropped the thread-safety guarantee one by one, it is not safe anymore, and may actually cause convoluted errors that pop up intermittently due to thread races in the JDBC driver code (see an example in the comments section below). Accordingly, exposing Connections via JMX shall be retired along with dropped support from most vendors.

      Note: the Datasource MBeans, which provide a vital set of metrics have no such problems as they don't depend on the underlying JDBC provider.

      Attachments

        1. 0001-DBCP-585-idea-clarification-1.patch
          5 kB
          Kurtcebe Eroglu
        2. conn_instance_attrs.png
          90 kB
          Kurtcebe Eroglu
        3. connections_attrs.png
          219 kB
          Kurtcebe Eroglu
        4. ds_attrs.png
          241 kB
          Kurtcebe Eroglu
        5. final.png
          211 kB
          Kurtcebe Eroglu

        Activity

          People

            Unassigned Unassigned
            Kurtcebe Eroglu Kurtcebe Eroglu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 1h 20m
                1h 20m