[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: 0001-DBCP-585-idea-clarification-1.patch
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification-1.patch,
> conn_instance_attrs.png, connections_attrs.png, ds_attrs.png, final.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: 0001-DBCP-585-idea-clarification.patch)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification-1.patch,
> conn_instance_attrs.png, connections_attrs.png, ds_attrs.png, final.png
>
>
> 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)
[jira] [Commented] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17516580#comment-17516580
]
Kurtcebe Eroglu commented on DBCP-585:
--
[~ggregory] I noticed your last comment after I've posted. I've submitted a PR
for your review.
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> conn_instance_attrs.png, connections_attrs.png, ds_attrs.png, final.png
>
>
> 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)
[jira] [Commented] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17516575#comment-17516575
]
Kurtcebe Eroglu commented on DBCP-585:
--
Hi [~ggregory],
We register two different sets of information to JMX. Below are screenshots
from JavaMissionControl for quick visualization.
When we configure the data source as
{code:java}
BasicDataSource ds = new BasicDataSource();
ds.setJmxName("com.example:name=BasicDataSource1");
{code}
# The selected object in the screenshot is
"{{{}com.example:name=BasicDataSource1{}}}".
It gives the datasource config and runtime metrics as can be seen above.
!ds_attrs.png!
We also register a subset of this data at
"{{{}com.example:connectionpool=connections,name=BasicDataSource1{}}}"
!connections_attrs.png!
# And we have the MBeans pointing to the connection object instances. These
are registered at {{{}org.apache.commons.dbcp2.PoolableConnection{}}}, as a
part of the connection lifecycle. This is the problematic one as values are
fetched from underlying vendor-specific connection objects (we extend
{{{}DelegatingConnection{}}}).
These are registered with the name
"{{{}com.example:connectionpool=connections,connection=X,name=BasicDataSource1{}}}",
where "X" changes with each new connection creation.
!conn_instance_attrs.png!
The first two sets of data are essential for monitoring, as it gives us both
DataSource runtime config options, and metrics like connection pool size, idle
and active connections etc. Setting {{setJmxEnabled(false)}} would disable
everything. However, we can hold on to the essential metrics and just drop out
the connection instance metrics (to be honest, in my personal opinion, these
are not adding too much value to monitoring anyways).
Nothing changes with default settings, but if we set up the pool specifying the
new flag like below, we end up disabling only the problematic part, and can
still reach all the essential monitoring information (notice the connections
disappear);
{code:java}
BasicDataSource ds = new BasicDataSource();
ds.setJmxName("com.example:name=BasicDataSource1");
ds.setRegisterConnectionMBean(false);
{code}
!final.png!
Below is the patch file (a quick one), for testing the above parameter.
[^0001-DBCP-585-idea-clarification.patch]
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> conn_instance_attrs.png, connections_attrs.png, ds_attrs.png, final.png
>
>
> 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
>
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: final.png
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> conn_instance_attrs.png, connections_attrs.png, ds_attrs.png, final.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: conn_instance_attrs.png
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> conn_instance_attrs.png, connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: conn_meta.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: connections_attrs.png
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: ds_attrs.png
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: all_mbeans_1.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: 1_meta.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: conn_attrs.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch,
> connections_attrs.png, ds_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: 2_attrs.png
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch, 1_meta.png,
> all_mbeans_1.png, conn_attrs.png, conn_meta.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: 2_attrs.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch, 1_meta.png,
> all_mbeans_1.png, conn_attrs.png, conn_meta.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: 2_meta.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch, 1_meta.png,
> all_mbeans_1.png, conn_attrs.png, conn_meta.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: (was: 2_attrs.png)
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch, 1_meta.png,
> all_mbeans_1.png, conn_attrs.png, conn_meta.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: 0001-DBCP-585-idea-clarification.patch
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch, 1_meta.png,
> 2_attrs.png, 2_meta.png, all_mbeans_1.png, conn_attrs.png, conn_meta.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: conn_meta.png
> 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
>Priority: Major
> Attachments: 0001-DBCP-585-idea-clarification.patch, 1_meta.png,
> 2_attrs.png, 2_meta.png, all_mbeans_1.png, conn_attrs.png, conn_meta.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: conn_attrs.png
> 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
>Priority: Major
> Attachments: 1_meta.png, 2_attrs.png, 2_meta.png, all_mbeans_1.png,
> conn_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: 2_meta.png
> 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
>Priority: Major
> Attachments: 1_meta.png, 2_attrs.png, 2_meta.png, all_mbeans_1.png,
> conn_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: 2_attrs.png
> 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
>Priority: Major
> Attachments: 1_meta.png, 2_attrs.png, 2_meta.png, all_mbeans_1.png,
> conn_attrs.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: 1_meta.png
> 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
>Priority: Major
> Attachments: 1_meta.png, all_mbeans_1.png
>
>
> 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)
[jira] [Updated] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kurtcebe Eroglu updated DBCP-585:
-
Attachment: all_mbeans_1.png
> 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
>Priority: Major
> Attachments: 1_meta.png, all_mbeans_1.png
>
>
> 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)
[jira] [Commented] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17516403#comment-17516403
]
Kurtcebe Eroglu commented on DBCP-585:
--
Thanks for your comment [~ggregory],
It makes perfect sense to handle this with as little disruption as possible to
existing users, and dropping an existing feature may indeed be a no go for a
minor version.
Would you agree that even if we don't have multiple examples (from different
vendors and versions) at the moment, the existence of clear guidance from
multiple major DB/ JDBC driver vendors, telling that their Connection objects
are not thread-safe and shall not be accessed as such, may justify an optional
config param for 2.x line? JMX instrumentation is already optional via
"jmxName" parameter, but we'd love to hang on to the DataSource MBeans, which
are providing immense value on monitoring. For example, an additional config
option "registerConnectionMBean", "true" by default, hence completely
transparent to existing users, may give users who have problems, or want to
follow the guidance from their DB vendors a way to opt-out from Connection
monitoring, only when this parameter is explicitly set to "false"?
> 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
>Priority: Major
>
> 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,
[jira] [Commented] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
[
https://issues.apache.org/jira/browse/DBCP-585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17516009#comment-17516009
]
Kurtcebe Eroglu commented on DBCP-585:
--
It's hard to consistently repro race conditions with test classes, but just to
provide a sample of cases where concurrent access can actually break things, we
may use the class below. We seem to be able to generate such an error almost
50% of the time with the example below.
The class depends on commons-dbcp2 2.9.0 and Postgres JDBC driver 42.2.23. We
simulate external JMX queries using [Prometheus JMX
Exporter|https://github.com/prometheus/jmx_exporter] 0.16.1.
- download the dependencies and the javaagent
- create a new file called 'jmx-exporter-config.yml' in the same directory,
with the following contents. This config will query and report all MBeans.
{noformat}
---
startDelaySeconds: 0
{noformat}
- compile class with dependencies and run like below. Notice that we expose
port 9099 for JMX queries over HTTP. Replace the JDBC connection string
parameter as per your DB instance details.
- after starting a program use a browser or Curl to send a get request to the
port exposed by the agent using the address "http://localhost:9099;
{noformat}
java -cp
postgresql-42.2.23.jar:commons-dbcp2-2.9.0.jar:commons-pool2-2.10.0.jar:commons-logging/1.2/commons-logging-1.2.jar:.
-javaagent:jmx_prometheus_javaagent-0.16.1.jar=9099:jmx-exporter-config.yml
TestBasicDataSource
"jdbc:postgresql://localhost:5432/testdb?user=testuser=testpass=false"
{noformat}
~50% of the time, as soon as we browse "http://localhost:9099;, we can notice
the following errors in the logs. If the error does not happen, stop and
restart the test and try again.
{noformat}
Setting up data source.
Creating threads to use the datasource
waiting for error messages during JMX access...
org.postgresql.util.PSQLException: Cannot change transaction isolation level in
the middle of a transaction.
at
org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:940)
at
org.apache.commons.dbcp2.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:958)
at
org.apache.commons.dbcp2.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:958)
at TestBasicDataSource$QueryTask.runOnce(TestBasicDataSource.java:54)
at TestBasicDataSource$QueryTask.run(TestBasicDataSource.java:35)
at java.lang.Thread.run(Thread.java:748)
Thread terminated on error
org.postgresql.util.PSQLException: Cannot change transaction isolation level in
the middle of a transaction.
at
org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:940)
at
org.apache.commons.dbcp2.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:958)
at
org.apache.commons.dbcp2.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:958)
at TestBasicDataSource$QueryTask.runOnce(TestBasicDataSource.java:54)
at TestBasicDataSource$QueryTask.run(TestBasicDataSource.java:35)
at java.lang.Thread.run(Thread.java:748)
...
{noformat}
Test class
{code:java}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class TestBasicDataSource {
static DataSource dataSource;
public static void main(String[] args) {
System.out.println("Setting up data source.");
dataSource = setupDataSource(args[0]);
System.out.println("Creating threads to use the datasource");
for (int i = 0; i < 20; i ++){
new Thread(new QueryTask()).start();
}
System.out.println("waiting for error messages during JMX access...");
}
public static DataSource setupDataSource(String connectURI) {
BasicDataSource ds = new BasicDataSource();
ds.setJmxName("com.example:name=BasicDataSource1");
ds.setDriverClassName("org.postgresql.Driver");
ds.setUrl(connectURI);
return ds;
}
static class QueryTask implements Runnable {
boolean runFlag = true;
public void run() {
while (runFlag) {
runOnce();
}
System.out.println("Thread terminated on error");
}
private void runOnce() {
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
conn = dataSource.getConnection();
// Following setTransactionIsolation call will fail if we're
already in an active transaction.
//
// This happens intermittently, because the JMX thread
sometimes read the stale value of "autoCommit"
// flag on the Postgres connection object, and send the "select
[jira] [Created] (DBCP-585) Connection level JMX queries result in concurrent access to connection objects, causing errors
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)
