Ecto postgres database simultaneous update

Questions / Help
1

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

2

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.11.1 instead.

15 Likes
3

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

4

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.

5

Hello @hauleth,

Considering the nature of the query statement in Option 2, does Option 1 give the same level of integrity to the data being modified?

Thanks.

Jerry

6

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.

7

Thanks so much for the clarification, @hauleth.
I really appreciate it.

What does the below mean, please? especially, the inc

8

You can find more information here: https://hexdocs.pm/ecto/Ecto.Query.html#update/3-operators

1 Like
9

Thanks very much @benwilson512.
Information seen.

Kindly let me run the tests and then I’ll feedback on the results.

I’m grateful to you all for the assistance.

Regards,

Jerry

10

Hello,

I have another question, please.

Please pardon me if my question sounds too trivial.

Can I use a normal select query to retrieve the value of account_id in order to execute the query statement below?
How do I ensure that another process doesn’t retrieve that same value of account_id before getting to this stage of running the update statement?

Thanks.

11

If all you’re doing is grabbing the account id to then run this query, yes.

Why does this matter?

12

I am retrieving the account_id and its corresponding current balance in order to go update the record with the new balance.

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([])

13 
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.

1 Like
14

Thanks very much @benwilson512. It’s clear to me now.

15

Is it permissible to use Ecto.Multi.update_all or Ecto.Multi to perform this transaction and achieve same atomic result?

16

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.

1 Like
17

That’s well noted.

Thanks very much @benwilson512

18

Thanks very much @benwilson512.
I’ve been able to implement the solution as you guided me to do.

Thanks @hauleth for your inputs as well.

It required a total rewrite of that aspect of the application’s engine.
This has been a very good learning process for me.

I shall provide some more feedback after some time of monitoring the application under heavy traffic.

Jerry