Advisory Locks in PostgreSQL

PostgreSQL can be used as a locking service. Imagine this use case: A db migrator is embedded as a library in multiple copies of a program, that, when deployed, try to migrate the database. How to we ensure only one copy of the migrator runs, while the other attempted runs are denied? Using an advisory lock.

Here's what the pg_locks view looks like:

\d pg_locks
                    View "pg_catalog.pg_locks"
┌────────────────────┬──────────┬───────────┬──────────┬─────────┐
│       Column       │   Type   │ Collation │ Nullable │ Default │
├────────────────────┼──────────┼───────────┼──────────┼─────────┤
│ locktype           │ text     │           │          │         │
│ database           │ oid      │           │          │         │
│ relation           │ oid      │           │          │         │
│ page               │ integer  │           │          │         │
│ tuple              │ smallint │           │          │         │
│ virtualxid         │ text     │           │          │         │
│ transactionid      │ xid      │           │          │         │
│ classid            │ oid      │           │          │         │
│ objid              │ oid      │           │          │         │
│ objsubid           │ smallint │           │          │         │
│ virtualtransaction │ text     │           │          │         │
│ pid                │ integer  │           │          │         │
│ mode               │ text     │           │          │         │
│ granted            │ boolean  │           │          │         │
│ fastpath           │ boolean  │           │          │         │
└────────────────────┴──────────┴───────────┴──────────┴─────────┘

Here's what it looks like when we fire up a session and grab an advisory lock with classid 666 and objid 999. Note that 666 and 999 have been chosen by us. They have no special meaning beyond the fact that we need to provide something to PostgreSQL to identify our advisory lock.

select pg_try_advisory_lock(666, 999); rollback;
┌──────────────────────┐
│ pg_try_advisory_lock │
├──────────────────────┤
│ t                    │
└──────────────────────┘
(1 row)

Now let's go look at our advisory lock:

\x
select * from pg_locks where locktype = 'advisory'; rollback;
┌─[ RECORD 1 ]───────┬───────────────┐
│ locktype           │ advisory      │
│ database           │ 12709         │
│ relation           │ [NULL]        │
│ page               │ [NULL]        │
│ tuple              │ [NULL]        │
│ virtualxid         │ [NULL]        │
│ transactionid      │ [NULL]        │
│ classid            │ 666           │
│ objid              │ 999           │
│ objsubid           │ 2             │
│ virtualtransaction │ 3/196         │
│ pid                │ 6755          │
│ mode               │ ExclusiveLock │
│ granted            │ t             │
│ fastpath           │ f             │
└────────────────────┴───────────────┘

Now let's fire up another PostgreSQL session (to immitate our mythical db migrator trying to run again).

select pg_try_advisory_lock(666, 999);
┌──────────────────────┐
│ pg_try_advisory_lock │
├──────────────────────┤
│ f                    │
└──────────────────────┘
(1 row)

So all we need to do is code our db migrator to try to acquire an advisory lock using the same classid/objid pair, and one copy of the migrator will succeed (get t from the query) and all other copies of the migrator will fail (get f from the query).

The best part is, when the db migrator logs out of PostgreSQL, the advisory lock will automatically be released!