[jboss-jira] [JBoss JIRA] (WFLY-8032) CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (PostgreSQL)

[Tomas Hofman (JIRA)]

    [ https://issues.jboss.org/browse/WFLY-8032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13396309#comment-13396309 ] 

Tomas Hofman edited comment on WFLY-8032 at 4/21/17 3:59 AM:
-------------------------------------------------------------

New PR merged: https://github.com/ironjacamar/ironjacamar/pull/623
Released in https://github.com/ironjacamar/ironjacamar/commits/ironjacamar-1.4.4.Final


was (Author: thofman):
New PR merged: https://github.com/ironjacamar/ironjacamar/pull/623

> CheckValidConnectionSQL can open a transaction, preventing application from changing transaction isolation level (PostgreSQL)
> -----------------------------------------------------------------------------------------------------------------------------
>
>                 Key: WFLY-8032
>                 URL: https://issues.jboss.org/browse/WFLY-8032
>             Project: WildFly
>          Issue Type: Bug
>          Components: JCA
>    Affects Versions: 10.1.0.Final
>            Reporter: Tomas Hofman
>            Assignee: Tomas Hofman
>
> PostgreSQL driver only allows changing the transaction isolation level when transaction is not opened. Under certain circumstances, an application can receive a connection with already opened transaction and an attempt to change transaction isolation level will lead to exception.
> This happens with the PostgreSQL driver and with CheckValidConnectionSQL checker configured to run a select statement to verify connections retrieved from the pool.
> The scenario is as follows:
> # A connection is retrieved from the pool for the 1st app and CheckValidConnectionSQL verifies it by running a select statement (autocommit is set to true by default). This statement is run directly via the jdbc connection, not the wrapper.
> # 1st app receives the connection, sets autocommit=false, perform some work and commits a transaction.
> # The connection is returned to the pool, {{cleanup()}} method is called on LocalManagedConnection wrapper, which sets autocommit=true. This however doesn't reset autocommit on the wrapped jdbc connection yet, which would only happen just before executing another SQL statement f.i.
> # The same connection is retrieved from the pool for the 2nd app and CheckValidConnectionSQL runs the query. Because the jdbc connection has still autocommit=false, new transaction is opened.
> # 2nd app receives the connection and calls {{setTransactionIsolation()}}, which throws an exception because the transaction is open.
> Possible solution could be that the {{cleanup()}} method propagates the autocommit=true to the wrapped jdbc connection immediately.



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)