Kurtcebe Eroglu created DBCP-585:
------------------------------------
Summary: Connection level JMX queries result in concurrent access
to connection objects, causing errors
Key: DBCP-585
URL: https://issues.apache.org/jira/browse/DBCP-585
Project: Commons DBCP
Issue Type: Bug
Affects Versions: 2.9.0
Reporter: Kurtcebe Eroglu
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|https://jdbc.postgresql.org/documentation/head/thread.html]
{quote}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.
{quote}
-
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-coding-tips.html#GUID-EE479007-D105-4F82-8D51-000CBBD4BC77]
{quote}Oracle strongly discourages sharing a database connection among multiple
threads. Avoid allowing multiple threads to access a connection simultaneously.
{quote}
- [Microsoft SQL
Server|https://www.javadoc.io/doc/com.microsoft.sqlserver/mssql-jdbc/latest/com.microsoft.sqlserver.jdbc/com/microsoft/sqlserver/jdbc/SQLServerConnection.html]
{quote}SQLServerConnection is not thread safe, however multiple statements
created from a single connection can be processing simultaneously in concurrent
threads.
{quote}
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|https://www.postgresql.org/docs/7.1/jdbc-thread.html],
[Oracle|https://docs.oracle.com/cd/A97335_02/apps.102/a83724/tips1.htm], [MSSQL
Server|https://www.javadoc.io/doc/com.microsoft.sqlserver/mssql-jdbc/6.1.0.jre7/com/microsoft/sqlserver/jdbc/SQLServerConnection.html].
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.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
