ruby-on-rails - 如何悲观地锁定Rails中的多行？

Question

我试图悲观地锁定表 (Postgres) 的一个子集以进行条件插入，并且我花了很长时间找到有效的语法。这基本上是我想要做的:

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

不幸的是，上述方法不起作用。但感觉应该是这样。我只需要锁定特定使用者的所有行而不锁定整个表。不幸的是，我在处理真钱，这是一种分类账，所以它必须是防弹的。
consumer.purchases.lock.to_sql结果 SELECT "purchases".* FROM "purchases" WHERE "purchases"."consumer_id" = ? FOR UPDATE正如我所料，但是
出于某种原因，链接 .create!导致查询生成器删除 FOR UPDATE锁。

好的，所以我把它拆开并尝试了我认为应该工作的各种方法，但 不要 :

# 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...

我的唯一途径 CAN 让它工作是如果我迭代地锁定行；此 做 工作:

# 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

但是不能要求我反复锁定它们，这太疯狂了:)
所以我开始想也许这是一个奇怪的 Postgres 怪癖？ (我更熟悉 MySQL)，所以我在 Postgres 中手工完成了它，它没有问题:

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立即插入， shouldBlock30Seconds 30 秒后插入，并且 shouldWait30Seconds之后立即插入。

我正在拔头发:)
有没有人以前遇到过这个问题，或者我只是很累并且错过了一些明显的东西？

(Rails 5.1.7、Ruby 2.4.1、Postgres 11.6)

Answer 1

purchases.lock不锁定任何内容，它只返回一个新关系，该关系将在获取时锁定记录。

尝试强制选择:purchases.lock.to_a