Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Timeout on advisory locks in postgresql

I'm migrating from ORACLE. Currently I'm trying to port this call:

lkstat := DBMS_LOCK.REQUEST(lkhndl, DBMS_LOCK.X_MODE, lktimeout, true);

This function tries to acquire lock lkhndl and returns 1 if it fails to get it after timeout seconds.

In postgresql I use

pg_advisory_xact_lock(lkhndl);

However, it seems that it waits for lock forever. pg_try_advisory_xact_lock returns immediately if fails. Is there a way to implement timeout version of lock acquiring?

There is lock_timeout setting, but I'm not sure is it applicable to advisory locks and how pg_advisory_xact_lock would behave after timeout.

like image 689
ov7a Avatar asked Aug 09 '16 14:08

ov7a


People also ask

What is lock timeout in Postgres?

lock_timeout is a configuration parameter determining the period of time which should elapse before an attempt to acquire a lock is abandoned. lock_timeout was added in PostgreSQL 9.3.

What is the default timeout in Postgres?

The default connection timeout limit to PostgreSQL data sources is 30 seconds.

What is advisory lock Postgres?

PostgreSQL advisory locks are application-level, cooperative locks explicitly locked and unlocked by the user's application code. An application can use PostgreSQL advisory locks to coordinate activity across multiple sessions.

How do I stop table locking in Postgres?

Do this instead: -- select, update, insert, and delete block until the catalog is update (milliseconds) ALTER TABLE items ADD COLUMN last_update timestamptz; -- select and insert go through, some updates and deletes block while the table is rewritten UPDATE items SET last_update = now();


1 Answers

This is a prototype of a wrapper that poorly emulates DBMS_LOCK.REQUEST - constrained to only one type of lock (transaction-scope advisory lock).

To make function fully compatible with Oracle's, it would need several hundreds lines. But that's a start.

CREATE OR REPLACE FUNCTION
advisory_xact_lock_request(p_key bigint, p_timeout numeric)
RETURNS integer
LANGUAGE plpgsql AS $$
/*  Imitate DBMS_LOCK.REQUEST for PostgreSQL advisory lock. 
Return 0 on Success, 1 on Timeout, 3 on Parameter Error. */
DECLARE
    t0 timestamptz := clock_timestamp();
BEGIN
    IF p_timeout NOT BETWEEN 0 AND 86400 THEN
        RAISE WARNING 'Invalid timeout parameter';
        RETURN 3;
    END IF;
    LOOP
        IF pg_try_advisory_xact_lock(key) THEN
            RETURN 0;
        ELSIF clock_timestamp() > t0 + (p_timeout||' seconds')::interval THEN
            RAISE WARNING 'Could not acquire lock in % seconds', p_timeout;
            RETURN 1;
        ELSE
            PERFORM pg_sleep(0.01); /* 10 ms */
        END IF;
    END LOOP;
END;
$$;

Test it using this code:

SELECT CASE 
    WHEN advisory_xact_lock_request(1, 2.5) = 0
    THEN pg_sleep(120)
END; -- and repeat this in parallel session 

/* Usage in Pl/PgSQL */

lkstat := advisory_xact_lock_request(lkhndl, lktimeout);
like image 136
filiprem Avatar answered Sep 19 '22 12:09

filiprem