How can I have rails select for update on multiple rows?

Question

I can't figure out how to tell rails to SELECT ... FOR UPDATE on multiple rows. Poking around in console, Foo.where(bar: "baz").lock does produce the right SQL. But when I try to do it in a transaction, that ruby code doesn't actually generate the SQL. I think the one-off is working because the rails console automatically runs .all on the relation to display the results.

Here's what I run in console

Foo.transaction do
  Foo.where(bar: "baz").lock  
  Foo.where(bar: "baz").update_all(bar: "baz2")  
end

and the SQL:

BEGIN
  Foo Update All (0.5ms)  UPDATE "foos" SET "bar" = $1 WHERE "foos"."bar = $1 [["bar", "baz2"]]
COMMIT

If I change the lock line so that rails thinks that it needs to do somthing with the collection, like add puts to the beginning or pluck to the end...

Foo.where(bar: "baz").lock.pluck(:id)

...then I do get the expected FOR UPDATE query before the update happens, with the expense of having to pipe the result over the wire and put it into memory and then do nothing with it.

So it seems that rails properly uses .lock to modify the SQL of the relation, but there's no elegant way to actually invoke the lock. Am I doing something wrong or do we need to use some sort of workaround (like pluck) to get the query to happen?

score 3 · Answer 1 · answered Jan 09 '20 at 17:42

3

ApplicationRecord.transaction do
  User.where(id: [1, 2]).lock!.map do |user|
    puts user.id
    user.id
  end
end

produces

   (0.2ms)  BEGIN
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2) FOR UPDATE
1
2
   (0.9ms)  COMMIT
=> [1, 2]

answered Jan 09 '20 at 17:42

Pavel Mikhailyuk

2,757
9
17

@JohnBachir This is just example, that action in block is protected by `SELECT FOR UPDATE`. – Pavel Mikhailyuk Jan 11 '20 at 17:07
@JohnBachir May be I didn't get your initial question. What you are looking for? – Pavel Mikhailyuk Jan 11 '20 at 17:10
I asked this question over on DBA: https://dba.stackexchange.com/questions/257188/ – John Bachir Jan 12 '20 at 02:57
@JohnBachir `UPDATE` will auto lock the records - as noted in first comment @ dba.stackexchange. And my answer there was about "How can I have rails select for update on multiple rows". – Pavel Mikhailyuk Jan 12 '20 at 12:57

score 2 · Accepted Answer · answered Jan 13 '20 at 20:47

2

Inspired by discussion here: https://dba.stackexchange.com/questions/257188/

I think the best general purpose solution is

Foo.where(bar: "baz").order(:id).lock.pluck('')

Which will produce

SELECT  FROM "foos" WHERE "foos"."bar" = 'baz' ORDER BY "foos"."id" ASC FOR UPDATE;

answered Jan 13 '20 at 20:47

John Bachir

22,495
29
154
227

Update - I'm not sure what changed since I first experimented with this but it either was never working or or some reason stopped working in my project. YMMV -comment if it did or didn't work for you – John Bachir Dec 23 '20 at 01:08

How can I have rails select for update on multiple rows?

2 Answers2

Linked