Why Advisory locks in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database could politely ask before stepping on someone else's toes?

The Scenario

Imagine you and your friends are trying to edit the same document at the same time without any coordination. You keep overwriting each other's changes, causing confusion and lost work.

The Problem

Without a system to control who can make changes when, you risk data conflicts, errors, and wasted time trying to fix mistakes. Manually checking if someone else is working on the data is slow and unreliable.

The Solution

Advisory locks let your database sessions politely ask for permission before changing shared data. This way, only one session can hold the lock at a time, preventing conflicts and keeping data safe.

Before vs After

✗ Before

SELECT * FROM data WHERE id = 1; -- then update without lock

✓ After

SELECT pg_advisory_lock(1); -- lock before update
UPDATE data SET value = 'new' WHERE id = 1;
SELECT pg_advisory_unlock(1); -- release lock

What It Enables

It enables safe, coordinated access to shared resources in your database without complex transaction management.

Real Life Example

In an online booking system, advisory locks ensure two users can't book the same seat at the same time, avoiding double bookings.

Key Takeaways

Manual coordination causes errors and confusion.

Advisory locks let sessions take turns safely.

This keeps data consistent and operations smooth.