数据库会话上下文中的更改随着池化连接的重用而持续存在 [英] Changes in database session context persists with pooled connection reuse

问题描述

在我的应用程序中，我具有需要访问数据库的多线程，并且正在使用

In my application I have multithreads that needs to access database and I am using apache.tomcat.jdbc.pool.DataSource as a JDBC connection pool.

在某些情况下，用户在执行另一个查询以检索某些数据之前执行可能会影响数据库会话上下文/变量的存储过程.

In some cases users execute stored procedures that might affect the database session context/variables before executing another query to retrieve some data.

线程完成后，连接将关闭，但是池化连接的工作方式实际上并未关闭连接，而是将其返回到连接池以供重用.问题类似于此问题问题

After a thread is finished, connection is closed but the way the pooled connections work, the connection is not actually closed but rather returned to the connection pool for reuse. The problem is similar to this question question.

问题在于这些变量通常会影响从数据库检索的数据.因此，当另一个线程获取设置了这些变量/上下文的连接，然后查询数据库时，检索到的数据将受到上下文/变量的影响.

The problem is that these variables usually affect the data retrieved from the database. So when another thread acquire a connection where these variables/context where set and then query the database the data retrieved is affected by the context/variables.

处理此类问题的最佳方法是什么?我已经概述了一些解决方案，但不确定哪种是最佳实践，而不是如何实现所有这些解决方案?

What is the best way to handle such an issue? I have outlined some solutions but not sure which is best practise and not how to implement them all?

执行一个过程/语句，以重置会话/变量，然后再将连接释放回池:此解决方案可能有效，但问题是我的应用程序使用其他数据库.目前，支持MySQL和Oracle.对于Oracle，我们有 RESET_PACKAGE 过程，但是MySQL没有等效的功能.有没有办法在MySQL中做到这一点?如果还支持新数据库又该怎么办?

Execute a Procedure / Statement that reset session / variables before releasing the connection back to the pool: This solution could work but the issue is my application use different databases. For now MySQL and Oracle are supported. For Oracle we have RESET_PACKAGE Procedure, but there is no equivalent for MySQL. Is there a way to do it in MySQL? also what if new databases are supported?

是否有一种方法可以强制或显式关闭实际/物理连接，而不是仅仅将其返回到池中?或是否存在强制池以关闭连接的属性?

Is there a way to enforce or explicitly close the actual/physical connection instead of just returning it to the pool? or Is there a property to enforce pool to close the connection?

回滚到保存点是否还原数据库会话变量/上下文?

Savepoint savepoint = connection.setSavepoint();
// execute some procedure connection that affect session
connection.rollback(savepoint);
connection.close();

或者还有其他方法可以强制清除会话或重新启动/关闭实际连接吗?

Or is there any other way to enforce clearing of session or restart/close the actual connection?

我的问题与此推荐答案

在MySQL上，您可以执行以下步骤:

On MySQL you might perform the sequence:

BEGIN;
COMMIT RELEASE;

作为您的 validationQuery . RELEASE 部分断开当前会话并创建一个新会话(请参见 MySQL文档).

as your validationQuery. The RELEASE part disconnects the current session and creates a new one (cf. MySQL Documentation).

这篇关于数据库会话上下文中的更改随着池化连接的重用而持续存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！