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?

Interesting. Now I'm checking also this different approach:

# == 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)
    AdvisoryLock.transaction do
      lock = AdvisoryLock.find_or_create_by!(key: key)
      AdvisoryLock.where(id: lock.id).lock(true).first
      yield
      lock.destroy!
    end
  end
end

This solution has the advantage of working even if the record is not deleted, avoiding polling, and utilizing the timeout configured in the database.yml file if SQLite fails to acquire the lock.

Here a little test:

require "test_helper"

class AdvisoryLockTest < ActiveSupport::TestCase
  def test_lock_record_creation_and_destruction
    key = "test_lock"
    block_executed = false

    AdvisoryLock.with_advisory_lock(key) do
      block_executed = true

      assert AdvisoryLock.exists?(key: key), "Lock should exist while the block executes"
    end

    assert block_executed, "The block should be executed"
    assert_not AdvisoryLock.exists?(key: key), "Lock should be released after the block"
  end

  def test_concurrency
    key = "test_concurrent_lock"
    first_thread_started = false
    first_thread_completed = false

    first_thread = Thread.new do
      AdvisoryLock.with_advisory_lock(key) do
        first_thread_started = true
        sleep 0.3
      end
      first_thread_completed = true
    end

    sleep 0.1 until first_thread_started # Wait for the first thread to acquire the lock

    AdvisoryLock.with_advisory_lock(key) do
      assert first_thread_completed, "The second lock should wait for the first to complete"
    end

    first_thread.join
  end
end

🤔 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.