Skip to main content

Advisory Locks of PostgreSQL

· One min read
Ivan Tsai
Ivan Tsai
Backend engineer

PostgreSQL provides advisory locks, which do not require a physical table. We can define the purpose of the lock according to our applications, and it will automatically release the lock when the session or transaction ends.

Session 1

SELECT pg_advisory_lock(100);
--- 
SELECT pg_advisory_unlock(100);

Session 2

SELECT pg_advisory_lock(100);
--- Waits for Session 1 to release or end

Within the same session/transaction, pg_advisory_lock can be called repeatedly. Once the lock is acquired, only the same session/transaction can unlock it. For a complete list of functions, refer to this link.

Use Cases​

In a Micro Service architecture, sometimes we need workers to "claim" work, and only those who claim it can proceed to execute, ensuring that the same task is executed only once. This is where PostgreSQL's advisory locks come in handy:

function check(jobId) {
  if(db.query(`SELECT pg_try_advisory_lock ($1)`, [jobId])) {
    // Do the job
  }
}

This way, only one worker will successfully lock and become the master for a task, and it will automatically release it after the session ends.

References​