Discussion of how to deal with concurrent write transactions.

[jimsynz]

So there's a small issue with Sqlite where only one transaction can obtain a write lock at a time, and any attempt to write (not even commit) while another transaction has a write lock causes an error which leaves no option other than rolling back the failed transaction.

Obviously this is not how Ash expects a transaction supporting data layer to behave, which is why transactions are explicitly disabled in ash_sqlite. Unfortunately there's not really any indication that this is the default as there's nothing in the docs that indicate that this is the case - in fact there's a couple of places that talk about using transactions.

There is reason to hope that Sqlite will get at least less surprising transaction behaviour via the BEGIN CONCURRENT proposal, however it currently lives outside of the main tree and is not shipped by default.

Proposals:

1. Doc changes that very prominently explain ash_sqlite's inability to run actions in a transaction.
2. A guide to using a reactor to replace action hooks for transaction-like behaviour.
3. A verifier (in core) that explicitly warns when any action does not have transaction? false when the data layer does not support transacting.
4. A runtime check (in core) that raises an error when before or after action hooks are used with a data layer that does not support transacting.
5. A verifier (in core) that verifies that the resource's data layer supports transacting when using the before_action and after_action builtin changes.
6. Engage with ecto_sqlite3 and exqlite package maintainers to try and find a better solution.

Relevant links:

• BEGIN CONNCURRENT: https://sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
• Concurrent sandbox support with begin concurrent elixir-sqlite/ecto_sqlite3#99
• ecto_sqlite3 on Hex: https://hex.pm/packages/ecto_sqlite3
• exqlite on Hex: https://hex.pm/packages/exqlite

[jimsynz]

Okay, after having a shower thought and discussing it with @zachdaniel here's a new proposal:

1. Add write_transactions? to the sqlite DSL section so that folks can explicitly opt in to transaction support.
2. Add AshSqlite.select_repo/2 which implements the expected behaviour for the sqlite.repo DSL callback - this currently doesn't exist and will have to be copied from postgres.repo.

The idea being that folks can create a separate repo just for writes, and configure the pool size to 1 which effectively forces all write transactions to be serialised just how sqlite wants them. We can even add an igniter code patcher which makes the required changes to existing repos in a codebase.

I still think proposals 3..6 from above are important and 1 should be modified to provide a guide to managing transactions with SQLite including the above solution.

[egze]

Fyi: There is now a default_transaction_mode on the adapter level, where you can set it to immediate.

elixir-sqlite/exqlite#308
elixir-sqlite/ecto_sqlite3#155

[a-nassim]

I am very interested in this and was wondering what the current state of the discussion is.

There have been some efforts in the Rails community to make the out-of-the-box experience with SQLite more production-ready as shown in this blog or this derived talk.

I would love to see a similar experience for SQLite in Elixir/Ash.

I experimented with two approaches:

1. Acquiring a write lock immediately at the start of a transaction, retrying if the lock is already held. a-nassim@d75eeff (+ optionally in exqlite a-nassim/exqlite@ae3a588)

   • This mimics the default behaviour of new Rails 8 apps with SQLite.

2. Having distinct RW pools as suggested above, not the exact interface but the same idea for testing. a-nassim@ef6d7a9

   • Could be combined with the first approach to prevent failures from concurrent write by external processes (e.g. sqlite3 CLI)
   Setup

   # config/dev.exs
   config :my_app, MyApp.Repo,
     database: Path.expand("../my_app_dev.db", __DIR__),
     pool_size: 1,
     stacktrace: true,
     show_sensitive_data_on_connection_error: true,
     # default_transaction_mode: :immediate, # if combined with the first approach
     busy_timeout: 0,
     # queue_interval: 15000 # might need to tune https://hexdocs.pm/db_connection/DBConnection.html#start_link/2-queue-config
   
   config :my_app, MyApp.Repo.Read,
     database: Path.expand("../my_app_dev.db", __DIR__),
     pool_size: 5,
     stacktrace: true,
     show_sensitive_data_on_connection_error: true,
     # default_transaction_mode: :deferred, # this is already the default
     read_only: true

   # lib/my_app/repo.ex
   defmodule MyApp.Repo do
     use AshSqlite.Repo,
       otp_app: :my_app
   
     defmodule Read do
       use AshSqlite.Repo,
         otp_app: :my_app
     end
   end

   # lib/my_app/application.ex
   
   def start(_type, _args) do
     children = [
       ...
       MyApp.Repo,
       MyApp.Repo.Read,
       ...
     ]
   
     opts = [strategy: :one_for_one, name: MyApp.Supervisor]
     Supervisor.start_link(children, opts)
   end

In my limited testing, both approaches seem to work so I am curious whether an official solution is being considered.

Thank you

[jimsynz]

I have certainly been investigating solutions that might work. My current approach is to do:

# config/config.exs
config :my_app, ecto_repos: [MyApp.ReadRed, MyApp.WriteRepo]
config :my_app, MyApp.ReadRepo, pool_size: 10
config :my_app, MyApp.WriteRepo, pool_size: 1, enable_transactions?: true

# some resource
sqlite do
  repo fn 
    _, :mutate -> MyApp.WriteRepo
    _, :read -> MyApp.ReadRepo
  end
end

Unfortunately AshSqlite doesn't support the function-in-place-of-repo repo option, but AshPostgres does, so it's definitely possible to add it. I've also been working up a PR to add transaction support back into the datalayer behind a flag, but despite it basically being a copy pasta of the AshPostgres datalayer, it still seems more challenging than I'd like it to be.

[zachdaniel]

It would be very easy to add the function support I think.

[a-nassim]

Thank you for the updates!

I just noticed this PR #95 which I assume is the one you are referring to
In case it helps with the ongoing work, I experimented with a similar setup and managed to add function support by removing AshSqlite.Transformers.VerifyRepo a-nassim@7a6bb74

Setup

# config/dev.exs
config :my_app, MyApp.Repo,
  database: Path.expand("../my_app_dev.db", __DIR__),
  pool_size: 1,
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  default_transaction_mode: :immediate,
  busy_timeout: 0,
  queue_interval: 15000,
  enable_transactions?: true

config :my_app, MyApp.Repo.Read,
  database: Path.expand("../my_app_dev.db", __DIR__),
  pool_size: 5,
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  default_transaction_mode: :deferred,
  read_only: true,
  custom_pragmas: [
    query_only: true
  ]

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use AshSqlite.Repo,
    otp_app: :my_app

  defmodule Read do
    use AshSqlite.Repo,
      otp_app: :my_app
  end

  def select_repo(_resource, kind) do
    case kind do
      :mutate -> MyApp.Repo
      :read -> (MyApp.Repo.in_transaction?() && MyApp.Repo) || MyApp.Repo.Read
    end
  end
end

# lib/my_app/domain/resource.ex
defmodule MyApp.Domain.Resource do
  use Ash.Resource,
    otp_app: :my_app,
    data_layer: AshSqlite.DataLayer

  sqlite do
    table "resources"
    repo &MyApp.Repo.select_repo/2
  end
end

Looking forward to seeing official support!