5

I am using BoneCP with Postgresql and Spring JdbcTemplate. When JdbcTemplate executes query and then tries to close the connection, it gets this exception:

org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction. at org.postgresql.jdbc2.AbstractJdbc2Connection.setReadOnly(AbstractJdbc2Connection.java:725) at com.jolbox.bonecp.ConnectionHandle.setReadOnly(ConnectionHandle.java:1279) at com.jolbox.bonecp.ConnectionHandle.(ConnectionHandle.java:254) at com.jolbox.bonecp.ConnectionHandle.recreateConnectionHandle(ConnectionHandle.java:273) at com.jolbox.bonecp.ConnectionHandle.close(ConnectionHandle.java:476) at org.springframework.jdbc.datasource.DataSourceUtils.doCloseConnection(DataSourceUtils.java:341) at org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:328) at org.springframework.jdbc.datasource.DataSourceUtils.releaseConnection(DataSourceUtils.java:294) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:411) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477)

Since it can't close connections, open connections reaches to max connection number, then app become unresponsive.

I am not setting any transaction related properties. So transaction settings should be default. How can I fix this?

Dependencies and configuration:

bonecp 0.8.0-rc1 postgresql 9.2-1002.jdbc4 spring-jdbc 3.2.1.RELEASE

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource"        destroy-method="close">         <property name="driverClass" value="org.postgresql.Driver" />       <property name="jdbcUrl" value="jdbc:postgresql://127.0.0.1/mkayman" />         <property name="username" value="mkayman" />        <property name="password" value="" />       <property name="idleConnectionTestPeriodInMinutes" value="5" />         <property name="idleMaxAgeInMinutes" value="30" />      <property name="maxConnectionsPerPartition" value="5" />        <property name="minConnectionsPerPartition" value="2" />        <property name="partitionCount" value="2" />        <property name="acquireIncrement" value="1" />      <property name="statementsCacheSize" value="100" />     </bean>
mkayman
  • 100
  • 1
  • 2
  • 11

3 Answers3

3

I ran into this issue today. You might be missing one of the required dependencies of boneCP.

I was missing SLF4J library, and receiving the same message. Check whether you have the dependencies here: http://jolbox.com/index.html?page=http://jolbox.com/requirements.html

  • 1
    I faced the exact same problem and adding the dependencies did not fix this problem. Anyway, from the error, the problem does not look like it is not finding a class it wants to refer to, but something about connection pool configuration. – Salil May 25 '13 at 13:59
0

I ran into the same issue and was able to solve it by doing these two steps:

  • setting the isolation level by adding these statement: config.setDefaultTransactionIsolation("READ UNCOMMITTED");

  • commit every transaction before closing.

I am not sure which one solved the problem or if both are necessary.

El Hoss
  • 3,767
  • 2
  • 18
  • 24
  • Setting `READ UNCOMMITTED` sounds like a bad idea — because then you might get rather random results when you read data that other transactions are writing, perhaps it'll even be rolled back. Perhaps it works for you in your particular case but I wouldn't recommend it in general. Or at least warn people about what it does – KajMagnus Feb 14 '16 at 00:54
0

Changing to BoneCP version to 0.8.0.RELEASE solves the issue.

sinuhepop
  • 20,010
  • 17
  • 72
  • 107