Ecto postgres database simultaneous update

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

That’s well noted.

Thanks very much @benwilson512

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.

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

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

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.

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

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

Why does this matter?

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.

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