[Solved] Which approach is best?-- Session 1 and 2 run this code:SELECT pg_try_advisory_lock(42);-- If true, run critical section, thenSELECT pg_advisory_unlock(42); | PostgreSQL

PostgreSQL - Transactions and Concurrency

You want to ensure two different sessions do not run a critical section simultaneously using advisory locks. Which approach is best?

-- Session 1 and 2 run this code:
SELECT pg_try_advisory_lock(42);
-- If true, run critical section, then
SELECT pg_advisory_unlock(42);

AUse pg_advisory_unlock before acquiring lock to clear old locks

BUse pg_try_advisory_lock to attempt lock and skip if busy

CUse pg_advisory_lock to wait until lock is available before running

DUse random keys each time to avoid conflicts

Step-by-Step Solution

Solution:

Step 1: Understand locking strategies
pg_try_advisory_lock returns immediately and may skip critical section if lock busy; pg_advisory_lock waits until lock is free.
Step 2: Choose best approach for critical section
To ensure only one session runs critical section at a time, waiting for the lock is safer than skipping it.
Step 3: Evaluate other options
Unlocking before acquiring is unsafe and random keys defeat locking purpose.
Final Answer:
Use pg_advisory_lock to wait until lock is available before running -> Option C
Quick Check:
Waiting lock ensures exclusive access [OK]

Quick Trick: Use pg_advisory_lock to wait for exclusive access [OK]

Common Mistakes:

Master "Transactions and Concurrency" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

You want to ensure two different sessions do not run a critical section simultaneously using advisory locks. Which approach is best?