title: Setting a transaction isolation level in Squeryl
body: <p>How can I set a transaction isolation level using Squeryl?</p>
  
  <p>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.</p>
  
  <p>Others may of course need other isolation levels for other databases for entire sessions (rather than single transactions), so general answers are preferable.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>I ended up with this modified version of Dave Whittaker's code:</p>
  
  <pre><code>def transactionWith[T](isolation: Int)(block: =&gt; T): T =
    transaction {
      val connection = Session.currentSession.connection
      connection.rollback // isolation cannot be changed in the middle of a tx
      connection.setTransactionIsolation(isolation)
      block
    }
  </code></pre>
  
  <p>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:</p>
  
  <blockquote>
    <p>org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.</p>
  </blockquote>
  
  <p>As long as I am using transaction{} and not inTransaction{} I think doing an immediate rollback should do no harm.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>java.lang.RuntimeException: Exception while executing statement : ERROR: could not serialize access due to concurrent update</p>
  </blockquote>
  
  <p>It wraps another exception which unfortunately also is generic (org.postgresql.util.PSQLException).</p>
  
  <p>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.</p>