Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DataSourceTransactionManager assumes that connection is not in transaction but nothing ensures it [SPR-8870] #13511

Closed
spring-projects-issues opened this issue Nov 22, 2011 · 7 comments
Labels
status: bulk-closed

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented Nov 22, 2011

Leonid opened SPR-8870 and commented

DataSourceTransactionManager.doBegin() assumes that connection it passes to DataSourceUtils.prepareConnectionForTransaction() is not in transaction but really nothing does guaranntee it. In case of PostgreSQL this causes an exception:

org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
at org.postgresql.jdbc2.AbstractJdbc2Connection.setTransactionIsolation(AbstractJdbc2Connection.java:783)
at org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:380)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setTransactionIsolation(PoolingDataSource.java:343)
at org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:380)
at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:174)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:210)
... 38 more


1 votes, 2 watchers

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 22, 2011

Leonid commented

P.S. autocommit is off.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 28, 2011

Juergen Hoeller commented

Well, what would you expect DataSourceTransactionManager to do in such a case? It has been designed for turning non-transactional Connections into transactional ones for the duration of a Spring-managed transaction. It does rely on the underlying connection pool returning Connection handles in proper state: Connections may be in autocommit off mode but they need to have been committed or rolled back properly before their return to the pool. It's generally recommendable to only use autocommit off in combination with 100% transactional access, that is, even using transactions for any kind of read access. DataSourceTransactionManager should be able to work fine in the latter scenario.

Juergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 14, 2012

George Angeletos commented

Hello,

I am using Spring's (3.0.5) programmatic transactions, postgres and the latest tomcat-jdbc connection pool.The above exception takes place always when setting the defaultAutoCommit property to false and randomly when it's set to true. Please advise for a workaround as this is becoming a show-stopper.

Are there any plans for fixing this in the near feature?

Thank you,
George

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 19, 2012

Juergen Hoeller commented

The question is: What can we do about this from Spring's side? JDBC doesn't provide a proper mechanism for detecting in-flight transactions, so we can only really check the auto-commit mode. However, finding auto-commit as false doesn't mean that the Connection is in an ongoing transaction either, since this is also the 'optimized' state for transaction-aware connection pools to return their connection handles in.

From my perspective, the ideal solution is to use a connection pool that ensures that a commit or rollback has been performed before returning a connection to the pool. That's what Spring's DataSourceTransactionManager has been designed for, and it's really only the connection pool implementation which can make sure that the connection is held in proper state, in particular with other parties performing JDBC access against the same DataSource as well.

An alternative solution is to make sure that all access to that JDBC DataSource goes through Spring's DataSourceTransactionManager, i.e. through Spring-managed transactions - even read-only operations, simply using @Transactional(readOnly=true) then. If there is anything we can do in DataSourceTransactionManager to make it work better in such a scenario, let us know, since we at least have a chance of effectively managing the connection state there.

Juergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 20, 2012

George Angeletos commented

Hi Juergen,

Thank you for your reply. I think the problem takes place only if you try to change the ISOLATION level of a transaction either programmatically or declaratively whether it's read-only or not. In my case I am specifying an isolation level of SERIALIZABLE. Below is my datasource configuration:

<bean id="datasource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
             
        <property name="testOnBorrow"                     value="true"/>
        <property name="testOnReturn"                     value="false"/>
        <property name="validationQuery"                  value="SELECT 1"/>        
        <property name="validationInterval"               value="30000"/>        
        <property name="timeBetweenEvictionRunsMillis"    value="30000"/>
        <property name="maxActive"                        value="10"/>        
        <property name="minIdle"                          value="2"/>
        <property name="maxIdle"                          value="2"/>
        <property name="maxWait"                          value="10000" />
        <property name="initialSize"                      value="5"/>
        <property name="removeAbandonedTimeout"           value="5" />
        <property name="removeAbandoned"                  value="true" />
        <property name="logAbandoned"                     value="true" />
        <property name="minEvictableIdleTimeMillis"       value="30000"/>
        <property name="defaultAutoCommit"                value="false" />
</bean>

All access to the JDBC datasource is through the DataSourceTransactionManager. We are using postgresql 9.2 (90201) and the postgres 9.2-1002 JDBC 4 driver. If I do not specify the isolation level (either programmatically or declaratively) and use the postgres default READ_COMMITTED everything behaves as expected. If you attempt to set the isolation level either programmatically or using @Transactional the second consecutive call to the same service method (with the SERIALIZABLE isolation) will result in the above exception. In the first call, I can see in the logs that Spring successfully commits the transaction and the postgresql driver sending the COMMIT command to the backend. In the second call however, the exception is thrown. Can someone please confirm the above behavior as it is driving me crazy :)

Please forgive my ignorance but which open source connection pool ensures that a commit or rollback has been performed before returning the connection to the pool? I have tried setting the commitOnReturn flag to true but I still get the same problem when Spring attempts to change the isolation level.

A workaround that seems to be working is to set the isolation level your application requires globally at the postgresql end.

Regards,
George

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 20, 2012

George Angeletos commented

If you remove the connection validation from the pool configuration

<property name="testOnBorrow"                     value="true"/>
<property name="testOnReturn"                     value="false"/>
<property name="validationQuery"                  value="SELECT 1"/>        
<property name="validationInterval"               value="30000"/>  

everything works as expected. The postgresql driver version I used when running the tests was 9.2-1001 and not 9.2-1002.

@spring-projects-issues spring-projects-issues added type: bug status: waiting-for-triage and removed type: bug labels Jan 11, 2019
@rstoyanchev rstoyanchev added status: bulk-closed and removed status: waiting-for-triage labels Jan 11, 2019
@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Jan 12, 2019

Bulk closing outdated, unresolved issues. Please, reopen if still relevant.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
status: bulk-closed
Projects
None yet
Development

No branches or pull requests

2 participants