How to set transaction's isolation level?

Questions / Help
, ,
1

With ecto_sql version 3.10.2 (for MySQL), the following code:

Repo.transaction(fn ->
  Repo.query!("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
  ...
end)

fails with:

[error] GenServer #PID<0.659.0> terminating
** (MyXQL.Error) (1568) Transaction characteristics can't be changed while a transaction is in progress

    query: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
   ...

I can workaround this by setting isolation level before opening a transaction and then changing it back afterwards. This is problematic, however, because I must make sure that previous isolation level is restored after the process is done and if the process crashes I cannot guarantee this.

1 Like
2

If this is not possible to do at database level, then you will have to build a supervisor to monitor and set isolation level.

I think the easier solution is to use a task supervisor and use the supervisor process to set the isolation level, in this way if the task fails you can catch the error and undo.