Postgresql中的咨询锁超时 [英] Timeout on advisory locks in postgresql

问题描述

我正在从ORACLE迁移.目前，我正在尝试移植此呼叫:

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

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

此函数尝试获取锁lkhndl，如果在timeout秒后未能获取它，则返回1.

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

在Postgresql中，我使用

In postgresql I use

pg_advisory_xact_lock(lkhndl);

但是，似乎它会永远等待锁定. pg_try_advisory_xact_lock 如果失败则立即返回.有没有一种方法可以实现锁获取的超时版本?

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?

有 lock_timeout 设置，但我不确定它是否适用于

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.

推荐答案

这是包装器的原型，该包装器无法很好地模拟DBMS_LOCK.REQUEST-仅限于一种类型的锁(事务范围咨询锁).

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

要使功能与Oracle完全兼容，它需要数百行.但这是一个开始.

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);

这篇关于Postgresql中的咨询锁超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！