The Case for Advisory Locks: Solving Distributed Concurrency Without the Overhead of Redis
Explore how to implement lightweight, application-level distributed mutexes directly within your Postgres transaction lifecycle without adding external infrastructure.
We’ve developed a reflex for reaching for new tools the moment a problem feels "distributed." It usually starts with a simple race condition—two workers trying to process the same payout or a duplicate webhook hitting your API—and ends with someone saying, "We need a distributed lock; let’s spin up a Redis instance."
Don't get me wrong, Redis is a phenomenal piece of engineering. But adding a new piece of infrastructure to your stack isn't free. It’s another point of failure, another set of credentials to manage, another service to monitor, and another bill from your cloud provider. Most of the time, the database you’re already using—Postgres—has exactly what you need built right into the engine.
Postgres advisory locks are one of those "hidden in plain sight" features. They allow you to create application-level distributed mutexes without locking actual table rows or cluttering your schema with "is_locked" columns.
The Problem with Row-Level Locking
When most developers think of locking in Postgres, they think of SELECT ... FOR UPDATE. This is great for protecting a specific row while you mutate it. However, it’s not a great fit for distributed coordination for a few reasons:
1. You need a row to exist: If you're trying to lock a "concept" (like "the daily report generation") that doesn't correspond to a single row, you end up creating "dummy" rows just to lock them.
2. Side-effects: Row-level locks can trigger triggers, update updated_at timestamps, and increase bloat if not handled carefully.
3. Deadlocks: Extensive use of row locks across different tables is a recipe for the dreaded "Deadlock Detected" error.
Advisory locks are different. They are essentially a set of 64-bit integers that you can tell Postgres to "hold." Postgres doesn't care what the integer represents; it just manages the queue of who is holding it and who is waiting for it.
The Two Flavors: Session vs. Transaction
This is where most people get tripped up. Postgres gives you two ways to hold an advisory lock, and the choice drastically changes how your application behaves.
1. Session-Level Locks
These are tied to the database connection (the session). If your application acquires a session-level lock, it will hold that lock until it explicitly releases it or until the connection is closed.
-- Acquire a session lock
SELECT pg_advisory_lock(12345);
-- Do some work...
-- Release it
SELECT pg_advisory_unlock(12345);The Danger: If your application code crashes after acquiring the lock but before calling unlock, the lock stays active until the connection is killed (e.g., by a timeout or a pooler). This can lead to "ghost locks" that block your entire system.
2. Transaction-Level Locks
These are much safer for most application logic. They are automatically released when the current transaction ends—whether it commits or rolls back.
BEGIN;
-- This lock will be released automatically when the transaction ends
SELECT pg_advisory_xact_lock(12345);
-- Critical section here
COMMIT;I almost always recommend transaction-level locks (pg_advisory_xact_lock) because they are self-cleaning. If your app server explodes mid-transaction, Postgres detects the disconnect, rolls back the transaction, and frees the lock.
From Strings to Integers
Postgres advisory locks require a 64-bit integer (bigint). But in the real world, we usually want to lock things based on strings—user IDs, slug names, or job types.
The easiest way to bridge this gap is to use the hashtext function or an MD5 hash to convert your string into a stable integer.
-- Convert a string to a bigint for locking
SELECT pg_advisory_xact_lock(hashtext('daily_report_generation'));Keep in mind that hashtext returns a 32-bit integer. If you have a massive scale and are worried about collisions, you can use two 32-bit integers (Postgres supports a two-argument version of the lock function) or a custom 64-bit hashing function.
Real-World Implementation: The "Skip if Busy" Pattern
One of the most powerful ways to use advisory locks is the "non-blocking" attempt. Imagine a cron job that runs every minute to process a queue. If the previous run is still going, you don't want a second one to start.
Using pg_try_advisory_xact_lock, the function returns true if it got the lock and false immediately if someone else has it.
async function processQueue() {
const lockKey = 987654321; // Our unique ID for this job type
const client = await pool.connect();
try {
await client.query('BEGIN');
// Try to get the lock. If it fails, return immediately.
const { rows } = await client.query(
'SELECT pg_try_advisory_xact_lock($1) as "acquired"',
[lockKey]
);
if (!rows[0].acquired) {
console.log('Another worker is already processing the queue. Skipping.');
await client.query('ROLLBACK');
return;
}
// --- Critical Work Starts Here ---
const jobs = await client.query('SELECT * FROM jobs WHERE status = "pending" FOR UPDATE SKIP LOCKED');
// Process jobs...
// --- Critical Work Ends Here ---
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}This pattern is incredibly robust. It handles concurrency at the database level, ensuring that even if you have 50 app instances running, only one can enter that critical block.
Why Not Redis?
If you already have Redis for caching, why skip it for locking?
1. Atomic Guarantees: When you use Postgres for locking, your lock state and your data state are inside the same system. You can guarantee that a lock is only released if the transaction successfully commits. With Redis, you have to handle the edge case where the database write fails but the Redis lock was already released (or vice-versa).
2. The "Redlock" Complexity: Distributed locking is famously hard. The Redlock algorithm is the standard way to do this in Redis, but it requires at least three independent Redis nodes to be truly safe against network partitions. Most people just use a single Redis instance, which makes their "distributed lock" a single point of failure.
3. Infrastructure Overhead: Every piece of infra you *don't* add is a win for the long-term maintainability of your project. If Postgres is already your source of truth, let it be your coordinator too.
The Gotchas: When to Be Careful
While I love advisory locks, they aren't a silver bullet. You need to be aware of how they interact with your connection pool.
Connection Exhaustion
Advisory locks (especially session-level ones) require an active database connection. If you have a lock that lasts for 10 minutes, you are holding a database connection open for 10 minutes. If you do this 50 times simultaneously and your pool size is 50, your entire application will hang because there are no connections left to perform simple queries.
If you have long-running tasks (like uploading a massive file to S3), don't hold the lock during the I/O wait. Instead:
1. Lock.
2. Mark a "status" column in the DB as processing.
3. Unlock/Commit.
4. Do the slow I/O.
5. Update status to done.
The 64-bit Namespace
Since the namespace for these locks is just a set of integers, there's a tiny risk of collision if two different parts of your app happen to use the same ID.
I find it helpful to define a "Lock Registry" in your codebase:
// locks.ts
export const LOCKS = {
STRIPE_WEBHOOK_SYNC: 1001,
INVENTORY_RECONCILIATION: 1002,
PDF_GENERATION: 1003,
};
// Use it
const lockId = LOCKS.STRIPE_WEBHOOK_SYNC + userId;A Practical Example: Preventing Duplicate Credits
Let’s look at a common scenario: adding credits to a user's account from an idempotent webhook. Even if the webhook hits us three times at once, we only want to process it once.
import hashlib
def get_lock_id(webhook_id: str) -> int:
# Hash the string to a 64-bit signed integer for Postgres
return int(hashlib.sha256(webhook_id.encode()).hexdigest()[:15], 16)
def process_payment(webhook_id, user_id, amount):
lock_id = get_lock_id(webhook_id)
with connection.cursor() as cursor:
cursor.execute("BEGIN;")
# Try to acquire the lock, don't wait if busy
cursor.execute("SELECT pg_try_advisory_xact_lock(%s);", (lock_id,))
acquired = cursor.fetchone()[0]
if not acquired:
print(f"Webhook {webhook_id} is already being processed. Exiting.")
cursor.execute("ROLLBACK;")
return
# Check if we've already processed this webhook in the DB
cursor.execute("SELECT 1 FROM processed_webhooks WHERE id = %s", (webhook_id,))
if cursor.fetchone():
cursor.execute("ROLLBACK;")
return
# Perform the actual business logic
cursor.execute("UPDATE users SET balance = balance + %s WHERE id = %s", (amount, user_id))
cursor.execute("INSERT INTO processed_webhooks (id) VALUES (%s)", (webhook_id,))
cursor.execute("COMMIT;")In this example, the advisory lock acts as a guard. Even if three requests arrive at the exact same millisecond, the Postgres lock manager ensures only one worker proceeds. The other two will see acquired = false and exit gracefully without even needing to check the processed_webhooks table.
Summary: When to Use What
I’ve found that a good rule of thumb is:
* Use Row Locks (`FOR UPDATE`) when you are modifying a specific row and want to prevent other transactions from modifying *that specific data*.
* Use Advisory Locks when you need to coordinate an action, a process, or a logic flow that doesn't map perfectly to a single row.
* Use Redis only when you need thousands of locks per second, or when your lock needs to persist across a database migration or restart where the DB connections might be dropped.
Postgres is a workhorse. It’s often much more capable than we give it credit for. Before you spin up that next microservice or caching layer, take a look at the tools already sitting in your public schema. Advisory locks might just save you a weekend of debugging distributed state.
