Intermediate LevelPostgreSQL
What are PostgreSQL Application Advisory Locks (pg_advisory_lock) and when to use them?
❓ Interview Prompt: Explain Advisory Locks, `pg_advisory_lock()`, `pg_try_advisory_lock()`, session vs transaction level advisory locks, and application-level synchronization.
Advisory Locks allow applications to create custom locks using arbitrary integer keys without locking table rows:
Types:
•
Session-Level ('pg_advisory_lock(key)'): Held until explicitly unlocked ('pg_advisory_unlock(key)') or connection drops.
•
Transaction-Level ('pg_advisory_xact_lock(key)'): Automatically released when current transaction commits or rolls back!
•
Use Case: Preventing concurrent cron job executions or leader election in distributed backend apps.
Code Implementation Example
sql
-- Try acquiring non-blocking application advisory lock (Key: 8842)
SELECT pg_try_advisory_xact_lock(8842) AS lock_acquired;
-- If lock_acquired is true, safe to run single-instance background job!💡Acquiring non-blocking transaction-level advisory lock.
🎯 Interview Key Takeaway
Use advisory locks for application-level concurrency control without row locking overhead.
#Advisory Locks#pg_advisory_lock#Concurrency#Intermediate
Back to all PostgreSQL Questions→