How do I use HikariCP for both read-only and read-write connections?

[KajMagnus]

Hi,

I recently started using HikariCP. Previously I was using some Postgres connection pool but it didn't reconnect after a disconnection.

However, now with HikariCP, when I do this: (Scala code, and readOnly is sometimes true sometimes false)

    val connection: js.Connection = dataSource.getConnection()

    connection.setReadOnly(readOnly)

this gets thrown:

    org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction

But I haven't intentionally started any transaction. (What starts it?)

This same code did work previously with the PostgreSQL data source.

— I wonder, now with HikariCP, what do you recommend that I do instead?

Should I for example have 2 Hikari pools, one for read-only transactions and one for read-write?

Here's my HikariConfig b.t.w.:

val config = new HikariConfig()
config.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource")
config.setUsername(user)
config.setPassword(password)
config.addDataSourceProperty("serverName", server)
config.addDataSourceProperty("portNumber", port)
config.addDataSourceProperty("databaseName", database)
config.setAutoCommit(false)
config.setConnectionTimeout(...)
config.setValidationTimeout(...)
config.addDataSourceProperty("loginTimeout", ...)
config.addDataSourceProperty("socketTimeout", ...)
config.setTransactionIsolation("TRANSACTION_SERIALIZABLE")

To me, two pools sounds as if it could be a good idea? Because then I can avoid database roundrips otherwise caused by setReadOnly()?

(About roundtrips: When reading in this forum, I found:

 [...] Whenever a call to setTransactionIsolation() is made, a roundtrip to the database is required.  Same for setAutocommit() and setReadOnly().  Some JDBC drivers cache the current values for a connection and only roundtrip if the new value is different that the old value, but many do not.

https://groups.google.com/d/msg/hikari-cp/zBKo96xoHXI/x88fgeoqZU4J  )

So perhaps it's better with 2 connection pools, one with  read-only for all connections,  and another with  read-write (with auto-commit-disabled).  So I never have to do any change-the-connection-settings roundtrips?

Or is there some other way to get both read-only & read-write-no-autocommit serializable transactions from Hikari, other than having 2 pools?

Thanks for building this apparently great connection pool,

and best regards,

KajMagnus

[Brett Wooldridge]

First, I would say two pools is going to perform better (but likely to have more impact on your existing code).  This issue in particular (the exception), I think can be avoided by setting isolateInternalQueries=true. Can you verify it?

-Brett

[Niels van Klaveren]

We had this exact same problem with Postgres and the tomcat connection pool. It was indeed because running the connection test query starts an implicit transaction on Postgres, and any changes to isolation level or read only flag transaction setting is impossible when in a transaction. We had to manually force a commit after the test query to fix it. Nice to see Hikari already has a built in way to deal with these situations.

[KajMagnus]

Hi Brett and Niels,

Thanks for your replies.

config.setIsolateInternalQueries(true) apparently doesn't solve the problem in my case; the "Cannot change transaction read-only property in the middle of a transaction." error still happens, when I call `conn.setReadOnly(readOnly)`, although I've done config.setIsolateInternalQueries(true) just before. — Perhaps there's some other config value that "messes things up" somehow (?)

However, I'm now using 2 different pools, one for read-write and one for read-only connections. So now all works fine now for me, since I specify read-only when I create the data source, rather than after I've obtained a connection (so the above-mentioned error situation never happens any more for me).  (I had already split my get-connection calls into readOnlyTransaction { ... } and readWriteTransaction { ... } so actually I had to change a few lines only, to do this.)

***

Anyway in case you want more info about this: Here's how my DataSource looks in the debugger:

dataSource = {HikariDataSource@14902} "HikariDataSource (HikariPool-0)"

 isShutdown = {AtomicBoolean@14909} "false"

 fastPathPool = {HikariPool@14910} "HikariPool-0"

 pool = {HikariPool@14910} "HikariPool-0"

 connectionTimeout = 3000

 validationTimeout = 2000

 idleTimeout = 600000

 leakDetectionThreshold = 0

 maxLifetime = 1800000

 maxPoolSize = 10

 minIdle = 10

 catalog = null

 connectionInitSql = null

 connectionTestQuery = null

 dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"

 dataSourceJndiName = null

 driverClassName = null

 jdbcUrl = null

 password = "public"

 poolName = "HikariPool-0"

 transactionIsolationName = "TRANSACTION_SERIALIZABLE"

 username = "debiki_dev"

 isAutoCommit = false

 isReadOnly = false

 isInitializationFailFast = true

 isIsolateInternalQueries = true

 isRegisterMbeans = false

 isAllowPoolSuspension = false

 dataSource = null

 dataSourceProperties = {Properties@14916}  size = 5

 threadFactory = null

 scheduledExecutor = null

 metricsTrackerFactory = null

 metricRegistry = null

 healthCheckRegistry = null

 healthCheckProperties = {Properties@14919}  size = 0

and doing dataSource.getConnection().setReadOnly(true) then throws "Cannot change transaction read-only property in the middle of a transaction."

Using: "com.zaxxer" % "HikariCP" % "2.4.3"

Best regards, KajMagnus

On Sunday, March 27, 2016 at 3:14:52 AM UTC+2, Niels van Klaveren wrote:

We had this exact same problem with Postgres and the tomcat connection pool. It was indeed because running the connection test query starts an implicit transaction on Postgres, and any changes to isolation level or read only flag transaction setting is impossible when in a transaction. We had to manually force a commit after the test query to fix it. Nice to see Hikari already has a built in way to deal with these situations.

On Saturday, March 19, 2016 at 1:10:33 PM UTC+1, Brett Wooldridge wrote:

[Brett Wooldridge]

Interesting.  I may have to use wireshark to see what is traveling between the driver and the DB.  I noticed you are using JDBC4 isValid() connection validation -- by not setting a connection test query -- which is usually recommended, but I would be curious is the behavior is different when you explicitly configure a test query.

-Brett

[KajMagnus]

Hi Brett,

I enabled the connection test queries (select 1), and then this code runs, in checkValidationMode in PoolBase.java:

   executeSql(connection, config.getConnectionTestQuery(), false, isIsolateInternalQueries && !isAutoCommit);

  and the variable values are: isIsolateInternalQueries = true & isAutoCommit = false

then in executeSql(...) this code runs:

statement.execute(sql);

if (!isReadOnly) {
   if (isCommit) {
      connection.commit();
   }
   else if (isRollback) {
      connection.rollback();    <--- throws
   }
}

that line throws:

  org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.

But I have disabled autoCommit.  Weird. Perhaps a PostgreSQL driver surprising-behavior?

Here's all the code:

val config = new HikariConfig()
config.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource")
config.setUsername(user)
config.setPassword(password)
config.addDataSourceProperty("serverName", server)
config.addDataSourceProperty("portNumber", port)
config.addDataSourceProperty("databaseName", database)

val WaitForConnectionMillis = 3000
val TooSlowQuerySeconds = 5
config.setAutoCommit(false)
config.setConnectionTimeout(WaitForConnectionMillis)
config.setValidationTimeout(WaitForConnectionMillis - 1000)
config.addDataSourceProperty("loginTimeout", TooSlowQuerySeconds)
config.addDataSourceProperty("socketTimeout", TooSlowQuerySeconds)
//config.setReadOnly(readOnly) -- removed now when testing
config.setIsolateInternalQueries(true)
config.setConnectionInitSql("select 1")
config.setConnectionTestQuery("select 1")
config.setTransactionIsolation("TRANSACTION_SERIALIZABLE")
val dataSource =
  try new HikariDataSource(config)

Best regards, KajMagnus

[Nitin Chauhan]

@KajMagnus

If possible, try latest pgjdbc driver 9.4.1208 with hikaricp 2.4.5.

Driver had issues regarding isValid() and open transaction.

remove:

config.setConnectionInitSql("select 1")

[KajMagnus]

Hi Nitin and others,

(Please note that for me the problem is gone, because I have 2 pools nowadays, one always read-only, one always read-write.

The stuff below is just in case you for some reason want to troubleshoot or fix something.)

Okay I'm now using Hikari 2.4.5 & Postgres 9.4.1208 (I was about to upgarde to Postgres 9.5 anyway),

and ... there's still this cannot-rollback-because-of-autocommit error, although I've set autocommit = off:  (note that there's an NPE too, right?)

2016-03-29 05:32:44,997 [info] c.z.h.p.PoolBase: HikariPool-1 - Driver does not support get/set network timeout for connections. (Method org.postgresql.jdbc.PgConnection.getNetworkTimeout() is not yet implemented.)

2016-03-29 05:32:45,041 [error] c.z.h.p.PoolBase: HikariPool-1 - Failed to execute connection test query. (Cannot rollback when autoCommit is enabled.)

2016-03-29 05:32:45,042 [info] c.z.h.p.HikariPool: HikariPool-1 - Close initiated...

2016-03-29 05:32:45,043 [info] c.z.h.p.HikariPool: HikariPool-1 - Closed.

2016-03-29 05:32:47,895 [error] application: Error connecting to database, for HikariPool-1 [EsE7JK4]

org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.

        at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:796) ~[postgresql-9.4.1208.jar:9.4.1208]

        at com.zaxxer.hikari.pool.PoolBase.executeSql(PoolBase.java:489) ~[HikariCP-2.4.5.jar:na]

        at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:379) ~[HikariCP-2.4.5.jar:na]

        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:342) ~[HikariCP-2.4.5.jar:na]

        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:316) ~[HikariCP-2.4.5.jar:na]

        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514) ~[HikariCP-2.4.5.jar:na]

        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:118) ~[HikariCP-2.4.5.jar:na] 

        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71) ~[HikariCP-2.4.5.jar:na]

        at debiki.Debiki$.createPostgresHikariDataSource(Debiki.scala:114) ~[classes/:na] 

        Suppressed: java.lang.NullPointerException: null      

                at com.zaxxer.hikari.pool.HikariPool.shutdown(HikariPool.java:219) ~[HikariCP-2.4.5.jar:na] 

                at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:518) ~[HikariCP-2.4.5.jar:na]

                at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:118) ~[HikariCP-2.4.5.jar:na] 

                at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71) ~[HikariCP-2.4.5.jar:na]

                at debiki.Debiki$.createPostgresHikariDataSource(Debiki.scala:114) ~[classes/:na]   

                at debiki.Globals$$anonfun$9.apply(Globals.scala:238) [classes/:na]    

                at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24) [scala-library-2.11.7.jar:na]

                at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24) [scala-library-2.11.7.jar:na]

                at scala.concurrent.impl.ExecutionContextImpl$AdaptedForkJoinTask.exec(ExecutionContextImpl.scala:121) [scala-library-2.11.7.jar:na]

Wrapped by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Cannot rollback when autoCommit is enabled.

        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:524) ~[HikariCP-2.4.5.jar:na]  

        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:118) ~[HikariCP-2.4.5.jar:na]  

        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71) ~[HikariCP-2.4.5.jar:na]  

        at debiki.Debiki$.createPostgresHikariDataSource(Debiki.scala:114) ~[classes/:na] 

        at debiki.Globals$$anonfun$9.apply(Globals.scala:238) [classes/:na]       

        at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24) [scala-library-2.11.7.jar:na]

        at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24) [scala-library-2.11.7.jar:na]

        at scala.concurrent.impl.ExecutionContextImpl$AdaptedForkJoinTask.exec(ExecutionContextImpl.scala:121) [scala-library-2.11.7.jar:na]

        at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) [scala-library-2.11.7.jar:na]

My code:

val config = new HikariConfig()
config.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource")
config.setUsername(user)
config.setPassword(password)
config.addDataSourceProperty("serverName", server)
config.addDataSourceProperty("portNumber", port)
config.addDataSourceProperty("databaseName", database)
val WaitForConnectionMillis = 3000
val TooSlowQuerySeconds = 5
config.setAutoCommit(false)
config.setConnectionTimeout(WaitForConnectionMillis)
config.setValidationTimeout(WaitForConnectionMillis - 1000)
config.addDataSourceProperty("loginTimeout", TooSlowQuerySeconds)
config.addDataSourceProperty("socketTimeout", TooSlowQuerySeconds)
//config.setReadOnly(readOnly)

config.setConnectionTestQuery("select 1")
config.setIsolateInternalQueries(true)

config.setTransactionIsolation("TRANSACTION_SERIALIZABLE")
val dataSource =
  try new HikariDataSource(config)

Best regards, KajMagnus

[Nitin Chauhan]

Thanks for reply and with stack trace pointing out NPE too.

I think it is throwing exception when you have readonly and autocommit both set to false.

However, do one more favor please. try removing config.setConnectionTestQuery("select 1") too.

If I remember in driver, they made changes to isValid() to send empty query. may be that does not start transaction anymore.

[KajMagnus]

Hi Nitin,

I removed the test query and now it works :-) That is, I can call connection.setReadOnly(true) without any "Cannot change transaction read-only property in the middle of a transaction" exception.

Here's the code that works:

val config = new HikariConfig()
config.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource")
config.setUsername(user)
config.setPassword(password)
config.addDataSourceProperty("serverName", server)
config.addDataSourceProperty("portNumber", port)
config.addDataSourceProperty("databaseName", database)
val WaitForConnectionMillis = 3000
val TooSlowQuerySeconds = 5
config.setAutoCommit(false)
config.setConnectionTimeout(WaitForConnectionMillis)
config.setValidationTimeout(WaitForConnectionMillis - 1000)
config.addDataSourceProperty("loginTimeout", TooSlowQuerySeconds)
config.addDataSourceProperty("socketTimeout", TooSlowQuerySeconds)
//config.setReadOnly(readOnly)

config.setIsolateInternalQueries(true)
config.setTransactionIsolation("TRANSACTION_SERIALIZABLE")

...

def getConnection(readOnly: Boolean): js.Connection = {
  if (readOnly) {
    val c = readOnlyDataSource.getConnection()
    c.setReadOnly(true)                <-- didn't work before (threw exception)
    c
  }
  else readWriteDataSource.getConnection()
}

The differences in comparison to my original post, should be:  1) config.setIsolateInternalQueries(true)  and 2) I use postgresql-9.4.1208 instead of 9.3.something.

But now I removed  config.setIsolateInternalQueries(true) — and the code still works, there's no "Cannot change transaction read-only property in the middle of a transaction" error.

So apparently what fixed the error, was this:   postgresql-9.3.whatever  --> postgresql-9.4.1208

Best regards, KajMagnus

[Nitin Chauhan]

Thanks for report. Glad it works :)

It would be more efficient to set read only to true for configuration of readOnlyDataSource. then your getConnection can be more readable as:
(not sure of actual syntax, pseudo code based on your code above)

def getReadOnlyConnection(): js.Connection = {
    readOnlyDataSource.getConnection()
}
def getReadWriteConnection(): js.Connection = {
    readWriteDataSource.getConnection()
}

Best

[Kaj Magnus Lindberg]

(  Hi Nitin, thanks for the tips about specifying read-only at configuration time. Actually, that's what I do already — but the code I posted above, was edited, in order to reproduce the error being discussed. This is my real code:

def getConnection(readOnly: Boolean): js.Connection = {
  if (readOnly) readOnlyDataSource.getConnection()   <-- read-only because: HikariConfig.setReadOnly(true)
  else readWriteDataSource.getConnection()
}

Best regards, KajMagnus   )