[jira] [Comment Edited] (METAMODEL-1156) Transactions left running with PostgreSQL pooled connection

["Kasper Sørensen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/METAMODEL-1156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16134624#comment-16134624 ] 

Kasper Sørensen edited comment on METAMODEL-1156 at 8/21/17 1:41 AM:
---------------------------------------------------------------------

Just to make sure I understand it properly, you're talking about these lines (118-130 in JdbcUpdateCallback.java), right?

{code}
                try {
                    commitOrRollback(success);

                    if (getDataContext().isDefaultAutoCommit()) {
                        try {
                            getConnection().setAutoCommit(true);
                        } catch (SQLException e) {
                            throw JdbcUtils.wrapException(e, "enable auto-commit");
                        }
                    }
                } finally {
                    getDataContext().close(_connection);
                }
{code}


was (Author: kaspersor):
Just to make sure I understand it properly, you're talking about these lines (118-130 in JdbcUpdateCallback.java), right?

{code}

> Transactions left running with PostgreSQL pooled connection
> -----------------------------------------------------------
>
>                 Key: METAMODEL-1156
>                 URL: https://issues.apache.org/jira/browse/METAMODEL-1156
>             Project: Apache MetaModel
>          Issue Type: Bug
>    Affects Versions: 4.6.1
>            Reporter: Owain Braddick
>            Priority: Minor
>
> When querying a PostgreSQL database, MetaModel sets the JDBC connection to not auto-commit as a workaround to avoid fetching all results into memory at once. That causes a transaction to be created which holds a shared lock. This transaction is never rolled back or committed. I think it is being relied upon that closing the connection will roll back the transaction. However the JavaDoc for Connection states "It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined." In our application we are using a Tomcat connection pool. Calling close on the connection just puts the connection back in the pool and the transaction remains holding a lock. When an operation comes along needing an exclusive lock, the system gets stuck behind the shared lock held by the open transaction. There is a setting on the pool that we could use called rollbackOnReturn (as yet untested), but because of what the JavaDoc states and because MetaModel is changing the connection state internally, I think it would be good if MetaModel could also find a way to roll back the transaction before closing the connection. Perhaps the connection could be wrapped in this case so that the close method calls rollback first?



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)