postgresql Rails ActiveRecord - 如何锁定表以供阅读？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24587403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails ActiveRecord - how can I lock a table for reading?
提问by Niels B.
I have some Rails ActiveRecord code that looks like this:
我有一些如下所示的 Rails ActiveRecord 代码：
new_account_number = Model.maximum(:account_number)
# Some processing that usually involves incrementing
# the new account number by one.
Model.create(foo: 12, bar: 34, account_number: new_account_number)
This code works fine on its own, but I have some background jobs that are processed by DelayedJob workers. There are two workers and if they both start processing a batch of jobs that deal with this code, they end up creating new Modelrecords that has the same account_number, because of the delay between finding the maximum and creating a new record with an even higher account number.
这段代码本身运行良好，但我有一些由 DelayedJob 工作人员处理的后台作业。有两个工人，如果他们都开始处理处理此代码的一批作业，他们最终会创建Model具有相同 account_number 的新记录，因为找到最大值和创建具有更高帐户的新记录之间存在延迟数字。
For now, I have solved it by adding a uniqueness constraint at database level to the models table and then retry by re-selecting the maximum in case this constraint triggers an exception.
现在，我通过在数据库级别向模型表添加唯一性约束来解决它，然后通过重新选择最大值来重试，以防此约束触发异常。
However it feels like a hack.
然而，这感觉就像一个黑客。
Adding auto incrementing at database level to the account_numbercolumn is not an option, because the account_number assigning entails more than just incrementing.
在数据库级别向account_number列添加自动递增不是一个选项，因为 account_number 分配需要的不仅仅是递增。
Ideally I would like to lock the table in question for reading, so no other can execute the maximum select query against the table until I am done. However, I'm not sure how to go about that. I'm using Postgresql.
理想情况下，我想锁定有问题的表以供读取，因此在我完成之前，没有其他人可以对该表执行最大选择查询。但是，我不知道该怎么做。我正在使用 Postgresql。
回答by Max Wallace
Based on the ActiveRecord::Locking docsit looks like Rails doesn't provide a built-in API for table-level locks.
根据ActiveRecord::Locking 文档，Rails 似乎没有为表级锁提供内置 API。
But you can still do this with raw SQL. For Postgres, this looks like
但是您仍然可以使用原始 SQL 执行此操作。对于 Postgres，这看起来像
ActiveRecord::Base.transaction do
ActiveRecord::Base.connection.execute('LOCK table_name IN ACCESS EXCLUSIVE MODE')
...
end
The lock must be acquired within a transaction, and is automatically freed once the transaction ends.
锁必须在事务中获取，并且在事务结束后自动释放。
Note that the SQL you use here will be different depending on your database.
请注意，您在此处使用的 SQL 将因您的数据库而异。
回答by kaspernj
There are already answers on how to lock the entire table, but I believe you should try to avoid that. Instead I believe you should give advisory locks a look. It makes sure the same block of code isn't executed on two machines simultaneously, while still keeping the table open for other business.
已经有关于如何锁定整个表的答案，但我相信您应该尽量避免这种情况。相反，我认为您应该看看咨询锁。它确保同一代码块不会同时在两台机器上执行，同时仍然为其他业务保持开放。
It still uses the database, but it doesn't lock your tables.
它仍然使用数据库，但它不会锁定您的表。
You can use the gem called "with_advisory_lock" like this:
您可以像这样使用名为“with_advisory_lock”的 gem：
Model.with_advisory_lock("ADVISORY_LOCK_NAME") do
# Your code
end
https://github.com/ClosureTree/with_advisory_lock
https://github.com/ClosureTree/with_advisory_lock
It doesn't work with SQLite.
它不适用于 SQLite。
回答by Nikita Shilnikov
Setting unique constraint IS NOT a hack. It is thing that makes your data consistent. By the way you have a few more options here:
设置唯一约束不是黑客。它使您的数据保持一致。顺便说一下，您在这里还有更多选择：
Lock some DB resource (e.g. it could be a unique record) using SELECT FOR UPDATE or PostreSQL's Advisory Locks (see docs).
Use a sequence (docs).
The main difference between two approaches is #1 does not allow to have gaps in your numbers because other session will wait for transaction commit and #2 allows.
两种方法之间的主要区别是 #1 不允许您的数字存在差距，因为其他会话将等待事务提交，而 #2 允许。
回答by Ehud
you don't have to lock the hall table to lock a piece of code for a single process at a time. locking a full table causes performence problems.you can lock a single same row all the time with "with_lock" method.this way code is fully protected. no extra gem is needed. it also creates a transaction. like this:
m = Model.order(:id).first
m.with_lock do #aquire lock
#some code here for a single process at a time
end #release lock
您不必锁定大厅表来一次锁定单个进程的一段代码。锁定一个完整的表会导致性能问题。您可以使用“with_lock”方法始终锁定同一行。这样代码就得到了完全的保护。不需要额外的宝石。它还创建了一个事务。像这样：
m = Model.order(:id).first
m.with_lock do #aquire lock
#some code here for a single process at a time
end #release lock
回答by Helio Santos
You can use the lockmethod from ActiveRecord::Locking::Pessimistic.
您可以使用lock从方法的ActiveRecord ::锁定::悲观。
回答by TerryYin
Well, technically it's the same to lock a table or to always lock a record of another table before accessing the table.
好吧，从技术上讲，锁定一个表或在访问该表之前始终锁定另一个表的记录是相同的。
So you may have another table with max one record, alway lock that record with http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.htmlbefore read/write from the table you want to lock:
因此，您可能有另一个最多包含一条记录的表，在从要锁定的表读取/写入之前，请始终使用http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html锁定该记录：
LockTable.last.with_lock do
// the things that needed for your table
end
