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 think that solution would keep the DB locked completely while the block is running, because of the open transaction, and how SQLite works. So, this works for locking but it'd lock everything else, I think. You wouldn't be able to have other locks being used in the meantime, only one, and you wouldn't be able to write other tables. I'm not a SQLite expert by any means (the DBMS I have the most experience with is MySQL) but I think that's how it works.

I confirm that the entire database is locked.