Database transactions in Play framework scala appl

Question

I am developing an application using Play framework and scala. I am using anorm for data-access layer. And I've got a problem I could not solve.

Brief: I want to be able to have methods in data-access objects (dao) to work inside transactions as well as being called alone.

Details:

I have data-access layer consist of class with methods that only executes particular SQL over database. Traditionally they looks like:

def list() = DB.withConnection { implicit cn =>
  ...
}

Now I want to have some methods to be executed in a transaction scope. Like traditional select-update service methods but still be able to run them alone. So, what I have in my mind is like this:

class Service {
  def fooTransacted() = {
    inTransaction {
      val old = dao.select(id = 2)
      val newObj = old.copy(isActive = true)
      dao.update(newObj)
    }
  }

  def fooSinle() = {
    dao.select(id = 2)
  }
}

I tried around several ways, but could not come up with any solution.

Answer 1

The solution I've seen used elsewhere (principally in Squeryl), is roughly the following:

import java.sql.Connection
object Helper {
  private val conn: ThreadLocal[Connection] = new ThreadLocal

  def inTransaction[X](f: Connection => X) = {
    conn.get() match {
      case null =>
        DB.withConnection { newConn =>
          conn.set(newConn)
          try f(newConn)
          finally conn.set(null)
        }
      case c => f(c)
    }
  }
}

This way, the inTransaction method is re-entrant, so there's no harm in calling it redundantly inside dao.select.

If you prefer, you can expose conn via a public method, and change the signature of f to => X - you lose some compile-time safety, but the API is a little cleaner.

One pitfall with this approach is that connections are tied to threads, which may cause problems if you're using futures or actors, and a process can resume on a different thread (this is a tricky area anyway, but one you should be aware of).

You might want to look into Squeryl too - it may already do what you need.

Answer 2

What about

class Dao {
  def foo(id: Long)(implicit connection: Connection) = {
    SQL("select * from foo where id={id}").on('id->id).as(...)
  }
}

class Service{
  def withConnection = {
    DB.withConnection {implicit connection =>
      Dao.foo(1)
      Dao.foo(2)
    }
  }

  def withTransaction = {
    DB.withTransaction {implicit connection =>
      Dao.foo(1)
      Dao.foo(2)
  }
}