Advisory locks in PostgreSQL

Introduction

Advisory locks let you control access to resources in your database without blocking normal queries. They help avoid conflicts when multiple tasks try to use the same resource.

When you want to prevent two processes from running the same job at the same time.

When you need to coordinate access to a shared resource like a file or cache outside the database.

When you want to lock something without locking database rows or tables.

When you want a lightweight way to signal that a resource is in use.

When you want to avoid deadlocks by managing locks manually.

Syntax

PostgreSQL

SELECT pg_advisory_lock(key);
SELECT pg_advisory_unlock(key);
SELECT pg_try_advisory_lock(key);

The key is usually a big integer that identifies the resource.

pg_advisory_lock waits until the lock is free, pg_try_advisory_lock returns immediately with success or failure.

Examples

This locks the resource identified by 12345. If another session holds it, this waits.

PostgreSQL

SELECT pg_advisory_lock(12345);

This tries to lock resource 12345 but returns immediately with true or false.

PostgreSQL

SELECT pg_try_advisory_lock(12345);

This releases the lock on resource 12345.

PostgreSQL

SELECT pg_advisory_unlock(12345);

Sample Program

This example tries to get an advisory lock on resource 9999. If successful, you can safely do your work. Then it releases the lock.

PostgreSQL

BEGIN;
SELECT pg_try_advisory_lock(9999) AS got_lock;
-- Do work only if got_lock is true
SELECT pg_advisory_unlock(9999);
COMMIT;

Important Notes

Advisory locks are session-based and released automatically if the session ends.

Use the same key in all sessions that need to coordinate on the same resource.

Advisory locks do not block normal database operations like row locks do.

Summary

Advisory locks let you control access to resources using simple integer keys.

You can wait for a lock or try to get it immediately.

Remember to release locks when done to avoid blocking others.