PostgreSQL Advisory Lock
PostgreSQL Advisory Lock is a SQL statement in the PostgreSQL Specific category. PostgreSQL: application-defined locks that dont depend on table rows. Useful for coordinating between sessions. The syntax is SELECT pg_advisory_lock(key); SELECT pg_advisory_unlock(key);. It returns advisory lock. A typical example: -- Session 1 acquires lock: SELECT pg_advisory_lock(12345); -- Session 2 tries to acquire same lock: SELECT pg_advisory_lock(12345); -- Blocks until session 1 releases -- Session 1 releases: SELECT pg_advisory_unlock(12345); -- Try-lock (non-blocking): SELECT pg_try_advisory_lock(12345); -- Returns true/false -- Session-level locks: -- pg_advisory_lock: released at session end or explicit unlock -- pg_advisory_xact_lock: released at transaction end -- Common use case: job queue coordination SELECT pg_try_advisory_lock(12345) AS locked; -- Only one worker… A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).