Scala Play 2.2 Slick 1.0.1 - future { Try {…} } Timed out waiting for a free available connection

Question

I have a scala.html page that makes AJAX calls in a Play 2.2.1, Scala 2.10.2, Slick 1.0.1, Postgres 9.3 application.

The following synchronous code works fine. It parses the request query string and calls the method Schools.findSchoolsByFilter, which makes a scala slick call to a table and filters the results based upon the SchoolFilter object and returns a Try[List[School]]

def listSchools = Action { implicit request =>
  db.withSession { implicit s: Session =>
    Schools.findSchoolsByFilter(parseFilter) match {
      case Success(schools) => Ok(toJsArray(schools))
      case Failure(e) => Ok(e.getMessage)
    }
  }
}

If I change the method to run asynchronously (see below), and make multiple calls to listSchools, then after about 20 seconds this exception gets thrown. I suspect that it might be some sort of race condition akin to this post Play slick and Async - is it a race condition?. My question is, how should I change this code to safely run it asynchronously?

def listSchools = Action.async { implicit request =>
  db.withSession { implicit s: Session =>
    Schools.findSchoolsByFilter(parseFilter) map {
      case Success(schools) => Ok(toJsArray(schools))
      case Failure(e) => Ok(e.getMessage)
    }
  }
}
def findSchoolsByFilter(f: SchoolFilter, n: Int)(implicit s: Session) = 
  future { Try {
    ...
}}
case class SchoolFilter(name: Option[String], 
                        city: Option[String], 
                        state: Option[String],
                        zip: Option[String], 
                        district: Option[String])

Here are my dependencies:

libraryDependencies ++= Seq(
  jdbc,
  cache,
  "com.typesafe.slick" %% "slick" % "1.0.1",
  "com.github.tototoshi" %% "slick-joda-mapper" % "0.4.0",
  "org.scalatest" % "scalatest_2.10" % "2.0" % "test",
  "org.easymock" % "easymock" % "3.2",
  "org.postgresql" % "postgresql" % "9.3-1100-jdbc4"
)

Here's the stack trace:

com.jolbox.bonecp.DefaultConnectionStrategy.getConnectionInternal(DefaultConnectionStrategy.java:88) com.jolbox.bonecp.AbstractConnectionStrategy.getConnection(AbstractConnectionStrategy.java:90) com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:553) com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:131) scala.slick.session.Database$$anon$1.createConnection(Database.scala:82) scala.slick.session.BaseSession.conn$lzycompute(Session.scala:207) scala.slick.session.BaseSession.conn(Session.scala:207) scala.slick.session.Session$class.prepareStatement(Session.scala:29) scala.slick.session.BaseSession.prepareStatement(Session.scala:201) scala.slick.jdbc.StatementInvoker.results(StatementInvoker.scala:29) scala.slick.jdbc.StatementInvoker.elementsTo(StatementInvoker.scala:17) scala.slick.jdbc.Invoker$class.foreach(Invoker.scala:90) scala.slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:10) scala.slick.jdbc.Invoker$class.build(Invoker.scala:66) scala.slick.jdbc.StatementInvoker.build(StatementInvoker.scala:10) scala.slick.jdbc.Invoker$class.list(Invoker.scala:56) scala.slick.jdbc.StatementInvoker.list(StatementInvoker.scala:10) scala.slick.jdbc.UnitInvoker$class.list(Invoker.scala:150) scala.slick.driver.BasicInvokerComponent$QueryInvoker.list(BasicInvokerComponent.scala:19) models.school.Schools$$anonfun$findSchoolsByFilter$1$$anonfun$apply$5.apply(School.scala:85) models.school.Schools$$anonfun$findSchoolsByFilter$1$$anonfun$apply$5.apply(School.scala:84) scala.util.Try$.apply(Try.scala:161) models.school.Schools$$anonfun$findSchoolsByFilter$1.apply(School.scala:84) models.school.Schools$$anonfun$findSchoolsByFilter$1.apply(School.scala:84) scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24) scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24) akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:42) akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:386) scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339) scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979) scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)

UPDATE

Per the recommendations of Heroku/Play/BoneCp connection issues, I added "com.jolbox" % "bonecp" % "0.8.0.RELEASE" to my library dependencies and the following to application.conf and am still getting the same behavior.

db.default.idleMaxAge=10 minutes
db.default.idleConnectionTestPeriod=30 seconds
db.default.connectionTimeout=20 second
db.default.connectionTestStatement="SELECT 1"
db.default.maxConnectionAge=30 minutes

cvogt cvogt · Accepted Answer · 2013-12-23T06:00:02

You need to open the session WITHIN the future. Right now you are doing the opposite. A Slick session is not valid after the end of the withSession block. If you open a future within the block it hangs on to the session even after the withSession block ended and the session became invalid. If the future code tries to use the invalid session you end up with unpredictable behavior.

Or in other words move the db.withSession { implicit s: Session => call into the future {...} call.

Scala Play 2.2 Slick 1.0.1 - future { Try {…} } Timed out waiting for a free available connection

UPDATE

1 Answers