`java.sql.SQLException: Connection is closed` after running multiple queries in parallel inside a transaction with Monix 3.1+

[fkrull]

Version: 3.9.0 Module: quill-jdbc-monix Database: postgresql

We're seeing an SQLException (connection closed) after trying to run multiple queries in parallel inside a transaction, but only with Monix >= 3.1.

Let me try to break this down some more:

The issue requires both a transaction block, and inside it a Task combinator that runs things in parallel (e.g. Task.gather/Task.parSequence). This combination somehow causes Quill to close the connection early so that subsequent queries fail with a "Connection is closed" SQLException. I have a suite of 5 tests that test the code in question; each test works if run individually, but if I run all of them, the first one succeeds and the other ones fail.

With Monix 3.0, the code works as expected; with an explicit dependency on Monix 3.1+, it fails as described.

The offending code fragment looks something like this; it stores, in a transaction, a domain object that maps onto several database tables with foreign key relations to a "main" table. I was trying to get some speedup by first saving the main object and then saving the sub-objects in parallel:

val main = ...
val sub1 = ...
val sub2 = ...
ctx.transaction {
  for {
    _ <- ctx.run {
      mainTable.insert(lift(main))
    }
    _ <- Task.gather(Seq(
      ctx.run {
        sub1Table.insert(lift(sub1))
      },
      ctx.run {
        sub2Table.insert(lift(sub2))
      },
      ))
  } yield ...
}

The task ultimately gets turned into a future using .runToFuture. If I either remove the ctx.transaction or replace the gather with sequence, the problem disappears.

Partial stack trace:

Caused by: java.sql.SQLException: Connection is closed
	at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515)
	at com.sun.proxy.$Proxy23.prepareStatement(Unknown Source)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at io.getquill.context.jdbc.JdbcRunContext.$anonfun$executeQuery$1(JdbcContextBase.scala:85)
	at io.getquill.context.jdbc.JdbcRunContext.$anonfun$withConnectionWrapped$2(JdbcContextBase.scala:74)
	at map @ io.getquill.context.monix.MonixJdbcContext.$anonfun$withConnection$2(MonixJdbcContext.scala:73)
	at flatMap @ io.getquill.context.monix.MonixJdbcContext.withConnection(MonixJdbcContext.scala:72)
...

This seems suspiciously similar to https://github.com/getquill/quill/issues/1416. That one didn't involve actually trying to run things in parallel, AFAICT, but the symptoms are the same.

---

I'm admittedly not sure that the approach as described makes sense (could the tasks running in parallel even be using the same connection?), but the fact that it works with Monix 3.0 makes me think so. Even if it doesn't, there might be a way to provide better error feedback.

This could also be a Monix issue, but I can't tell, so I'm just putting this here.

@getquill/maintainers

[jilen]

Actually u cannot run queries parallel inside transaction. Most database client/server protocol is stateful, so you cannot run multiple at the same time with same connection.

[fkrull]

That's all well and good, but then why did it work (or at least run producing the expected result) with Monix 3.0? And can this be detected with a better error message?

[guizmaii]

I think we see the same java.sql.SQLException: Connection is closed error in CI when updating Monix to v3.1.0: See:

• https://github.com/zio/zio-quill/pull/1700
• https://github.com/zio/zio-quill/actions/runs/6574935333/job/17860984808?pr=1700