0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Advisory Locks for Application Coordination

Learn how PostgreSQL advisory locks give your application a lightweight, programmer-controlled mutex for coordinating jobs and protecting critical sections without locking table rows.

Advisory Locks for Application Coordination is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Are Advisory Locks?

Advisory locks are locks whose meaning is defined entirely by your application. PostgreSQL does not associate them with any row or table — your code decides what a given lock key represents.

Why Use Them?

They are perfect for coordination that does not map to a single row:

  • Ensuring only one worker runs a nightly job
  • Serializing access to an external API
  • Preventing two processes from importing the same file

Lock Keys

An advisory lock is identified by a 64-bit integer, or by two 32-bit integers. You pick the numbers; a common pattern is to hash a string into an integer key.

SELECT hashtext('nightly-report');

Session-Level Locks

pg_advisory_lock holds the lock until you release it or the session ends. It blocks if another session already holds the same key.

SELECT pg_advisory_lock(42);
-- do exclusive work
SELECT pg_advisory_unlock(42);

Try-Lock Without Blocking

Often you do not want to wait. pg_try_advisory_lock returns true if it got the lock and false immediately if someone else holds it.

SELECT pg_try_advisory_lock(42);

The Single-Worker Pattern

A cron-triggered job uses try-lock to ensure only one instance runs. If it returns false, this instance simply exits.

-- if this returns false, another worker is running
SELECT pg_try_advisory_lock(hashtext('nightly-report'));

Transaction-Level Locks

The _xact_ variants release automatically at COMMIT or ROLLBACK, so you cannot forget to unlock. Great for short critical sections.

BEGIN;
SELECT pg_advisory_xact_lock(42);
-- protected work here
COMMIT; -- lock auto-released

Inspecting Held Locks

Advisory locks show up in pg_locks with locktype 'advisory'. Use it to debug who is holding what.

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

Advisory vs Row Locks

Key differences from SELECT ... FOR UPDATE:

  • Advisory locks are not tied to any row
  • They survive across statements without holding row versions
  • Their meaning is purely a convention your app agrees on

Pitfalls to Avoid

Watch out for:

  • Forgetting to unlock session-level locks (prefer xact variants)
  • Key collisions between unrelated features — namespace your keys
  • Connection poolers reusing sessions and leaking locks

Unlocking Session Locks Cleanly

Session-level locks can be acquired multiple times and must be unlocked the same number of times. To clear everything held by the current session at once, use the unlock-all helper.

SELECT pg_advisory_unlock_all();

Quick Check

Test your advisory lock knowledge.

Recap

You learned advisory locks:

  • Application-defined locks not tied to rows or tables
  • Identified by 64-bit or paired 32-bit keys
  • Session-level vs auto-releasing transaction-level variants
  • pg_try_advisory_lock powers single-worker patterns
  • Namespace keys and prefer xact locks to avoid leaks

Frequently asked questions

Is the “Advisory Locks for Application Coordination” lesson free?

Yes — the full text of “Advisory Locks for Application Coordination” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Advisory Locks for Application Coordination”?

Learn how PostgreSQL advisory locks give your application a lightweight, programmer-controlled mutex for coordinating jobs and protecting critical sections without locking table rows. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Advisory Locks for Application Coordination” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Understanding Locks and Deadlocks
  2. Identifying and Resolving Lock Contention
  3. Row-Level Locking Strategies
  4. Advisory Locks for Application Coordination
← Back to PostgreSQL Performance & Query Optimization