How do i set Snapshot isolation level for transaction?

[DenisNovac]

Hello. I've noticed there is no snapshot isolation level in doobie enum:

https://github.com/tpolecat/doobie/blob/main/modules/core/src/main/scala/doobie/enumerated/transactionisolation.scala

object TransactionIsolation {

  /** @group Values */ case object TransactionNone            extends TransactionIsolation(TRANSACTION_NONE)
  /** @group Values */ case object TransactionReadUncommitted extends TransactionIsolation(TRANSACTION_READ_UNCOMMITTED)
  /** @group Values */ case object TransactionReadCommitted   extends TransactionIsolation(TRANSACTION_READ_COMMITTED)
  /** @group Values */ case object TransactionRepeatableRead  extends TransactionIsolation(TRANSACTION_REPEATABLE_READ)
  /** @group Values */ case object TransactionSerializable    extends TransactionIsolation(TRANSACTION_SERIALIZABLE)

...}

I am using this solution to set levels: https://github.com/tpolecat/doobie/issues/1837#issuecomment-1476745441

Is there any workaround for that?

[jatcwang]

I've added a comment in https://github.com/tpolecat/doobie/issues/1837#issuecomment-2060973978 which allow you to set the isolation level using an int. Hope it helps!

[jatcwang]

I'm curious though. Which database you're using that you're trying to set a "snapshot" isolation level?

[DenisNovac]

Microsoft SQL Server.

Also i've checked Java level and MS SQL Snapshot is code 5 but java.sql doesn't have such level in it's enum it seems.

    int TRANSACTION_NONE             = 0;
    int TRANSACTION_READ_UNCOMMITTED = 1;
    int TRANSACTION_READ_COMMITTED   = 2;
    int TRANSACTION_REPEATABLE_READ  = 4;
    int TRANSACTION_SERIALIZABLE     = 8;

ms sql check of level:

 SELECT CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
    FROM sys.dm_exec_sessions
    where session_id = @@SPID

Or it doesn't have to correspond?

[jatcwang]

Your JDBC driver ultimately handles the intepretation of the int you passed to setTransactionIsolationLevel. It's best to follow the documentation of your specific JDBC driver.

https://github.com/microsoft/mssql-jdbc/blob/main/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java#L4904 https://github.com/microsoft/mssql-jdbc/blob/main/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java#L4593

Seems like what you want to pass in is SQLServerConnection.TRANSACTION_SNAPSHOT

[DenisNovac]

I see, thanks!