jerry

Ecto postgres database simultaneous update

Hello All,

I have an issue I need your assistance on.

I have a table that contains unique account information on my customers.
Whenever there are simultaneous/concurrent updates on an account record, the resulting balance is wrong.

What is the best way to handle this in order to ensure that even when there are multiple update requests on an account in the table, the updates are performed one by one in a such a sequential manner to ensure the balances are correct in the account table?

I would be sincerely grateful for your assistance.

Thanks.

Jerry

17 comments

#ecto

0 3416 17

2020-06-01 16:09:54 UTC

Marked As Solved

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

To be clear update_all isn’t the thing that makes the difference here, it’s that you’re using inc to do the math in the database, and inc requires update_all.

For example this uses update_all but is still wrong

new_balance=current_balance + 5

Account |> where(id: ^account_id) |> update(set: [balance: new_balance]) |> Repo.update_all([])

The math is happening in Elixir, so it’s subject to race conditions. If you want to do the math in Elixir you need to use a lock like I showed you in option #2. That lock requires a transaction, it doesn’t matter whether that transaction is handled via Multi or via Repo.transaction with an anonymous function.

Post #16

Also Liked

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.

Post #2

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

You can find more information here: Ecto.Query — Ecto v3.14.0

Post #8

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

account_id=record.account_id
current_balance=record.balance

new_balance=current_balance + 5

Account |> where(id: ^account_id) |> update(set: [balance: new_balance]) |> Repo.update_all([])

This is still a read, modify, write. This will not work safely. You must use inc: so that the change happens in a single atomic operation, OR you have to explicilty lock.

Post #13