How can I pessimistically lock multiple rows in Rails?

Question

I am trying to pessimistically lock a subset of a table (Postgres) for conditional inserts and am having a heck of a time finding a syntax that works. Here's basically what I'm trying to do:

ActiveRecord::Base.transaction do
  if consumer.purchases.lock.sum(&:amount) < some_threshold
    consumer.purchases.create!(amount: amount)
  end
end

Unfortunately the above doesn't work. But it feels like it should. I just need to lock all the rows for a specific consumer without locking the whole table. And unfortunately, I'm dealing with real money, it's a ledger of sorts, so it's gotta be bulletproof.

consumer.purchases.lock.to_sql results in SELECT "purchases".* FROM "purchases" WHERE "purchases"."consumer_id" = ? FOR UPDATE as I would expect, but for some reason, chaining .create! causes the query builder to remove the FOR UPDATE lock.

Ok, so I've pulled it apart and tried various things that I think should work but DON'T:

# Process 1
ActiveRecord::Base.transaction do
  consumer.purchases.tap{ |p| p.lock! }.create!(amount: amount)
  sleep 20
end

# Process 2
ActiveRecord::Base.transaction do
  consumer.purchases.tap{ |p| p.lock! }.create!(amount: amount)
  # Should wait but doesn't
end

ActiveRecord::Base.transaction do
  purchases = Consumer.find(3).purchases
  purchases.lock
  purchase = purchases.new(amount: amount)
  purchase.save!
  sleep 20
end

... Other process doesn't wait...

The only way I CAN get it working is if I lock the rows iteratively; this DOES work:

# DOES WORK!
# Process 1
ActiveRecord::Base.transaction do
  purchases = Consumer.where(id: 3).first.purchases
  purchases.each(&:lock!)
  purchase = purchases.new(amount: amount)
  purchase.save!
  sleep 20
end

# Process 2
ActiveRecord::Base.transaction do
  purchases = Consumer.where(id: 3).first.purchases
  purchases.each(&:lock!)
  purchase = purchases.new(amount: amount)
  purchase.save!
  # waits as it should
end

But I can't be asked to lock them iteratively, that's insane :) So I got to thinking maybe it's a weird Postgres quirk? (I'm much more familiar with MySQL), so I did it by hand in Postgres and it works no problem:

BEGIN;
SELECT * FROM purchases WHERE consumer_id = 3 FOR UPDATE;
SELECT pg_sleep(30);
INSERT INTO purchases (name, amount) VALUES ('shouldBlock30Seconds', '1000');
END;

BEGIN;
SELECT * FROM purchases WHERE consumer_id = 3 FOR UPDATE;
INSERT INTO purchases (name, amount) VALUES ('shouldWait30Seconds', '1000');
END;

BEGIN;
SELECT * FROM purchases WHERE consumer_id = 24839992 FOR UPDATE;
INSERT INTO purchases (name, amount) VALUES ('shouldInsertImmediately', '1000');
END;

shouldInsertImmediately gets inserted right away, shouldBlock30Seconds gets inserted after 30s, and shouldWait30Seconds gets inserted immediately after.

I'm pulling my hair out :) Has anyone run across this before or am I just very tired and missing something obvious?

(Rails 5.1.7, Ruby 2.4.1, Postgres 11.6)

Answer 1

purchases.lock does not lock anything, it only returns a new relation, that will lock records on fetch.

Try forcing the select: purchases.lock.to_a