PostgreSQL表锁冻结

Question

我们有一个PostgreSQL函数，它已经运行了相当长的一段时间，但最近由于未知的原因开始挂起……

我深入研究了这个函数，该函数的第一行是"lock ;“

注释掉这一行后，函数运行得很好，但允许运行该表锁会导致我的函数，从而导致我的应用程序无限期地锁定(冻结)……

代码本身有点傻(这是我们目前正在维护的遗留代码)……所以请不要问为什么运行这段代码...只需知道它获取了一个惟一的ID，用于应用程序中的一组给定函数。我也知道，当它耗尽唯一ID时，就会出现问题……我们定期刷新这些ID，以便始终有可用的ID。

declare
    tmp int4;
begin
    lock table1; --the offending line
    for g in 1 .. 999 loop
        select g_id into tmp from table1 where g_id = g;
        if not found then
            insert into table1 (g_id, type, date, status) values (g, 'type', current_timestamp, 'w');
            return g;
        end if;
    end loop;
    raise exception 'unable to find unique id';
    return 0;
end;

同样，在没有" lock“命令的情况下，函数会运行，但是一旦我允许lock命令运行，它就会挂起。有没有人看到过类似的PostgreSQL问题并有解决方案？

Answer 1

一旦函数挂起，请查看pg_locks表，看看是什么在持有锁。从那里，你应该能够找出你的死锁在哪里。请记住，锁将一直保持到事务结束。

由于您正在执行插入操作，因此在获取一个空闲的id并将其插入到表中时，您需要使用锁来避免竞争条件。如果应用程序可以处理它，请考虑使用序列来生成ids。

正如"a_horse_with_no_name“所指出的，这是一种寻找新id的糟糕方式。SQL是基于集合的，所以使用集合操作来查找未使用的ids。

举个例子：

首先，测试数据表。您不需要这个，因为您将使用自己的table1。

create temp table table1 (g_id integer);
insert into table1
select x from generate_series(1,999) x
where random() < 0.10;

获取未使用的id：

select
generate_series(1,999) new_id
except
select g_id from table1
limit 1;

如果您需要最小的未使用id，则使用：

with unused as (
select
generate_series(1,999) new_id
except
select g_id from table1
)
select new_id from unused order by new_id limit 1;