Question: Is it possible to use immediate transactions?

[crbelaus]

Ruby on Rails is making many changes to improve SQLite support. Apparently, one of the most noticeable is using BEGIN IMMEDIATE TRANSACTION. Quoting from this article from the author of such improvements for RoR (it is a highly recommended read for anyone using SQLite):

By default, SQLite uses a deferred transaction mode. This means that SQLite will not acquire the lock until a write operation is made inside the transaction.
In a context where you only have one connection or you have a large amount of transactions that only do read operations, this is great for performance, because it means that SQLite doesn’t have to acquire a lock on the database for every transaction, only for transactions that actually write to the database.

The he goes about how 99% of the time starting a transaction in Ruby on Rails means that there will be a database write. The conclusion is that immediate transactions should be the default for most cases.

This made me wonder a few things:

1. Is it possible to use immediate transactions with Ecto?
2. If not, would it be possible to add support for them? I could help if this feature seems interesting to have.
3. Would it be interesting to replicate the RoR behaviour in Ecto as well and make immediate transactions the default? I've been thinking about it and the fact that most transactions result in a database write seems true in my experience.

[warmwaffles]

Great question, yes it is 100% now

https://github.com/elixir-sqlite/exqlite/blob/fec606ccc8a291526d1e3137099b6acf0f6f84fe/lib/exqlite/connection.ex#L263-L280

You need to set the transaction mode to :immediate

Check out the transaction documentation for Ecto in general https://github.com/elixir-sqlite/exqlite/blob/fec606ccc8a291526d1e3137099b6acf0f6f84fe/lib/exqlite/connection.ex#L263-L280

EDIT:

Something like this repo.insert(changeset, mode: :exclusive)

[warmwaffles]

I wonder if it is possible to set the default transaction mode for ecto rather than being forced to pass it everytime. I will need to dig further.

[warmwaffles]

@josevalim or @ruslandoga or @greg-rychlewski what is your opinion on setting the default transaction mode for this adapter to :immediate rather than :deferred. I don't really have a guide to look at for the SQL adapters because, well, those databases don't provide this level of control on transactions.

• postgrex
• myxql
• tds

[crbelaus]

I am trying to set the :mode option but it doesn't seem to have any effect. I am not sure if I am using it correctly though.

iex(4)> Repo.insert(%Person{name: "Cristian"}, mode: :immediate)
# [debug] QUERY OK source="persons" db=0.8ms idle=936.3ms
# INSERT INTO "persons" ("name") VALUES (?) RETURNING "id" ["Cristian"]

iex(5)> Repo.transaction fn repo ->
...(5)>   repo.insert(%Person{name: "Cristian"}, mode: :immediate)
...(5)> end
# [debug] QUERY OK db=0.0ms idle=1807.7ms
# begin []
# ↳ :erl_eval.do_apply/7, at: erl_eval.erl:746
# [debug] QUERY OK source="persons" db=0.7ms
# INSERT INTO "persons" ("name") VALUES (?) RETURNING "id" ["Cristian"]
# ↳ anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4, at: # lib/ecto/adapters/sql.ex:1382
# [debug] QUERY OK db=0.2ms
# commit []

[warmwaffles]

I don't know if the debug logs spit out the BEGIN TRANSACTION preamble

[crbelaus]

Oh that makes sense. I will dig further.

[warmwaffles]

I do remember looking into this when I was first implementing this https://www.sqlite.org/lang_transaction.html

My goal was maximum read/write throughput. I opted for deferred because of the official documentation

IMMEDIATE causes the database connection to start a new write immediately, without waiting for a write statement. The BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.

[ruslandoga]

I would personally prefer to have the defaults match SQLite's, but since this adapter already uses WAL mode and foreign keys by default, I think it would be consistent to pick a transaction mode that works better in Ecto applications by default as well. We'll just need to make sure that reads don't start explicit transactions or if they do, they are not immediate. That might require some changes.

[warmwaffles]

We'll just need to make sure that reads don't start explicit transactions, that might require some changes.

That would definitely have to happen.

EDIT: I personally think it is wiser to leave that up to the caller to explicitly call out an exclusive or immediate lock is desired, but err on the side of deferred. I could document this better in the readme to point out the pitfalls of using the different modes and let the programmer decide when to use the mode.

[greg-rychlewski]

Regarding the logging, DBConnection used to log begin for everything until I made this change:: elixir-ecto/db_connection#297.

But it requires the adapter to changes its return value from handle_begin and I can't remember if I made a PR for that to exqlite.

[greg-rychlewski]

to point out the pitfalls of using the different modes

FWIW I feel like it's enough for a library to document the option, say briefly what it controls and then link to the database docs to get more information. In my experience, documenting things like pitfalls tends to have the same issue as online tutorials where they can become out of date. People should ideally not rely on Ecto to teach them how to use their database. That should be knowledge they have before touching Ecto.

That's just my $0.02 though. It's obviously your call!

[dkulchenko]

EDIT: I personally think it is wiser to leave that up to the caller to explicitly call out an exclusive or immediate lock is desired, but err on the side of deferred. I could document this better in the readme to point out the pitfalls of using the different modes and let the programmer decide when to use the mode.

I'd really love it if the default transaction mode could be a configurable option in the Ecto.Adapters.SQLite3 config (rather than having to specify immediate on every transaction invoke individually).

I use BEGIN IMMEDIATE for all my SQLite transactions across the board for this reason: https://kerkour.com/sqlite-for-servers#use-immediate-transactions

[ruslandoga]

@dkulchenko 👋

Would something like this work instead of adding extra configuration options?

defmodule MyApp.Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.SQLite3, otp_app: :my_app
  
  def immediate_transaction(fun_or_multi) do
    transaction(fun_or_multi, mode: :immediate)
  end
end

[dkulchenko]

@dkulchenko 👋

Would something like this work instead of adding extra configuration options?

defmodule MyApp.Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.SQLite3, otp_app: :my_app
  
  def immediate_transaction(fun_or_multi) do
    transaction(fun_or_multi, mode: :immediate)
  end
end

Good call! That definitely works too :)

[warmwaffles]

@ruslandoga, that's actually a super simple solution... I wish I had thought of it.