PostgreSQL中的咨询锁超时问题

Question

我正在从ORACLE迁移。 目前我正在尝试转移这个调用：

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

此函数尝试获取锁lkhndl，如果在timeout秒后无法获取，则返回1。

在postgresql中我使用

pg_advisory_xact_lock(lkhndl);

然而，它似乎无限期地等待锁。如果失败，pg_try_advisory_xact_lock会立即返回。是否有一种实现锁获取超时版本的方法？

有lock_timeout设置，但我不确定它是否适用于advisory locks，以及在超时后pg_advisory_xact_lock会如何行动。

- ov7a

---

你可以使用 SET LOCAL 来设置 statement_timeout。不幸的是，它只能在会话级别（或者使用 LOCAL 时，在事务级别）生效，而非语句级别。 - Craig Ringer

我对此无异议。但是超时后会发生什么？我如何知道锁是否已被获取？ - ov7a

超时后，执行被中止，我认为你甚至无法捕获这些错误。为了完全模拟Oracle函数，恐怕你需要编写一个重试循环，并可能使用pg_sleep()调用（但我不确定它的性能如何，我从未编写过类似的代码）。 - pozs

说实话，我们似乎真的需要一个补丁来实现类似于你现在在Oracle中使用的功能。你的C编程怎么样？ - Craig Ringer

足够用于编程，但不足以进行良好的编程，我认为。补丁会很好，但我担心对我来说毫无价值，因为很难说服我们的架构使用不稳定的版本。 - ov7a

Answer 1

这是一个包装器的原型，它模拟了DBMS_LOCK.REQUEST，但只限于一种类型的锁（事务范围咨询锁）。 要使函数与Oracle完全兼容，需要几百行代码。但这是一个开始。

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

使用以下代码进行测试：

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

- filiprem

---

更好的答案：https://stackoverflow.com/questions/41230942/how-to-avoid-dead-lock-while-using-advisory-locks-in-postgresql/41240274#41240274 - undefined