Advisory Locks: MySQL GET_LOCK vs PostgreSQL pg_advisory_lock in Production

At 02:00 every night, a billing job recalculated daily usage for every account, and it was protected by GET_LOCK so two app servers could never run it at once. It worked for a year. Then an Orchestrator failover at 01:58 promoted a replica, the job started on schedule at 02:00, and it ran twice, once on each app server, because GET_LOCK had been held on the old primary and a lock is a property of a running server, not of the data. The new primary's lock namespace was empty. Both servers asked for the lock, both got it, and forty thousand accounts got double-billed usage rows that finance found three weeks later.

Advisory locks are the tool every developer reaches for when they need a mutex but do not want to operate ZooKeeper: a named, application-level lock that no row has to exist for. MySQL and PostgreSQL both have them, both are genuinely useful, and both fail in ways that only show up during failovers, connection pooler misconfigurations, and the quiet moment when a session dies and releases a lock someone assumed was held. This article is the production comparison: semantics, scoping, pooling hazards, and observability, on both engines.

What do GET_LOCK and pg_advisory_lock actually do?

Both acquire a mutual-exclusion token identified by a name or number, held by your session, completely unrelated to any table row. "Advisory" means the database does not enforce anything with it: no row is locked, no other session is blocked from touching data, and the lock only works because every participant voluntarily asks for the same name before doing the protected work. That voluntary property is the feature and the hazard in one. Any code path that forgets to take the lock simply ignores the protocol, and nothing errors.

The acquisition shapes differ. MySQL's GET_LOCK takes a string name, up to 64 characters, and a timeout in seconds, returning 1 on success, 0 on timeout, and NULL on error. PostgreSQL's pg_advisory_lock takes a bigint key, or two 32-bit integers for a namespace-plus-key convention, and blocks indefinitely, while pg_try_advisory_lock returns a boolean immediately:

-- MySQL 8.0: string-named, timeout built in
SELECT GET_LOCK('billing_daily_usage', 10);   -- 1 = acquired
SELECT RELEASE_LOCK('billing_daily_usage');

-- PostgreSQL: numeric key, try-variant returns a boolean
SELECT pg_try_advisory_lock(987654);           -- true = acquired
SELECT pg_advisory_unlock(987654);

Since MySQL 5.7, a session may hold multiple named locks at once; before that, acquiring a second lock silently released the first, a piece of legacy behavior that still explains some very old application bugs. PostgreSQL advisory locks are re-entrant: the same session may take the same key repeatedly, but it must then release it the same number of times, which is a fun surprise when a helper function takes the lock and the caller also does.

How do scoping and release semantics differ between the engines?

MySQL advisory locks are session-scoped only, while PostgreSQL offers both session-scoped and transaction-scoped variants, and that second option is the one worth migrating to. On MySQL the lock lives until RELEASE_LOCK, RELEASE_ALL_LOCKS, or the connection ends. PostgreSQL's pg_advisory_lock mirrors that session behavior, but pg_advisory_xact_lock binds the lock to the current transaction and releases it automatically at commit or rollback, with no early release possible:

-- PostgreSQL: the lock dies with the transaction, guaranteed
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('billing_daily_usage'));
-- ... do the protected work ...
COMMIT;  -- lock released here, even if the app forgets

The transaction-scoped variant eliminates the entire class of "the app threw an exception and left the lock held" bugs, which on session-scoped locks manifest as every subsequent attempt timing out until someone finds and kills the offending session. On MySQL there is no transaction-scoped advisory lock, so the discipline is manual: release in a finally block, and treat any GET_LOCK held across an unexpected code path as a bug to fix, not a state to tolerate. One namespace subtlety matters on multi-database clusters: PostgreSQL advisory locks share a single keyspace across the whole cluster, so the same key in two different databases collides, which is either a cross-database coordination feature or a bug depending on whether you knew about it. MySQL's namespace is likewise server-global, not per-schema.

What breaks under connection poolers and failovers?

Poolers break session-scoped assumptions, and failovers break the assumption that the lock exists at all. Under PgBouncer in transaction pooling mode, a session-level pg_advisory_lock is a trap: your transaction ends, the server connection goes back to the pool still holding your lock, and the next client that borrows the connection inherits a lock it never took. The rule on pooled PostgreSQL is absolute: use pg_advisory_xact_lock, or do not use advisory locks. The full catalog of similar traps is in the PgBouncer transaction-mode gotchas piece. MySQL pools misbehave differently: a connection returned to the pool with a lock still held hands that lock to the next borrower, so pool configuration that resets session state matters, and RELEASE_ALL_LOCKS in cleanup code is cheap insurance.

Failover is the deeper issue, and it is symmetric: neither engine replicates advisory lock state. MySQL's GET_LOCK namespace lives in the running server's memory; PostgreSQL advisory locks live in the primary's shared memory and are invisible to standbys, which cannot take them at all. Any design where the advisory lock is the only thing preventing duplicate work will duplicate work the night the primary changes. The billing incident's fix was not a better lock; it was making the job idempotent with a unique constraint on (account_id, usage_date) so a double run was a no-op instead of a double bill, and keeping the advisory lock purely as a courtesy that avoids the redundant work. That is the correct hierarchy everywhere: the constraint guarantees correctness, the lock guarantees efficiency.

How do you see who holds an advisory lock right now?

PostgreSQL shows advisory locks in pg_locks alongside every other lock type; MySQL exposes them through the performance schema metadata locks table, where they appear as user-level locks. When a deploy hangs or a job will not start, these are the queries:

-- PostgreSQL: who holds advisory locks, and on what
SELECT pid, classid, objid, mode, granted,
       now() - query_start AS held_for,
       left(query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE locktype = 'advisory';

-- MySQL 8.0: user-level locks via performance_schema
SELECT object_name, lock_type, lock_status,
       owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_type = 'USER LEVEL LOCK';

SELECT IS_USED_LOCK('billing_daily_usage');  -- connection id of holder

The pg_locks view is covered more broadly in the locks and deadlocks guide, and the MySQL metadata_locks table in the metadata locks diagnosis notes; the user-level locks slot into the same views as everything else, which is genuinely convenient once you know to look there. Note the asymmetry in identification: PostgreSQL gives you the pid and the query text directly, while MySQL's IS_USED_LOCK gives you a connection id you then chase through the processlist.

Where does MonPG fit when a lock is the hang?

An advisory-lock pile-up looks like a stall with no slow query: jobs queue, nothing errors, and the blocker is a lock type most dashboards never graph. On PostgreSQL, MonPG's PostgreSQL monitoring watches pg_locks and session state continuously, so an advisory lock held for six hours by a forgotten session shows up as a long-held lock with the holder's query attached, not as a mystery the night shift has to reconstruct from pg_locks by hand.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will surface: user-level locks from performance_schema with their holding connection, so the GET_LOCK that outlived its job is visible before the next scheduled run collides with it. Until then, the MySQL monitoring page tracks that work. And the lesson from 02:00 needs no monitoring at all: an advisory lock is a performance optimization, never a correctness mechanism, because the lock can vanish with a failover, a pooler, or a dropped connection, and the data has to stay correct in every one of those cases.