scalikejdbc.DBConnection#readOnly does not honor isolationLevel

[dvoet]

NamedDB(dbName.name).isolationLevel(IsolationLevel.Serializable).readOnly does not propagate the isolation level down to the database connection. When using a connection pool, readOnly reuses the isolation level set by the previous use of the connection.

Our workaround sets the isolation directly on the connection:

  def readOnly[A](f: DBSession => A): A = {
    val db = NamedDB(name)
    db.conn.setTransactionIsolation(TRANSACTION_SERIALIZABLE)
    db.readOnly(f)
  }

[dvoet]

I also suspect using IsolationLevel.Default inherits the isolation level from the last transaction used by a connection in a pool.

https://github.com/scalikejdbc/scalikejdbc/blob/master/scalikejdbc-core/src/main/scala/scalikejdbc/Tx.scala#L27

[seratch]

Thanks for sharing this. The recommended way for performing queries with a particular isolation level is to use a localTx block. It works even when you do not run updates in it.

NamedDB(dbName.name).isolationLevel(IsolationLevel.Serializable).localTx { implicit s =>
   // select query here
}

[dvoet]

It is beneficial to use read only transactions when other transactions are serializable so the db can know they can’t cause a serialization failure.

…

On Thu, Dec 17, 2020 at 6:01 PM Kazuhiro Sera ***@***.***> wrote: Thanks for sharing this. The recommended way for performing queries with a particular isolation level is to use a localTx block. It works even when you do not run updates in it. NamedDB(dbName.name).isolationLevel(IsolationLevel.Serializable).localTx { implicit s => // select query here } — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1143 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AALTNNKRCWQCTS3NN44LYU3SVKEULANCNFSM4U4U2JZQ> .

[seratch]

Thanks for the prompt reply. While I am open to supporting read-only transactions, I am not going to work on it in the short term. Actually, we got a request #542 four years ago but it's not done yet. Please go with your workaround for now.

[dvoet]

Sure, I am not blocked by this. More importantly though is that the way isolation level is handled makes stateful changes to database connections and when pools are used there are situations where the prior use of a connection affects the current. In my particular case I use serializable transactions to write and read committed transactions to read, all using the same connection pool. Since read committed is the default I was not setting the isolation level. Eventually all connections were set to serializable leading to unexpected serialization failures. On the whole, scalikejdbc has been a pleasure to use. Thanks.

…

On Thu, Dec 17, 2020 at 8:00 PM Kazuhiro Sera ***@***.***> wrote: Thanks for the prompt reply. While I am open to supporting read-only transactions, I am not going to work on it in the short term. Actually, we got a request #542 <#542> four years ago but it's not done yet. Please go with your workaround for now. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1143 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AALTNNKUBXYXHDVT666V5ILSVKSRVANCNFSM4U4U2JZQ> .