数据库会话上下文的更改通过池化连接重用而持久存在 [英] Changes to database session context persists with pooled connection reuse

问题描述

在我的应用程序中，我有一个连接池，用于获取与Oracle数据库的连接.

In my application, I have a connection pool that I use to obtain connections to Oracle database.

我需要执行调用存储过程的语句，这些存储过程可能会影响数据库会话上下文/变量，以便这些更改仅影响连接的当前使用.

I need to execute statements that call stored procedures that might affect the database session context/variables so that these changes affect only the current use of the connection.

当我关闭连接并从池中获取另一个连接时，我希望它像一个新的连接/会话一样，在该连接/会话中该过程的效果不存在.不幸的是，这不会发生.

When I close the connection and obtain another connection from the pool, I want it like a new connection/session at which the effect of the procedure doesn't exist. Unfortunately, this doesn't happen.

因此我获得了连接，请调用以下过程:

So I obtain a connection, calls the following procedure:

PROCEDURE set_empno (empno NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app1_ctx', 'empno', empno);
  END;

像这样:

CALL APP1_CTX_PACKAGE.SET_EMPNO(11)

然后执行此查询，该查询按预期方式工作(返回值 11 ):

and then I execute this query, which works as expected (returning the value 11):

SELECT "PRICE", "EMPNO" FROM "ORDERS" WHERE empno = SYS_CONTEXT('app1_ctx', 'empno')

直到现在一切看起来都很好，我关闭了连接(因此它返回到池中)并调用pool.getConnection从池中获取连接(我希望它像新的一样，没有任何影响).问题是如果我刚获得名为以下的连接:

until now everything looks fine, I close the connection (so it returns to the pool) and call pool.getConnection to obtain a connection from the pool (I want it like new without any effects). The problem is if I just after obtaining the connection called:

SYS_CONTEXT('app1_ctx', 'empno')

在关闭连接之前，我得到了来自呼叫的值 11 .因为没有使用此连接设置值，所以我期望得到一个错误或为空.

I get the value 11 that came from the call before closing the connection. I was expecting to get an error or null since I didn't set the value using this connection.

有什么方法可以重置会话或连接以使其成为新会话，而无需更改上下文或安全上下文或类似的内容

Is there any way that I can reset the session or the connection to act as a new one without any changes to the context or security context or anything like this

请注意，我不想只重设app1_ctx，我想消除对会话的任何更改(我不知道用户在通话中将发生什么变化)

Note that I don't want only to reset the app1_ctx, I want to eliminate any changes to the session (I don't know what exactly the user would change in his calls)

还请注意，我使用此应用程序访问不同的数据库:Oracle，MySQL，SQLServer ..etc

Note also that I use this application to access different databases: Oracle, MySQL, SQLServer ..etc

推荐答案

对于Oracle，您将要调用DBMS_SESSION.RESET_PACKAGE.调用该过程将重置所有程序包的会话状态，因此该会话将看起来像一个新会话.

For Oracle, you will want to call DBMS_SESSION.RESET_PACKAGE. Calling that procedure resets the session state of all packages so the session will seem like a new session.

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