更改数据库会话上下文将随池化连接重用而持续存在。

huangapple go评论67阅读模式
英文:

Changes to database session context persists with pooled connection reuse

问题

在我的应用程序中,我有一个连接池,用于获取到 Oracle 数据库的连接。

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

当我关闭连接并从连接池中获取另一个连接时,我希望它就像是一个新的连接/会话,不受该存储过程影响的效果。不幸的是,实际情况并非如此。

因此,我获取一个连接,调用以下存储过程:

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

就像这样:

    CALL APP1_CTX_PACKAGE.SET_EMPNO(11)

然后我执行这个查询,它的工作正如预期(返回值 11):

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

到目前为止,一切看起来都正常,我关闭连接(它会返回到连接池),然后调用 pool.getConnection 来从连接池中获取连接(我希望它像是没有任何影响的新连接)。问题是,如果我在获取连接后立即调用:

    SYS_CONTEXT('app1_ctx', 'empno')

我会得到值为 11,这个值是来自在关闭连接之前的那次调用。我本来期望会得到错误或空值,因为我没有在这个连接中设置过这个值。

是否有任何方法可以重置会话或连接,使其像全新的连接一样,没有任何上下文、安全上下文或类似的更改?

请注意,我不仅想要重置 app1_ctx,我希望消除会话中的任何更改(我不知道用户会在他的调用中更改什么)。

还要注意,我使用此应用程序来访问不同的数据库:Oracle、MySQL、SQLServer 等。

英文:

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;

like this:

CALL APP1_CTX_PACKAGE.SET_EMPNO(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')

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')

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

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)

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

答案1

得分: 1

对于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.

huangapple
  • 本文由 发表于 2020年9月21日 18:37:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/63990609.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定