[SOLVED] How to avoid dead lock while using advisory locks in postgresql

How to avoid dead lock while using advisory locks in postgresql

I'm using the pg_advisory_lock function in postgres which blocks/waits if it's not able to get a lock on a particular key. I was wondering if there is some way to get a timeout on this? I couldn't find anything with what I've looked. If not, is there a way for a session to force release a lock obtained by a different session?

Solution

If you don't want to block at all, you can just call pg_try_advisory_lock().

If a blocking pg_advisory_lock() call does result in a deadlock, it will automatically time out after the interval specified by the deadlock_timeout setting (one second by default). You can also limit the lock wait time - deadlocked or not - by setting lock_timeout (which by default is disabled).

Note that triggering either of these timeouts will raise an error, so it may be useful to wrap the error handling in a function, e.g.:

CREATE FUNCTION pg_try_advisory_lock_with_timeout(key bigint) RETURNS boolean
SET lock_timeout TO '1s'
AS $$
BEGIN
  PERFORM pg_advisory_lock(key);
  RETURN true;
EXCEPTION
  WHEN lock_not_available OR deadlock_detected THEN
    RETURN false;
END;
$$
LANGUAGE plpgsql;