You can find more information here: Ecto.Query — Ecto v3.14.0
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
hauleth
Yes, each query is transaction within itself, so within single query it is impossible to have race condition (assuming that there are no bugs in DB implementation). I would say that DB is probably better optimised for single UPDATE queries than locking mechanisms as second one can impose some additional restrictions as DB do not know what you want to do with that lock.
0
hauleth
If you need just to update, then I think the 1st option will be better as you do not need to do 12 trips (at least as it is TCP) and you can just do everything in 6.
0
jerry
Many thanks @benwilson512.
I think the option two will work for me.
It’s a high traffic application, using Elixir and ecto_sql with postgres.
There is usually multiples of requests to the same account.
For each request, I need to reduce the account balance by the requested value before taking an action, hence the need to do read, modify, and write on the record.
Kindly let me try it out and feedback on the outcome.
Regards,
Jerry
0
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
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.
15