Rails’ query cache will happily cache your advisory lock

I ran into this one a while ago: an advisory lock that worked fine in tests, worked fine most of the time in production, and every now and then just didn't lock anything. The database was never the problem. Rails was answering the lock query from its query cache, without ever sending it to PostgreSQL.

Some context

PostgreSQL advisory locks are locks on an arbitrary number instead of a row or a table. They're handy when you want to make sure only one process does something at a time, like processing a specific order or running a sync for a specific account.

There are two flavors. Session-level locks stay until you release them or the connection closes. Transaction-level locks are released automatically when the transaction ends, which is usually what you want, since you can't forget to unlock them.

Rails has built-in methods for session-level locks (get_advisory_lock and release_advisory_lock), but nothing for transaction-level ones. So you end up writing the SQL yourself, something like this:

ActiveRecord::Base.transaction do
  locked = ActiveRecord::Base.connection.select_value(
    "SELECT pg_try_advisory_xact_lock(#{lock_key})"
  )
  next unless locked

  # do the thing that only one process should do
end

pg_try_advisory_xact_lock doesn't wait: it returns true if it got the lock and false if someone else holds it. The lock is released when the transaction commits.

Looks fine, right? Well, not quite.

The query cache

During a web request or a background job, Rails turns on its query cache. If you run the same SELECT twice, the second one is served from memory instead of hitting the database. The cache is keyed by the SQL string, and it's cleared whenever you write something (insert, update, delete, and so on).

That's great for SELECT * FROM users WHERE id = 1. It's not so great for SELECT pg_try_advisory_xact_lock(42), because that query isn't a read. It changes the state of the database by taking a lock, and its result depends on what other processes are doing right now. Rails has no way of knowing that. It just sees a SELECT it has already seen.

Reproducing it

Here's a small script that shows the problem. It runs two transactions inside ActiveRecord::Base.cache (which is what Rails does for you during a request), and between them, another connection grabs the same lock:

conn = ActiveRecord::Base.connection
other = PG.connect(ENV["DATABASE_URL"]) # plays the role of another worker
lock = "SELECT pg_try_advisory_xact_lock(42)"

ActiveRecord::Base.cache do
  ActiveRecord::Base.transaction do
    puts "T1 got lock: #{conn.select_value(lock)}"
  end

  other.exec("SELECT pg_advisory_lock(42)")
  puts "other worker now holds lock 42"

  ActiveRecord::Base.transaction do
    puts "T2 got lock: #{conn.select_value(lock)}"
  end
end

I also logged whether each lock query went to the database or came from the cache. Here's the output:

  DB     SELECT pg_try_advisory_xact_lock(42)
T1 got lock: true
other worker now holds lock 42
  CACHE  SELECT pg_try_advisory_xact_lock(42)
T2 got lock: true

The second transaction never asked PostgreSQL. It got the cached true from the first one, so it thinks it holds a lock that another worker actually holds. Now two processes are inside the section that was supposed to be exclusive.

It also goes wrong the other way. If you retry pg_try_advisory_xact_lock in a loop while waiting for another worker to finish, the first false gets cached, and every retry after that gets the same false, even after the lock is free:

  DB     SELECT pg_try_advisory_xact_lock(42)
attempt 1: false
  CACHE  SELECT pg_try_advisory_xact_lock(42)
attempt 2 (lock is free now): false
  CACHE  SELECT pg_try_advisory_xact_lock(42)
attempt 3 (lock is free now): false

I got the same results on every version I tried, from Rails 7.1 to 8.1.

Why it only happens sometimes

This is the part that made it hard to track down. Committing a transaction doesn't clear the query cache. Writing does. So if the first transaction inserted or updated anything, the cache gets cleared and the second lock attempt goes to the database like it should. If the first transaction only read stuff, the stale result is still there.

Add the fact that the query cache is often off in tests and in the console, and you get a bug that never shows up locally and only sometimes shows up in production.

The fix

Wrap the lock query in uncached:

ActiveRecord::Base.transaction do
  locked = ActiveRecord::Base.uncached do
    ActiveRecord::Base.connection.select_value(
      "SELECT pg_try_advisory_xact_lock(#{lock_key})"
    )
  end
  next unless locked

  # do the thing that only one process should do
end

With that, the same script gives the right answer:

  DB     SELECT pg_try_advisory_xact_lock(42)
T1 got lock: true
other worker now holds lock 42
  DB     SELECT pg_try_advisory_xact_lock(42)
T2 got lock: false

Since it's so easy to forget, I'd put it in a small helper so nobody has to write the raw SQL again:

module AdvisoryLock
  module_function

  def try_xact_lock(key, conn: ActiveRecord::Base.connection)
    raise "needs an open transaction" unless conn.transaction_open?

    conn.uncached do
      conn.select_value("SELECT pg_try_advisory_xact_lock(#{Integer(key)})")
    end
  end
end

The transaction_open? check is there for another mistake that's just as quiet: taking a transaction-level lock outside a transaction. The statement commits on its own, so the lock is released right after you take it.

It's not just advisory locks

The general problem is that the query cache assumes a SELECT only reads, and in PostgreSQL that's not always true. Anything that has side effects or returns something different every time can get caught the same way if you run it through select_value or select_all twice in one request, like nextval('some_sequence') or gen_random_uuid().

So if you're writing raw SQL that calls a function, it's worth asking yourself whether you'd be OK getting the previous result back. If not, wrap it in uncached.