Here are some options you can use to serialize access to rows. Regardless of which of these you use, I highly recommend also having a table that logs balance changes on each account, like a ledger. This allows you to audit the total in the future, and debug any issues.
Option 1: Avoid read, modify, write patterns.
Instead of doing:
account = Repo.get(Account, account_id)
account |> Account.changeset(%{balance: account.balance + 5}) |> Repo.update!
Do:
Account |> where(id: ^account_id) |> update(inc: [balance: 5]) |> Repo.update_all([])
In this approach, you change the balance in a single SQL query instead of two, avoiding race conditions. Postgres will ensure that the single update query runs atomicly.
Option 2: SELECT FOR UPDATE
If you can’t avoid read, modify, write, then take a lock on the row you wish to update:
Repo.transaction(fn ->
account = Account |> lock("FOR UPDATE") |> Repo.get!(account_id)
account |> Account.changeset(%{balance: account.balance + 5}) |> Repo.update!
end)
This locks access to the account row for the duration of the transaction, allowing your process to safely reason about it.
If conflict is rare, you can also try doing Ecto.Changeset — Ecto v3.14.0 instead.
