Fork me on GitHub

Following up on the follow-up from yesterday: The java.sql.SQLException: Connection is closed issue was caused by the fact that the failing db call ran in a future, but at a later time than the web request that spawned it. That shouldn’t have been a problem, I thought, but that was before I learned of binding conveyance, which is the mechanism that transfers dynamic binding values over to new threads when using future and the like. I didn’t know this was happening. But apparently the database connection that was active during the web request was also the connection that was made available to database functions in the Future, and when the web request finished the connection was closed. Then the database functions in the Future failed. I’ve solved this by setting up an ExecutorService to run my async jobs instead of just dropping (future …) calls. I know, I know, should have done it to begin with, it’s good advice.


@msolli FWIW, my advice would be to set up a pooled datasource at startup and pass that around and then get connections from it and use them only locally. So the code inside your future should work with the pooled datasource and not a connection. Keeping the connection scopes as small as possible should avoid the problems you were seeing.


Thanks, yes, that is what I’m doing. I’m using hikari-cp. The more I think about it, the weirder it seems. I don’t think my analysis was completely correct (I checked the connections in the web request and and in the future code, and they were distinct - at least they had different Java object ids). Nevertheless, when all the code in the future could complete before the web request, all was fine. And when I moved to an ExecutorService, all was fine, too, even when the code ran after the web request completed. So my problem is solved, but I don’t know exactly what caused it. I don’t have the time to really dig further to discover more, though I would have liked to.


If you're trying to maintain a transaction across a whole web request, I would strongly caution against that, especially if you're trying to run code in a background thread. Again, keeping transaction scopes -- like connections -- as small as possible helps avoid a lot of problems.