Akin Gundogdu

Postgres Advisory Locks in Rails

Distributed locking in Rails without adding Redis. How I replaced a SETNX coordination layer with Postgres advisory locks, dropped a dependency, and avoided the usual deadlock traps.

A Wednesday afternoon at the creator-economy platform I worked at. I was three days into cleaning up the orchestration layer behind our branded mobile apps pipeline. The pipeline is Rails plus Python plus Fastlane plus GitHub Actions, and it had been running in production for a while. It worked. Honestly, it worked fine. But it had a Redis cluster sitting next to it whose only job was a SETNX lock so two Sidekiq workers wouldn’t try to submit the same branded app to Apple at the same time.

That Redis cluster cost real money. It had its own alerts, its own runbook, its own page-at-3am potential. And the only thing it was guarding was a coordination decision the database already knew the answer to.

So I pulled it out. Replaced the whole thing with Postgres advisory locks. The dependency is gone. The pipeline runs cleaner. Nothing pages anymore.

This is the writeup I wish I’d had before that week.

Why advisory locks

Postgres advisory locks are application-level locks the database keeps for you. They don’t lock rows. They don’t lock tables. They lock a number you choose. Two workers ask for the same lock key, one gets it, the other doesn’t. That’s the whole feature.

The thing people miss is that you already have a Postgres connection. You already trust your database for transactional state. Adding Redis just to do SETNX with a TTL is adding a second source of truth for “who’s allowed to run this right now”. Two sources of truth eventually disagree. Mine did.

There are two flavors you actually care about.

  • Session-level locks live until you explicitly release them or the connection dies. Good for “this Sidekiq job is the only one allowed to process app X right now”.
  • Transaction-level locks live until the surrounding transaction commits or rolls back. Good for “this code path inside a transaction is the only one allowed to touch this aggregate”.

Use session for jobs. Use transaction for request-scoped critical sections. Almost everything I’ve shipped uses one of those two.

The with_advisory_lock gem

For the common case, with_advisory_lock does the right thing.

# Gemfile
gem "with_advisory_lock", "~> 5.1"
class BrandedAppSubmissionJob
  include Sidekiq::Job
  sidekiq_options queue: :bma_submissions, retry: 5

  LOCK_TIMEOUT_SECONDS = 30

  def perform(branded_app_id, build_id)
    lock_key = "bma_submission:#{branded_app_id}"

    ApplicationRecord.with_advisory_lock(lock_key, timeout_seconds: LOCK_TIMEOUT_SECONDS) do
      build = BrandedAppBuild.find(build_id)
      return if build.submitted? || build.failed?

      AppleSubmissionService.new(build).call
    end
  rescue WithAdvisoryLock::FailedToAcquireLock
    # Another worker is already submitting this app. Re-enqueue with backoff.
    self.class.perform_in(60.seconds, branded_app_id, build_id)
  end
end

A few details that matter in production. The timeout_seconds: 30 is non-negotiable, the default block-forever behavior will eat your Sidekiq worker. The return if build.submitted? re-check inside the lock is the read-after-acquire pattern, because by the time you hold the lock the world has moved on. And the rescue branch matters more than people think. If you don’t handle “couldn’t acquire”, the job either dies on the floor or busy-loops, and both are worse than a polite retry.

The gem hashes your string key into a 64-bit integer. If you bypass the gem and use raw SQL, you do that hashing yourself.

Raw SQL when you need it

Sometimes you want the lock without a block, or you want it scoped to a transaction. Raw SQL is fine. It’s three functions and they’re all named obviously.

class CreatorSubscriptionRenewal
  RENEWAL_LOCK_NAMESPACE = 8821

  def self.process(notification)
    ActiveRecord::Base.transaction do
      lock_acquired = ActiveRecord::Base.connection.select_value(<<~SQL.squish)
        SELECT pg_try_advisory_xact_lock(
          #{RENEWAL_LOCK_NAMESPACE},
          #{hash_int(notification.apple_original_transaction_id)}
        )
      SQL

      raise BusyError, "renewal in flight" unless lock_acquired

      CreatorSubscription.upsert_from_apple_notification!(notification)
    end
  end

  def self.hash_int(string)
    # Postgres advisory locks take signed int4 in the two-arg form.
    (Zlib.crc32(string) & 0x7fffffff)
  end
end

Two things here. pg_try_advisory_xact_lock is the non-blocking, transaction-scoped variant, which means I never need to remember to unlock. When the transaction commits or rolls back, the lock is gone. And I’m using the two-argument form so I can carve out a namespace integer per use case, which keeps unrelated lock keys from colliding by accident.

This is exactly the shape I wish we’d had a year earlier on that same platform, when Apple’s SubscriptionRenewal server-to-server notifications hit our endpoint twice for the same transaction. We had no idempotency check on the renewal handler. Apple retried after a slow response, every retry created a new creator_subscriptions row, and a chunk of creators ended up double-billed. The fix at the time was a unique constraint plus an async Sidekiq pipeline so we could return 200 OK fast. An advisory lock on the original transaction id would have been the cleaner inner guard. The unique constraint catches the duplicate after it happens. The advisory lock prevents the duplicate from being computed in the first place.

Deadlocks are still a thing

Advisory locks can absolutely deadlock. Two workers, two keys, opposite acquisition order, classic.

The fix is the boring one. Sort your keys before you acquire.

def with_sorted_locks(keys, &block)
  sorted = keys.uniq.sort
  acquired = []

  sorted.each do |key|
    got = ApplicationRecord.connection.select_value(
      "SELECT pg_try_advisory_lock(#{Zlib.crc32(key) & 0x7fffffff})"
    )
    raise WithAdvisoryLock::FailedToAcquireLock unless got
    acquired << key
  end

  yield
ensure
  acquired.reverse_each do |key|
    ApplicationRecord.connection.execute(
      "SELECT pg_advisory_unlock(#{Zlib.crc32(key) & 0x7fffffff})"
    )
  end
end

If every code path acquires its locks in sorted order, you can’t deadlock. It’s the same trick you use for row locks. It’s the same trick the database uses internally. Don’t try to be clever about it.

When advisory locks aren’t the answer

They’re not magic.

You still need idempotency keys at the data layer. Advisory locks coordinate workers. They don’t coordinate retries that have already happened on the upstream side. Use the right tool. Advisory locks are for application-level critical sections inside Rails. They are not for “make my distributed system correct”.

And don’t put advisory locks inside long-running transactions. The transaction holds row locks. The advisory lock blocks other workers. You’re now serializing two unrelated concerns and you’ll find out about it during a peak hour.

Takeaways

  • If you already have Postgres, you probably don’t need Redis just for SETNX.
  • Use session-level locks for jobs, transaction-level for request-scoped critical sections.
  • Always pass a timeout. Always handle the “couldn’t acquire” branch.
  • Namespace your lock keys with the two-argument form.
  • Sort keys before acquiring multiple locks. That’s how you don’t deadlock.
  • Advisory locks are not a substitute for idempotency at the data layer.

Thanks for reading. If you’ve got thoughts, send them my way.

© 2026 Akin Gundogdu. All Rights Reserved.