[Ruby on Rails (RoR)] Pessimistic Locking in Rails

Posted on 2020-06-13 Edited on 2023-09-02 In Programming Language , Ruby , Rails / Ruby on Rails (RoR) Views: Word count in article: 3.2k Reading time ≈ 3 mins.

Optimistic 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:

1 2	# 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:

```ruby
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

Combie with_lock and lock!:

def like(id)
  message = Message.find(id)
  message.with_lock do
    account.lock!
    message.like_count += 1
    message.save!
  end
end

Since we are already in a database transaction (the first lock), we cannot use another with_lock block because it itself is a transaction. what we can do inside of this with_lock block is call .lock! .

Database-specific information on row locking

MySQL

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads - https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

PostgreSQL

PostgreSQL: Documentation: 13: SELECT - https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE

Instance Public methods

lock!(lock = true)

Obtain a row lock on this record. Reloads the record to obtain the requested lock. Pass an SQL locking clause to append the end of the SELECT statement or pass true for “FOR UPDATE” (the default, an exclusive row lock). Returns the locked record.