Cannot change transaction isolation level in the middle of a transaction.

[Kostas kougios]

When I change the default transaction isolation, I am setting the exception.

I do:

val all = DBIO.sequence(data)
  .withTransactionIsolation(TransactionIsolation.Serializable)
  .transactionally
db.run(all).map(_ => ())

And when I run it I get:

Exception in thread "main" org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.

at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:825)

at com.zaxxer.hikari.pool.ProxyConnection.setTransactionIsolation(ProxyConnection.java:397)

at com.zaxxer.hikari.pool.HikariProxyConnection.setTransactionIsolation(HikariProxyConnection.java)

at slick.jdbc.JdbcActionComponent$SetTransactionIsolation.run(JdbcActionComponent.scala:71)

at slick.jdbc.JdbcActionComponent$SetTransactionIsolation.run(JdbcActionComponent.scala:67)

at slick.dbio.SynchronousDatabaseAction$$anon$6.run(DBIOAction.scala:475)

at slick.dbio.SynchronousDatabaseAction$$anon$10.run(DBIOAction.scala:562)

at slick.dbio.SynchronousDatabaseAction$$anon$7.run(DBIOAction.scala:487)

at slick.dbio.SynchronousDatabaseAction$FusedAndThenAction$$anonfun$run$4.apply(DBIOAction.scala:534)

at slick.dbio.SynchronousDatabaseAction$FusedAndThenAction$$anonfun$run$4.apply(DBIOAction.scala:534)

at scala.collection.Iterator$class.foreach(Iterator.scala:893)

at scala.collection.AbstractIterator.foreach(Iterator.scala:1336)

at scala.collection.IterableLike$class.foreach(IterableLike.scala:72)

at scala.collection.AbstractIterable.foreach(Iterable.scala:54)

at slick.dbio.SynchronousDatabaseAction$FusedAndThenAction.run(DBIOAction.scala:534)

at slick.dbio.SynchronousDatabaseAction$$anon$11.run(DBIOAction.scala:571)

at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:240)

at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:240)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

If I remove the transaction isolation line, it works fine.

I've debugged it and noticed that the code below calls the highlighted line multiple times, even if the isolation level is set:

protected class SetTransactionIsolation(ti: Int) extends SynchronousDatabaseAction[Int, NoStream, Backend, Effect] {
  def run(ctx: Backend#Context): Int = {
    val c = ctx.session.conn
    val old = c.getTransactionIsolation
    c.setTransactionIsolation(ti)
    old
  }
  def getDumpInfo = DumpInfo(name = "SetTransactionIsolation")
}

So old === ti but still it calls it and subsequently the driver fails at

if (queryExecutor.getTransactionState() != TransactionState.IDLE) {
  throw new PSQLException(
      GT.tr("Cannot change transaction isolation level in the middle of a transaction."),
      PSQLState.ACTIVE_SQL_TRANSACTION);
}

Am I doing something wrong or this is a bug (slick or driver)?

I am using slick 3.2.0 and latest postgres driver.

[Kostas kougios]

It seems swapping the call order fixes the issue:

.transactionally
.withTransactionIsolation(TransactionIsolation.Serializable)