Feature Proposal: Transaction Isolation Level as a Repo level property

[Vietor Davis]

Previously there was discussion of setting transaction isolation levels on a per-transaction basis, which turned out to have a lot of unexpected complexities:
https://github.com/elixir-ecto/ecto/issues/1460

As discussed in that issue, trying to set them manually within a transaction *also* has significant problems. Which leaves us in a place where it's very hard to use transaction isolation levels with Ecto.

Rather than trying to set it per-transaction, or manually with raw SQL statement, I think the right place to set transaction isolation level is on the Repo definition. Not just because the other options didn't work, but also because I think it's a cleaner abstraction, requires less code in use, and provides me with fewer opportunities to make errors when using it.

Rather than thinking of a repo as *being* a storage backend, we just need to remember that it's one *view* of a storage backend, and it's reasonable that a single application might want different views of the same backing data, with different constraints on each default view. When I'm writing code that needs to be serializable, I want a completely serializable view of the DB, where every DB action is, by default, part of that serializable world. When I forget that it's usually a bug, and when it's not a bug, I can override it by setting another level using raw SQL. (That will continue to work for all cases where it would currently work.)

If that sounds reasonable, I'll put a PR together and we can see how it shapes up.

[José Valim]

> As discussed in that issue, trying to set them manually within a transaction *also* has significant problems. Which leaves us in a place where it's very hard to use transaction isolation levels with Ecto. 

What are those significant problems?

Today there is an after_connect callback which you can use to set the transaction right after the connection starts. if you want to set a transaction level, I would recommend using it as we don't have plans to wrap it in options.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/c3744a88-73e8-49a3-b3f9-1ee49f6d7782%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vietor Davis]

That's perfect! I'd missed :after_connect in the docs, and it will let me implement exactly what I'd had in mind.

The manual setting issue I was running into was the same mentioned here: https://github.com/elixir-ecto/ecto/issues/1460#issuecomment-223274048

Thanks!

[José Valim]

after_connect is still the way to go, definitely. :)

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

On Tue, Aug 2, 2016 at 10:37 PM, Wojtek Mach <woj...@wojtekmach.pl> wrote:

Hello,

It looks like `after_connect` is an option for the Repo adapter configuration - does it mean that if we want to set the isolation level through this hook all transactions in the app will use the same isolation level? What if I just have one place in system when I need to set isolation to serializable and all the other transactions can use default level?

Also, I'm probably doing this wrong, but I tried using after_connect like this:

config :app, Repo,
  adapter: Ecto.Adapters.Postgres,
  # ...
  pool: Ecto.Adapters.SQL.Sandbox,
  after_connect: &Repo.after_connect/1

defmodule Repo do
  # ...

  def after_connect(_conn) do
    {:ok, _} = Ecto.Adapters.SQL.query(Repo, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", [])
  end
end

and I got timeout, probably due to not using ownership mechanism correctly in this particular case:

22:22:27.268 [error] GenServer #PID<0.1450.0> terminating
** (stop) exited in: :gen_server.call(#PID<0.1446.0>, {:checkout, #Reference<0.0.7.1039>, true, :infinity}, 5000)
    ** (EXIT) time out
    (db_connection) lib/db_connection/poolboy.ex:112: DBConnection.Poolboy.checkout/3

I needed to use isolation levels on a couple of projects and I ended up copy-pasting how HexWeb handles it: https://github.com/hexpm/hex_web/blob/master/lib/hex_web/repo.ex#L14:L26. The consensus on https://github.com/elixir-ecto/ecto/issues/1460 was that the option shouldn't be implemented on the driver level but perhaps it could be reconsidered to introduce it on the repo transaction call as originally proposed in #1460?

If setting `after_connect` is still the recommended way of setting isolation level, I'd be happy to take a stab at documenting it in Ecto API docs for future reference - what's the best place to put it? https://github.com/elixir-ecto/ecto/blob/v2.0.3/lib/ecto/repo.ex#L737?

Regards,

Wojtek