Select For Update in Elixir Transaction

Questions / Help
,
1

I am trying to avoid deadlock between my Ecto Transaction operating on the same Table. To do that I am trying to replicate Postgres “Select … for Update” in Ecto Multi.
Question is: Can I avoid lock here?

Query:

    query =
      from(s in Split,
        where: <condition>
        lock: fragment("FOR UPDATE OF ?", s),
        order_by: [asc: :id],
        select: %{
          id: s.id
        }
      )

    Repo.multi_update_all(
      multi,
      :updated_splits,
      from(s in Split,
        join: t in subquery(query),
        on: s.id == t.id,
        update: [
          set: [
            ....
          ]
        ]
       ),
      []
    )
2

AFAIK there is no difference between locking for update and selecting for update, if that is what you are getting at. Beyond that, to tell you if you can avoid the lock from a bigger picture perspective, I’d need to know a lot more about how it all fit together.

3

PostgreSQL has a bit more granular locks that might help you but indeed you need to share your scenario so we can recommend something.