Advisory locking with ActiveRecord
Not too long ago we had a problem with doing (almost) guaranteed inserts for columns with unique constraints on them (see this post).
Today I was facing a similar problem, from a bit different angle – we needed to track a sequential number (think of it as a auto incrementing value, like primary keys), which would only be incremented on certain conditions and it absolutely cannot fail due to race conditions.
The Setup
So we have a model, which has :number column, which should be sequential and also unique.
When the object transitions to a certain state, the :number for this row should be incremented by one and saved. Some objects won’t reach this state at all, some will do it many times.
Also, the :number column has an unique index defined on it, ensuring that there cannot be several objects with the same value in this column.
This incrementation needs to be reliable, it has to be successful incrementing the number and saving it without any uniqueness conflicts. The most common case for this operation to fail would be a race condition — one user does a "SELECT MAX(number) + 1 from objects" and stores the return value.
Now another user does exactly the same thing at the same time. They both have the value stored and they are both ready to update their respective :number columns with the value. When the first user calls the update query, it finishes just fine. But the second user is dealing with stale data now and his/her update query will fail due to the unique constraint defined on the :number field.
The Idea
We tried to approach this problem from several angles, considering several options, starting from Rails’ validate_uniqueness_of, using transactions and retrying for a set amount of times (see the post I linked to before), database triggers etc.
Finally we ended up with the idea of using some kind of locking. We can’t use table level locks as this would block all operations on the table during the lock period. Neither can we use SELECT FOR UPDATE row locking, as we are not actually selecting any rows for updating.
The third option was PostgreSQL advisory locking (in MySQL this is called “named locks”).
The idea was to wrap a piece of code (in this case incrementing the number and saving it) inside an advisory lock, which would effectively ensure that only one increment and save operation would be run at any given time, guaranteeing that there won’t be race conditions issues.
While locking in itself is a nice solution for concurrency issues, you shouldn’t go overboard with it. Excessive locking can lead to unnecessary blocking and even deadlocks. Remember: use locking as much as you need and as little as possible.
The Solution
The most basic functionality we needed was just to wrap some operations inside – we obtain a lock, identified by a pair of keys (consisting of the current entity id and operation type), do the operation and release the lock. Also note that the lock must be released upon errors raised by the wrapped code.
First step was to create a simple ActiveRecord extension, providing obtain_advisory_lock(key, type, &block) method.
module ActiveRecord
module AdvisoryLock
def obtain_advisory_lock(*params, &block)
self.class.obtain_advisory_lock(*params, &block)
end
def self.obtain_advisory_lock(*params, &block)
key, type = params.map { |param| param.to_i }
raise ArgumentError, "Method expects a block" unless block_given?
obtain_lock(key, type)
begin
yield block
ensure
release_lock(key, type)
end
end
protected
def obtain_lock(key1, key2)
if key2.zero?
connection.execute("SELECT pg_advisory_lock(#{key1})")
else
connection.execute("SELECT pg_advisory_lock(#{key1}, #{key2})")
end
end
def release_lock(key1, key2)
if key2.zero?
connection.execute("SELECT pg_advisory_unlock(#{key1})")
else
connection.execute("SELECT pg_advisory_unlock(#{key1}, #{key2})")
end
end
end
end
ActiveRecord::Base.__send__(:include, ActiveRecord::AdvisoryLock)
# code in some initializer
require "ext/active_record/advisory_lock"
Note that this code supports two separate ways of obtaining and releasing a lock – one parameter, a bigint or two parameters, both integers.
Now it really simple to implement the method for incrementing the :number in a safe, reliable way.
# return if we already have a number set for this record
# in case this is not the first time the conditions are met and this method is fired
return unless self.number.nil?
# LOCK_SEQUENCE is just an enum for defining different locking operations
obtain_advisory_lock(entity.id, LOCK_SEQUENCE) do
update_attribute(:number, self.class.calculate(:max, :number).to_i + 1)
end
end
The resulting SQL output running this method looks like this:
SQL (0.5ms) SELECT MAX("number") from "objects"
Object Update (0.4ms) UPDATE "objects" SET "number" = 2, WHERE "id" = 10
SQL (0.4ms) SELECT pg_advisory_unlock(1, 2)
And that’s it! We have effectively secured that every number increment operation will be done within a blocking lock, eliminating any uniqueness issues caused by possible race conditions.
Comments are closed here.