advisory_lock.md

Implementing Advisory Locks in SQLite3

In my Rails model, I initially had a function to handle advisory locks in PostgreSQL. Here's the code, which works as expected:

# Postgresql version
def with_advisory_lock
  self.class.connection.execute("SELECT pg_advisory_lock(#{self.id})")
  yield
ensure
  self.class.connection.execute("SELECT pg_advisory_unlock(#{self.id})")
end

However, I need to switch from PostgreSQL to SQLite3, which does not natively support advisory locks. To address this, I decided to implement my own solution.

I created a new model, AdvisoryLock, to manage locks:

# == Schema Information
#
# Table name: advisory_locks
#
#  id         :integer          not null, primary key
#  key        :string           not null
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
# Indexes
#
#  index_advisory_locks_on_key  (key) UNIQUE
#

class AdvisoryLock < ApplicationRecord
  def self.with_advisory_lock(key)
    lock = AdvisoryLock.create!(key: key)
    yield
  rescue ActiveRecord::RecordNotUnique
    sleep 0.1
    retry
  ensure
    lock.destroy!
  end
end

I then updated the original with_advisory_lock method to use the new model:

def with_advisory_lock
  AdvisoryLock.with_advisory_lock(self.id, &)
end

This solution appears to work, but I have some concerns:

Resource Cleanup: The ensure block destroys the lock, but this relies on the process executing correctly. What happens if the process crashes?

So my questions are:

How can this implementation be improved for better performance or reliability? Considering I cannot use external tools like PostgreSQL, Redis, or others, are there better alternatives?

I confirm that the entire database is locked.