Lightweight Distributed Locks with PostgreSQL Advisory Locks & MySQL GET_LOCK

A scheduled cron job that ran smoothly on a single server suddenly starts firing three times every night the moment you scale your deployment out to three instances for high availability. Preventing duplicate execution across multiple application servers requires a distributed lock. While many teams immediately reach for Redis, if your service already runs on PostgreSQL or MySQL and your lock acquisition rate is modest, your existing relational database provides built-in, in-memory application locks—without adding a single new piece of infrastructure. This article explains how to implement distributed locks using PostgreSQL Advisory Locks and MySQL Named Locks (`GET_LOCK`), along with their connection-pooling nuances and failure boundaries.

1. Intuitive Analogy

Think of the single physical restroom key hanging behind the counter of a small cafe. Whoever grabs the key from the counter first enters the room, and when they finish, they hang it back on the hook. Because every application server in your cluster already talks to the same primary database (the counter), the database can manage named or numbered "keys" in memory that are completely independent of any specific table row.

2. Concrete Scenario: Multi-Instance Cron Scheduler Deduplication

Suppose three Kubernetes pods run the same NestJS or Go backend binary containing a midnight `@Cron('0 0 * * *')` settlement task that aggregates daily invoices and emails a summary report. Without coordination, all three pods wake up at `00:00:00` and send three duplicate settlement emails. If each pod attempts a non-blocking database advisory lock before running the job, only the winning pod executes the task while the other two immediately exit.

3. PostgreSQL Advisory Locks (`pg_try_advisory_xact_lock`)

PostgreSQL provides fast, memory-backed Advisory Locks identified by a single 64-bit integer (`bigint`) or a pair of 32-bit integers (`int, int`). They come in two lifecycles:

• Session-Level Advisory Locks (pg_advisory_lock, pg_try_advisory_lock): Held until explicitly unlocked via pg_advisory_unlock() or until the underlying TCP database connection closes. Rolling back a transaction does not release a session-level advisory lock!
• Transaction-Level Advisory Locks (pg_advisory_xact_lock, pg_try_advisory_xact_lock): Automatically released the instant the enclosing transaction commits or rolls back.

Functions with `try` in their name are non-blocking: instead of waiting in a queue, they immediately return `true` if the lock was acquired or `false` if another session holds it. In modern backends using connection pools (or PgBouncer), transaction-level locks (`pg_try_advisory_xact_lock`) are strongly preferred because they can never leak into a pooled connection if an exception occurs:

// Go (database/sql)
func RunExclusive(ctx context.Context, db *sql.DB, key int64, job func(*sql.Tx) error) (bool, error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return false, err
	}
	defer tx.Rollback() // No-op if tx.Commit() succeeds below

	var locked bool
	err = tx.QueryRowContext(ctx, "SELECT pg_try_advisory_xact_lock($1)", key).Scan(&locked)
	if err != nil || !locked {
		return false, err // locked == false means another instance is already running the job
	}
	if err := job(tx); err != nil {
		return true, err
	}
	return true, tx.Commit()
}

// NestJS + Prisma Interactive Transaction
await this.prisma.$transaction(async (tx) => {
  const [row] = await tx.$queryRaw<{ locked: boolean }[]>`
    SELECT pg_try_advisory_xact_lock(${SETTLEMENT_LOCK_KEY}::bigint) AS locked`;
  if (!row?.locked) return; // Another instance holds the lock
  await this.settle(tx);
}, { timeout: 60_000 });

Note that Prisma interactive transactions default to a 5,000 ms timeout, so pass an explicit `timeout` option when wrapping longer batch jobs.

4. MySQL Named Locks: `GET_LOCK()` and `RELEASE_LOCK()`

MySQL provides string-named session locks via GET_LOCK(str, timeout_seconds) (where `str` is up to 64 characters and a timeout of `0` makes the call non-blocking):

SELECT GET_LOCK('daily-settlement', 0);  -- Returns 1 (acquired), 0 (timed out / busy), or NULL (error)
-- Execute exclusive job on the SAME database connection...
SELECT RELEASE_LOCK('daily-settlement'); -- Returns 1 (released), 0 (not held by this thread), or NULL

Critical MySQL connection pool warning: Unlike PostgreSQL's `pg_try_advisory_xact_lock`, MySQL's `GET_LOCK` is strictly session-scoped—committing or rolling back a transaction does not release it. When using an ORM connection pool, two separate `db.query()` calls can easily be dispatched over two different pooled connections! You must pin a single dedicated connection (or wrap both queries inside an explicit transaction block that guarantees single-connection affinity) and ensure `RELEASE_LOCK` runs inside a `finally` block.

5. Handling Long-Running Jobs Without Holding Transactions Open

Transaction-level advisory locks require keeping a database transaction open for the duration of the job. While a 5-second task is completely fine, holding a PostgreSQL transaction open for 30 minutes prevents `VACUUM` from reclaiming dead tuples across the database. For long-running batch jobs, consider two alternatives:

• Dedicated Connection + Session-Level Lock: Check out a dedicated connection from the pool, acquire a session-level lock, and if `RELEASE_LOCK` / `pg_advisory_unlock` fails on cleanup, destroy/close that physical connection rather than returning it to the pool.
• Idempotent Job Execution Ledger Table (`UNIQUE` Constraint): For daily or hourly scheduled jobs, inserting a row into a `job_runs` table is often simpler, leaves an audit trail, and requires no long-lived connection lock at all:

CREATE TABLE job_runs (
  job_name  VARCHAR(100) NOT NULL,
  run_date  DATE         NOT NULL,
  PRIMARY KEY (job_name, run_date)
);
-- Only the first server to execute INSERT succeeds;
-- competing servers catch the duplicate key error and skip execution.
INSERT INTO job_runs (job_name, run_date) VALUES ('daily-settlement', CURRENT_DATE);

6. Side-by-Side Comparison

Attribute	PostgreSQL Advisory Lock	MySQL GET_LOCK	Redis Distributed Lock
Lock Key Format	64-bit `bigint` (or two 32-bit `int`s)	String (up to 64 chars)	Arbitrary string key
Automatic Release	Transaction end (`xact`) or disconnect	TCP connection disconnect	Explicit TTL expiration (`PX`)
Extra Infrastructure	None (uses existing Postgres)	None (uses existing MySQL)	Requires Redis instance
Cost While Held	Pins 1 DB connection while held	Pins 1 DB connection while held	Zero DB connections pinned

7. Common Mistakes to Avoid

• Returning a session-locked connection to a connection pool: If an exception skips `pg_advisory_unlock` or `RELEASE_LOCK` and the connection returns to the pool, that lock stays permanently held until the pooled connection is recycled hours later!
• Using session-level advisory locks behind PgBouncer in Transaction Pooling mode: PgBouncer reassigns server connections after every transaction, breaking session-level locks. Always use `pg_try_advisory_xact_lock` with PgBouncer.
• Assuming a lock alone guarantees 100% exactly-once data integrity: If a database failover occurs mid-job, the lock on the old primary vanishes. Always back critical financial writes with database unique constraints or idempotency keys.

When lock throughput scales to thousands of acquisitions per second—where pinning database connections becomes a bottleneck—compare this approach with Redis Distributed Locks. And if you are simply protecting a specific database row during an update, use Row-Level Pessimistic or Optimistic Locks instead.

8. Summary

References

• PostgreSQL Documentation - Advisory Locks
• PostgreSQL Documentation - Advisory Lock Functions
• MySQL 8.4 Reference Manual - Locking Functions (`GET_LOCK`)
• PgBouncer Documentation - Feature Matrix for Pooling Modes