Setting a transaction isolation level in Squeryl

Question

How can I set a transaction isolation level using Squeryl?

For instance, right now I am using Postgresql and need serializable isolation for specific single transactions. I use both plain Squeryl and Squeryl-Record with the Lift web framework.

Others may of course need other isolation levels for other databases for entire sessions (rather than single transactions), so general answers are preferable.

Update:

I ended up with this modified version of Dave Whittaker's code:

def transactionWith[T](isolation: Int)(block: => T): T =
  transaction {
    val connection = Session.currentSession.connection
    connection.rollback // isolation cannot be changed in the middle of a tx
    connection.setTransactionIsolation(isolation)
    block
  }

The thing is that if a transaction has already been started, you can't change the isolation level. This was the case for me and without the rollback I would get:

org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.

As long as I am using transaction{} and not inTransaction{} I think doing an immediate rollback should do no harm.

The isolation level should be reset after transaction{} commits or rollbacks, but before the connection is returned to the connection pool. I'm not sure how to accomplish that. But in my case the c3p0 connection pool seems to reset the isolation level and every transaction{} starts with default isolation level even if I never clean them up myself.

The thing I'm not so happy with is the exception when there is a conflict. I would like to catch such an exception specifically and retry the transaction. But it's just a generic runtime exception:

java.lang.RuntimeException: Exception while executing statement : ERROR: could not serialize access due to concurrent update

It wraps another exception which unfortunately also is generic (org.postgresql.util.PSQLException).

Not perfect, but it does the job until Squeryl hopefully gets support for transaction isolation. I am using the above code with Squeryl 0.9.4.

Answer 1

Right now it would be a somewhat manual process. If you need it for the entire session then I suppose you could simply set the appropriate level in your SessionFactory, i.e.

SessionFactory.concreteFactory = Some(()=> {
  val connection = java.sql.DriverManager.getConnection("...")
  connection.setTransactionIsolation(...)
  Session.create(connection, new PostgreSqlAdapter)
 })

For a single transaction it would be a bit more difficult. You can access the current session with Session.currentSession or Session.currentSessionOption and you would have to set the isolation level before your transaction occurred and then set it back afterwards. Of course, it wouldn't be too difficult to create your own function that does just that:

def transactionWith(isolation: Int)(block: => T): T = {
  trasaction{
    val connection = Session.currentSession.connection
    val oldIsolation = connection.getTransactionIsolation()
    connection.setTransactionIsolation(isolation)
    try {
      block
    } finally {
      connection.setTransactionIsolation(oldIsolation)
    }
  }
}

Then you would use it like

transactionWith(Connection.TRANSACTION_SERIALIZABLE){
   from(blablabla)(......)
}

I think that would work but a) I'm not entirely sure when the isolation level is supposed to be set, I'm assuming that setting it within the current transaction before executing any other statements will work and b) I haven't tried to compile the above so there may be syntactic errors. Anyway, I think it will give you the general idea.