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…

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: [bala…

[image] jerry: Whenever there are simultaneous/concurrent updates on an account record, the resulting balance is wrong. 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…

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…

[image] jerry: 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. 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 …

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

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 additi…

Thanks so much for the clarification, @hauleth . I really appreciate it. What does the below mean, please? especially, the inc [image] benwilson512: update(inc: [balance: 5])

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

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 befor…

[image] jerry: Can I use a normal select query to retrieve the value of account_id in order to execute the query statement below? If all you’re doing is grabbing the account id to then run this query, yes. [image] jerry: How do I ensure that another process doesn’t retrieve that same val…

Elixir Programming Language Forum

Ecto postgres database simultaneous update

Questions & Help Questions

benwilson512 May 27, 2020, 3:06pm 8

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