Only lock specific row using "FOR UPDATE OF" using Ecto?

Phoenix Forum Questions / Help
, ,
1

I’m using Ecto + Postgres, and I’d like to be able to lock only a specific row from a table, when I have a join clause. In Postgres you accomplish this by adding an OF <tablename> to the lock expression. For example:

In Postgres SQL:

# lock `users` but not `roles`
SELECT * FROM users INNER JOIN roles ON users.role_id = roles.id FOR UPDATE OF users;

Using Ecto, it seems like the only way to accomplish this is to hardcode the generated table alias (u0 here), which is pretty brittle:

User
|> join(:inner, [u] _ in assoc(u, :role))
|> lock("FOR UPDATE OF u0")

Is there any way to do it with Ecto in a non-brittle way?

Seems like fragments won’t work either.

I found this StackOverflow question as well. The conclusion was there’s no clean way to do it with Ecto, but double checking with the Forum.

Also, I’ll create a feature request ticket in github depending what I learn here.

2

Also in case anyone is curious or needs more info, here’s the relevant text in the Postgres docs which explains how the locking clause works with a list of tables:

If specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the SELECT are simply read as usual. A locking clause without a table list affects all tables used in the statement. If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query. However, these clauses do not apply to WITH queries referenced by the primary query. If you want row locking to occur within a WITH query, specify a locking clause within the WITH query.

3 Likes
3

Just to note here that it looks like this can now be done via a fragment, see - Allow fragment on locks by josevalim · Pull Request #189 · elixir-ecto/ecto_sql · GitHub. Would allow for something like:

from c in Call,
 join: ..., # custom methods
 lock: fragment("FOR UPDATE OF ?", c)
2 Likes