时间戳

首页 > 解决方案 > Postgres 在函数内锁定表不起作用？

postgresql - Postgres 在函数内锁定表不起作用？

问题描述

CREATE OR REPLACE FUNCTION() 
RETURND VOID AS

BEGIN

FOR I IN 1..5
LOOP
LOCK TABLE tbl_Employee1 IN EXCLUSIVE MODE;
INSERT INTO tbl_Employee1
VALUES 
(i,'test');

END LOOP;

COMMIT;

END;
$$ LANGUAGE PLPGSQL

当我选择表时，它进入无限循环意味着事务未完成。请帮帮我？

标签： postgresql

解决方案

您的代码已被剥离太多，以至于不再有意义。

但是，您应该只锁定表一次，而不是在循环的每次迭代中。另外，你不能commit在 Postgres 的函数中使用，所以你也必须删除它。不为插入语句提供列名也是不好的编码风格（在 Postgres和Oracle 中）。

即时解决方案：

CREATE OR REPLACE FUNCTION ...
  RETURNS VOID AS
$$
BEGIN
  LOCK TABLE Employee1 IN EXCLUSIVE MODE;
  FOR I IN 1..5 LOOP
    INSERT INTO Employee1 (id, name)
    VALUES (i,'test');
  END LOOP;
  -- no commit here!    
END;
$$ LANGUAGE PLPGSQL

以上在 Postgres 中是不必要的复杂，并且可以在没有循环的情况下更有效地实现：

CREATE OR REPLACE FUNCTION ....
  RETURNS VOID AS
$$
BEGIN
  LOCK TABLE Employee1 IN EXCLUSIVE MODE;
  INSERT INTO Employee1 (id, name)
  select i, test
  from generate_series(1,5);

END;
$$ LANGUAGE PLPGSQL

一开始以独占模式锁定表似乎是个坏主意。在 Oracle 中也是如此，但在 Postgres 中，这可能会产生更严重的影响。如果要防止表中出现重复，请创建唯一索引（或约束）并处理错误。或者insert ... on conflict在 Postgres 中使用。这将比锁定一个完整的表更有效（和可扩展）。

另外：LOCK TABLE IN EXCLUSIVE MODE;在 Oracle 和 Postgres 中的行为不同。虽然 Oracle 仍将允许对该表进行只读查询，但您在 Postgres 中阻止对它的所有访问 -包括SELECT 语句。

推荐阅读