module Pessimistic

Pessimistic Locking

Locking::Pessimistic provides support for row-level locking using SELECT … FOR UPDATE and other lock types.

Chain ActiveRecord::Base#find to ActiveRecord::QueryMethods#lock to obtain an exclusive lock on the selected rows:

# select * from accounts where id=1 for update
Account.lock.find(1)

Call lock('some locking clause') to use a database-specific locking clause of your own such as ‘LOCK IN SHARE MODE’ or ‘FOR UPDATE NOWAIT’. Example:

Account.transaction do
  # select * from accounts where name = 'shugo' limit 1 for update nowait
  shugo = Account.lock("FOR UPDATE NOWAIT").find_by(name: "shugo")
  yuko = Account.lock("FOR UPDATE NOWAIT").find_by(name: "yuko")
  shugo.balance -= 100
  shugo.save!
  yuko.balance += 100
  yuko.save!
end

You can also use ActiveRecord::Base#lock! method to lock one record by id. This may be better if you don’t need to lock every row. Example:

Account.transaction do
  # select * from accounts where ...
  accounts = Account.where(...)
  account1 = accounts.detect { |account| ... }
  account2 = accounts.detect { |account| ... }
  # select * from accounts where id=? for update
  account1.lock!
  account2.lock!
  account1.balance -= 100
  account1.save!
  account2.balance += 100
  account2.save!
end

You can start a transaction and acquire the lock in one go by calling with_lock with a block. The block is called from within a transaction, the object is already locked. Example:

account = Account.first
account.with_lock do
  # This block is called within a transaction,
  # account is already locked.
  account.balance -= 100
  account.save!
end

Database-specific information on row locking:

MySQL

dev.mysql.com/doc/refman/en/innodb-locking-reads.html

PostgreSQL

www.postgresql.org/docs/current/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE