What is Advisory Lock in PostgreSQL: Explanation and Usage

In PostgreSQL, an advisory lock is a manual locking mechanism that applications use to coordinate access to resources without locking database rows or tables. It allows developers to create custom locks identified by keys, helping prevent conflicts in concurrent operations.

How It Works

Advisory locks in PostgreSQL work like a handshake between different parts of your application to say, "I'm using this resource, please wait." Unlike regular locks that automatically protect rows or tables, advisory locks are controlled by the application using unique keys (numbers). Think of it as reserving a seat in a theater by holding a ticket number.

When one process acquires an advisory lock with a specific key, other processes trying to get the same lock will wait until it is released. This helps avoid conflicts when multiple processes want to do something that should not happen at the same time, like updating a shared file or running a job.

Example

This example shows how to acquire and release an advisory lock using PostgreSQL functions. The lock uses a key (12345) to identify the resource.

sql

BEGIN;
-- Try to acquire the advisory lock with key 12345
SELECT pg_advisory_lock(12345);
-- Critical section: do work that needs exclusive access
-- ... your code here ...
-- Release the advisory lock
SELECT pg_advisory_unlock(12345);
COMMIT;

Output

 pg_advisory_lock 
------------------
 t
(1 row)

 pg_advisory_unlock 
--------------------
 t
(1 row)

When to Use

Use advisory locks when you want to control access to resources that are not directly tied to database rows or tables. For example, if multiple application instances need to run a scheduled task but only one should run it at a time, an advisory lock can coordinate this.

They are also useful for managing access to external resources like files, APIs, or caches where PostgreSQL's built-in row or table locks do not apply.

Key Points

• Advisory locks are application-controlled and identified by keys.
• They do not lock database rows or tables automatically.
• Useful for coordinating access to shared resources outside normal database locking.
• Locks are released automatically at the end of a session or transaction if not released manually.

Key Takeaways

Advisory locks let applications manually control access to shared resources using unique keys.

They help prevent conflicts without locking database rows or tables.

Use them to coordinate tasks or access to external resources safely.

Locks must be explicitly acquired and released by the application.

Unreleased locks are cleared when the session or transaction ends.