CODEWITHSUNDEEP
elixirecto
Nema Ga
Nema Ga

Reputation: 2600

Ecto/SQL - atomic get / set

I have credits column that I need to check(if value > 0) and at the same time subtract 1 (multiple processes/connections will be bombarding db at the same time so it needs to be done in atomic fashion).

Is it enough to just start transaction and get + update row from there? Like:

Repo.transaction(fn ->
  user = Repo.get(User, user_id)
  if user.credits >= 1 do
    MyRepo.update!(%{user | credits: user.credits - 1})
    # ... If above works, create row in another table here. 
  end 
end)

Does this put "lock" on user row? (imagine another process wanting to update credits in the middle of transaction above).

Upvotes: 0

Views: 746

Answers (1)

Dogbert
Dogbert

Reputation: 222278

Does this put "lock" on user row? (imagine another process wanting to update credits in the middle of transaction above).

It does not. Here's a simple demonstration:

alias MyApp.{Repo, User}
import Ecto.{Changeset, Query}

user_id = Repo.insert!(%User{credits: 1}).id

pid = spawn(fn ->
  user = Repo.get!(User, user_id)
  caller = receive do caller -> caller end
  Repo.update!(user |> change |> put_change(:credits, -5))
  send(caller, :cont)
end)

Repo.transaction(fn ->
  user = Repo.get!(User, user_id)
  IO.inspect {:before, user.credits}
  if user.credits >= 1 do
    # Ask the other process to update a column.
    send(pid, self)
    # Wait until the other process is done.
    receive do :cont -> :cont end
    IO.inspect {:middle, Repo.get!(User, user_id).credits}
    Repo.update!(user |> change |> put_change(:credits, user.credits - 1))
  end 
  IO.inspect {:after, Repo.get!(User, user.id).credits}
end)

Output:

{:before, 1}
{:middle, -5}
{:after, 0}

One way to fix this is to obtain a row-level lock like FOR UPDATE. This will ensure that no other changes are made to the same row until the current transaction is committed or aborted.

Changing

user = Repo.get!(User, user_id)

to

user = from(u in User, where: u.id == ^user_id, lock: "FOR UPDATE") |> Repo.one!

in the transaction in the above snippet will (rightfully) create a deadlock as the other process's SELECT will not return until the transaction is complete, and the transaction will not proceed until the other process gets the user.

So, in conclusion, you should replace:

user = Repo.get(User, user_id)

with

user = from(u in User, where: u.id == ^user_id, lock: "FOR UPDATE") |> Repo.one!

if you want to prevent updates to the returned user until your transaction is complete.

(Disclaimer: I'm not a Database expert but I believe what I've written above is correct. If anyone has any doubts or questions, please comment!)

Upvotes: 4

Related Questions