Alright, let’s talk about advisory locks. You’ve probably been in a situation where you need to coordinate actions across your entire application, maybe to prevent two instances of a job scheduler from running the same task or to ensure a single user can’t spam-click a “process payment” button. You could create a dedicated “lock” table and use SELECT ... FOR UPDATE on a specific row, but that feels a bit clunky, doesn’t it? It involves table I/O, it can contribute to vacuum load, and it just seems like using a sledgehammer to crack a nut.

PostgreSQL gives us a better toolbox for this: advisory locks. Think of them as a designated whiteboard in the database’s breakroom where you can post a note saying, “I’m working on this, everybody back off.” The best part? They’re fast, they’re light, and they live entirely in memory. No table gets bloated because you locked something a million times today.

The function you’ll probably fall in love with is pg_try_advisory_lock. The try is the operative word here. It’s the polite, non-blocking cousin of the more aggressive pg_advisory_lock. Instead of waiting in line for a lock to be released, it simply gives it a shot and immediately tells you whether it succeeded (true) or failed because someone else already has it (false). This is the key to building responsive applications that don’t get stuck waiting on each other.

How the Keys Work: One Big Keyring

You have two main ways to “name” your lock: with a single bigint argument or with two integer arguments. I almost always use the two-integer version. Why? Because it’s a fantastic way to namespace your locks. Think of the first integer as the “category” and the second as the “ID” within that category.

-- Lock for the 'payment-processing' system (category 1) for user ID 44558
SELECT pg_try_advisory_lock(1, 44558);

-- Lock for the 'report-generation' system (category 2) for report ID 99
SELECT pg_try_advisory_lock(2, 99);

This prevents a lock for user 44558 in the payment system from accidentally conflicting with a lock for user 44558 in the report system. If you used a single bigint, you’d have to carefully map all your different lock types into one gigantic, flat number space, which is a recipe for subtle, horrible bugs. The two-key approach is clearly the smarter choice, and I’m mildly annoyed the single-key version exists to tempt new developers into a trap.

The Code: It’s Simpler Than You Think

Here’s the classic pattern for using a non-blocking advisory lock. You check if you can get the lock, perform your work if you did, and then make absolutely sure to release it.

BEGIN;

-- Try to acquire the lock. Did we get it?
SELECT pg_try_advisory_lock(1, 44558) INTO got_lock;

IF got_lock THEN
    -- Sweet, we have the lock. Now do the thing no one else should do concurrently.
    UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 44558;
    INSERT INTO transactions (user_id, amount) VALUES (44558, -100.00);

    -- ... more sensitive work ...

    -- RELEASE THE LOCK! Seriously, don't forget this part.
    PERFORM pg_advisory_unlock(1, 44558);
ELSE
    -- We didn't get the lock. Handle it gracefully.
    RAISE NOTICE 'Could not acquire lock for user 44558. Someone else is processing their payment.';
END IF;

COMMIT;

Notice we use PERFORM for the unlock because we don’t care about the result (it returns a boolean, but we’re already done). The crucial point here: the lock is automatically released when your session ends. But relying on that is sloppy. Always, always explicitly unlock it as soon as you’re done. Your future self, debugging a session holding a lock for hours, will thank you.

Session vs. Transaction Scoping: A Critical Choice

Here’s a design choice that isn’t obvious but is incredibly important: advisory locks are session-scoped by default, not transaction-scoped. The example above shows this. The lock is taken and released inside a transaction, but it lives outside the transaction’s control.

If your transaction rolls back, the lock stays held. This is often what you want for a multi-statement workflow. But you need to be aware of it.

If you genuinely need a transaction-scoped lock, PostgreSQL has you covered with pg_try_advisory_xact_lock. The _xact_ variant will be released automatically on transaction end (commit or rollback). The choice is fundamental:

  • Use pg_try_advisory_lock (session) for a lock that needs to span multiple transactions.
  • Use pg_try_advisory_xact_lock (transaction) for a lock that should be tightly coupled to a single transaction’s life cycle.

Picking the wrong one is a common pitfall. If you use the session variant and forget to unlock, you’ve created a leak. If you use the transaction variant and need the lock to span a commit, you’re out of luck.

The Sharp Edges and Best Practices

First, the big one: Advisory locks are not persisted. If your database crashes or is restarted, poof, all advisory locks vanish into the ether. They are stored in memory. This means they are perfect for coordinating work between active sessions of your application, but they are utterly useless for creating a lock that needs to survive a restart. For that, you still need a table.

Second, always pair your lock with an unlock. Use a TRY...FINALLY block in your application code or a BEGIN...EXCEPTION block in your SQL to ensure cleanup happens even if your operation errors out.

Third, monitor them. If something feels stuck, query the pg_locks system view to see what’s being held and by whom.

SELECT locktype, objid, pid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory';

Finally, keep your lock durations short. They are designed for coordination, not for long-term leases. Get in, do your work, get out. That’s the philosophy of a good advisory lock user. It’s a brilliant feature, but it demands respect and a bit of precision. Use it wisely.