Would this mutex implementation make sense in PostgreSQL?

Question

I Need to implement synchronization between database sessions in PostgreSQL.

In SQL Server, I would implement it by creating my own "locking" table.

Create table MyLock(LockName VARCHAR(100) NOT NULL UNIQUE, LockOwner INT NULL)

I do not use explicit transaction to avoid really locking things up, I would acquire my "singleton" lock by putting my session Id as "owner".

UPDATE MyLock
  SET LockOwner = *MySessionId*
  WHERE LockName = 'Singleton' 
  AND LockOwner IS NULL;

By not using explicit transactions, I am not holding up other processes. You could think of this as "soft lock"...

If my update succeeds, then I know I "have" the lock and I can process some bit of code while other(s) would wait. If my update updated nothing, I know someone else has the "lock" and I wait, or give up.

I need to implement something similar in PostgreSQL.

Would you do it like this?

Answer 1

No, I would do it differently.

The problem is that you need to keep polling the lock, which means unnecessarily wasted CPU time or longer waits than necessary.

This requirement is perfect for PostgreSQL's advisory locks:

Instead of a lock name like Singleton you pick a lock number, e.g. 1234.

To obtain the lock, run

SELECT pg_advisory_lock(1234);

To release the lock, run

SELECT pg_advisory_unlock(1234);

This works just like normal database locks do, by suspending the calling process if the lock is unavailable and resuming it as soon as the lock holder releases the lock. There are also functions to “poll” an advisory lock for availability without blocking.

These locks are independent of PostgreSQL's transactions, they are held until released or untill the database session ends (so there is no danger of an “orphaned lock”).

These locks also don't interfere with autovacuum operations.

This is the perfect tool if an application wants to synchronize using database techniques.