On transactions and nested DB writes (using Scala)

Question

Let's say that I have the following methods:
On a WebsitesList model:

def create(urls: List[String]) = DB.withTransaction(implicit c => {
    val websites = urls.map(Website.create(_))
    val listId: Option[Long] = SQL("INSERT INTO LIST (created_date, active) VALUES ({created_date}, {active})").
      on('created_date -> new Date(), 'active -> true).
      executeInsert()
    websites.foreach(websiteId =>
      SQL("INSERT INTO websites_list (list_id, website_id) VALUES ({listId}, {websiteId})").
      on('listId -> listId.get, 'websiteId -> websiteId.id).executeInsert()
    )

    throw new Exception()
  })

And on the Website model:

def create(url: String): Website = DB.withConnection {
    implicit c =>
      val currentDate = new Date()
      val insertedId: Option[Long] = SQL("insert into websites (url, Date_Added) values ({url}, {date})").
        on('url -> url, 'date -> currentDate).
        executeInsert()
      Website(insertedId.get, url, currentDate)
  }

As you can see, I start a transaction on the WebsitesList create method, and said method calls the create method of the Website model.
My objective is to delete the created websites records if for some reason the WebsitesList fails to be created. In order to test it I raise an exception and as expected, the WebsitesList and List records are not created. However, the websites records are not rollback'd after the exception and stay on the database.

My theory is that the Website.create method created a new connection instead of using the existing one. Anybody knows how I could fix this?

Thanks.

Answer 1

You can change the signatures to take an implicit connection and then control the transaction outside the create methods.

Website.scala

object Website {

  def create(url: String): Website = {
    DB.withConnection { implicit connection =>
      createWithConnection(url)
    }
  }

  def createWithConnection(url: String)(implicit connection: Connection): Website = {
    val currentDate = new Date()
    val insertedId: Option[Long] = SQL("insert into websites(url, Date_Added) values ({url}, {date})").
      on('url -> url, 'date -> currentDate).
      executeInsert()
    Website(insertedId.get, url, currentDate)
  }
}

WebsiteList.scala

object WebsiteList {

  def create(urls: List[String]) = DB.withTransaction(implicit c => {
    createWithConnection(urls)
  })

  def createWithConnection(urls: List[String])(implicit connection: Connection) = {
    val websites = urls.map(Website.createWithConnection)
    val listId: Option[Long] = SQL("INSERT INTO LIST (created_date, active) VALUES ({created_date}, {active})").
      on('created_date -> new Date(), 'active -> true).
      executeInsert()
    websites.foreach(websiteId =>
      SQL("INSERT INTO websites_list (list_id, website_id) VALUES ({listId}, {websiteId})").
        on('listId -> listId.get, 'websiteId -> websiteId.id).executeInsert()
    )

    throw new Exception()
  }
}

As you can see in the WebsiteList I changed the DB.withConnection to DB.withTransaction since you are doing several inserts and want them committed together in the same transaction.

This lets you control when and where the Connection should be shared.

For example you could do the transaction management in the controller which knows better for how long a transaction should be used:

object SomeController extends Controller {
  def someAction(someData: String) = Action { implicit request =>
    DB.withTransaction { implicit connection =>
      SomeModel.create(someData)
      OtherModel.create(someData)
    }
  }
}

object SomeModel {
  def create(data: String)(implicit connection: Connection) {
  }
}

object OtherModel {
  def create(data: String)(implicit connection: Connection) {
  }
}