transactions.md

Transactions

SQLite's Write Lock Limitation

SQLite allows only one write lock at a time. Any attempt to write while another transaction already holds the write lock will immediately fail—there is no waiting or queuing built in. This is fundamentally different from PostgreSQL, where conflicting transactions queue up and proceed in order.

Because of this, AshSqlite disables transaction support by default (can?(:transact) returns false). Without extra configuration, Ash will not wrap actions in transactions when using the SQLite data layer.

Enabling Reliable Concurrent Writes

ecto_sqlite3 exposes two knobs that together make concurrent writes behave more like you would expect:

• default_transaction_mode: :immediate — SQLite acquires the exclusive write lock at the start of each transaction instead of at the first write statement. This prevents the scenario where two transactions both start in deferred mode, both read successfully, and then race to upgrade to a write lock, causing one to fail.

• busy_timeout — SQLite will retry acquiring the write lock for up to this many milliseconds before returning an error. Set this to a non-zero value so that a brief contention window does not immediately surface as an error to your users.

Example repo configuration:

# config/config.exs
config :my_app, MyApp.Repo,
  database: "path/to/my_app.db",
  pool_size: 1,
  default_transaction_mode: :immediate,
  busy_timeout: 5000

Keep pool_size: 1 for writes {: .warning}

SQLite does not support parallel writes, so a write pool larger than 1 will only cause contention. Set pool_size: 1 on any repo that performs writes.

Separate Read and Write Repos

For applications that need read concurrency, you can configure a dedicated read-only repo alongside a write repo. The write repo uses pool_size: 1 and immediate transactions; the read repo opens multiple read-only connections.

# config/config.exs
config :my_app, MyApp.Repo,
  database: "path/to/my_app.db",
  pool_size: 1,
  default_transaction_mode: :immediate,
  busy_timeout: 5000

config :my_app, MyApp.Repo.ReadOnly,
  database: "path/to/my_app.db",
  pool_size: 10,
  read_only: true

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

defmodule MyApp.Repo.ReadOnly do
  use AshSqlite.Repo, otp_app: :my_app
end

Start both repos in your application supervision tree:

# lib/my_app/application.ex
children = [
  MyApp.Repo,
  MyApp.Repo.ReadOnly,
  ...
]

Then route reads and writes to the appropriate repo using a function in the repo DSL option:

sqlite do
  repo fn _resource, type ->
    case type do
      :mutate -> MyApp.Repo
      :read -> MyApp.Repo.ReadOnly
    end
  end
  table "posts"
end

The function receives the resource module and either :read or :mutate as arguments and must return a repo module.