Lose 2 transactions per request

Question

The most convenient transaction management for me is the use of one optional transaction for the entire HTTP request. This means that the first SQL statement must retrieve the connection from the pool, start the transaction and complete the processing of the request, the transaction must be completed (or rolled back when an exception occurs), and the connection must be closed. Of course, if necessary, finer transaction management should be possible.

Does Play 2 support this out of the box? I can probably implement it myself, but I'm looking for a turnkey solution.

I looked at the DB object, but it seems that DB.withConnection uses a new connection (and transaction) every time.

I use the Scala and Anorm db libraries if that matters.

Answer 1

DB.withTransaction- This is what you need. Similarly DB.withConnection, it will provide one connection from the connection pool for all the SQL functions contained in it. Since you want to use one transaction for each request, it seems that it would be most appropriate to call it in the controller functions and require that all your model functions have an implicit connection parameter.

Model:

object Product {

    def checkInventory(id: Long)(implicit c: Connection): Int = SQL(...)

    def decrementInventory(id: Long, quantity: Int)(implicit c: Connection): Boolean = SQL(...)

}

object Cart {

    def addItem(id: Long, quantity: Int)(implicit c: Connection): Boolean = SQL(...)

}

Controller function:

def addToCart(id: Long, quantity: Int) = Action {

    DB.withTransaction{ implicit connection =>
       if(Product.checkInventory(id) >= quantity && Product.decrementInventory(id, quantity)) {

        Cart.addItem(id, quantity)
        ....
       } else {
           BadRequest
       }
    }
}

Disclaimer: This is clearly not a logical shopping cart transaction, but just a simple illustration of the use of database transactions.

Action documentation, Transaction, :

object Transaction extends ActionBuilder[Request] {
    def invokeBlock[A](request: Request[A], block: (Request[A]) => Future[Result]) = {
        DB.withTransaction{implicit connection => block(request)}
    }
}

, Action:

def addToCart(id: Long, quantity: Int) = Transaction {
    Product.checkInventory(id)

    ...
}

. , . Connection , Result . , , / , , .