为PostgreSQL存储过程设置隔离级别

Question

为PostgreSQL存储过程设置隔离级别

postgresqlstored-proceduresisolation-level

30

希望这是一个简单的问题，但我还没有找到一个好的答案。我被可靠地告知，在PostgreSQL（特别是版本9.0.4）中，存储过程（用户定义的DB函数）本质上是事务性的，因为它们通过一个SELECT语句被调用，而SELECT本身就是一个事务。那么如何选择存储过程的隔离级别呢？我相信在其他DBMS中，所需的事务块将被包装在START TRANSACTION块中，其中所需的隔离级别是一个可选参数。

以一个具体的虚构示例为例，假设我想要做到这一点：

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

假设我想确保此函数始终作为可序列化事务执行（是的，是的，PostgreSQL SERIALIZABLE 不是真正的可序列化，但这不是重点）。我不想要求以特定方式调用该函数。

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT add_new_row('foo');
COMMIT;

那么我如何将所需的隔离级别下推到函数中呢？我认为我不能仅在 BEGIN 语句中放置隔离级别，因为手册中说：

重要的是不要混淆使用 BEGIN/END 来分组语句在 PL/pgSQL 中与同名的 SQL 命令用于事务控制。 PL/pgSQL 的 BEGIN/END 仅用于分组；它们不会开始或结束一个事务。函数和触发器程序总是在外部建立的事务中执行查询——他们不能启动或提交该事务，因为没有上下文可以执行。

对我来说最明显的方法是在函数定义中的某个位置使用 SET TRANSACTION，例如：

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

尽管这样做是可以被接受的，但我不能确定能够依赖它正常工作。 SET TRANSACTION 的文档说：

如果在没有先执行 START TRANSACTION 或 BEGIN 的情况下执行 SET TRANSACTION，则它似乎没有任何效果，因为事务将立即结束。

这让我感到困惑，因为如果我调用一个孤立的 SELECT add_new_row('foo'); 语句，并且我没有禁用自动提交，那么我会期望该 SELECT 作为具有会话默认隔离级别的单行事务运行。

手册还说：

当事务的第一个查询或数据修改语句（SELECT、INSERT、DELETE、UPDATE、FETCH 或 COPY）已经执行后，就不能再更改事务的隔离级别。

那么，如果从具有较低隔离级别的事务中调用该函数会发生什么，例如：

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE row_counts_table SET count=0;
SELECT add_new_row('foo');
COMMIT;

额外的问题：函数的语言是否有任何区别？在 PL/pgSQL 中与纯 SQL 中设置隔离级别会有所不同吗？

我是一个标准和文档化最佳实践的粉丝，因此任何可靠的参考资料都将不胜感激。

- beldaz

1

当您尝试在函数内部使用 SET TRANSACTION 时会发生什么？ - user330315

@a_horse_with_no_name：正如我所提到的，我认为SET TRANSACTION是我需要的，而且其中包含的函数是被接受的，但有时这并不意味着太多（有些选项有时会被忽略），因此我正在寻找一种有文档支持的方法，而不仅仅是看起来有效的东西。 - beldaz

2

Postgres很少会盲目执行你告诉它要做的事情，即使它这样做了，我也希望它能发出警告。 - Denis de Bernardy

如果函数内的 SET TRANSACTION 编译没有错误并且运行没有错误，它就不会被“吞掉” - 除非你启用了自动提交。但是那样的话，你根本就没有事务。 - user330315

@Denis，@a_horse_with_no_name：我曾经遇到过Postgres接受了SPS，但在创建函数语句中设置的选项并未出现在存储定义中的情况。即使如此，是否接受“SET TRANSACTION”语句并不意味着它能够实现我的目的。我将扩展我的问题以澄清。 - beldaz

4个回答

1

函数的语言并不重要。

这将失败：

test=# create function test() returns int as $$
  set transaction isolation level serializable;
  select 1;
$$ language sql;
CREATE FUNCTION
test=# select test();
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL function "test" statement 1

请注意，在您的特定示例中，您可以使用第一个表上的触发器来完成此操作。只需确保行计数更新以一致的顺序进行，以避免死锁，并且在可重复读模式下，您将会做得很好。

我是标准的粉丝

PL /语言是特定于平台的。

- Denis de Bernardy

您介意通过展示应该在哪里设置来扩展您的答案吗？正如我所提到的，这个例子完全是虚构的，所以我并不是想误导您认为我正在寻求使用触发器的不同方法。至于PL语言是否特定于平台，它们仍然可以有文档。我只是觉得Postgres的文档很难深入了解某些具体问题的正确答案，因此指示确切的查找位置对我和其他读者都会有用。 - beldaz

只需将其包含在函数体中... 将 begin 替换为 begin isolation level serializable（如果出现错误，请添加额外的 begin/end 块）。 - Denis de Bernardy

1

@Denis：按照您的建议替换begin似乎会与Postgres v9手册39.2产生冲突 - “PL/pgSQL的BEGIN / END仅用于分组；它们不会开始或结束事务”。 - beldaz

@Denis：谢谢。这是否意味着没有办法做到这一点？http://postgresql.1045698.n5.nabble.com/Changing-the-transaction-isolation-level-within-the-stored-procedure-td2144791.html表明可能是这种情况。 - beldaz

0

事务隔离意味着你可以访问其他并发事务中所做的更改。

如果要串行执行，必须使用锁。

您可以使用行触发器和更新计数。 "UPDATE row_counts_table" 将锁定表格，并且所有事务将被序列化。这很慢。

在您的示例中有两个语句。插入已执行，但更新必须等待其他交易，并且在此期间计数无效。

- jordani

1

也许是10年前的事了。但如果你指的是一般情况，那么不需要使用锁定来进行序列化。最初的SQL标准假定将使用锁定，这就是隔离级别基于哪个特定锁定异常可以接受的原因。然而，Postgres和SQL Server采用的MVCC方法与锁定根本不同，尽管存在写偏差的问题，但已经制定了方法（例如Fekete等人的方法）来避免这种情况，从而提供可串行化执行。 - beldaz

0

在PG中，您的过程不是单独的事务。也就是说，存储过程参与现有事务。

BEGIN TRAN

SELECT 1;
SELECT my_proc(99);

ROLLBACK TRAN;

言归正传，您必须在存储过程之外设置事务级别，以确定事务开始的位置。

其中一个选项是配置服务器以运行您希望使用的隔离级别，并针对与服务器设置不同的边缘情况进行SET操作。

- Kuberchaun

唉，这让我失望了，因为我想要一些操作在服务器端以特定的事务隔离级别一起发生。比如给账户加上利息，你不希望使用间歇性的当前账户价值，如果其他交易正在执行余额转移。如果必须使用客户端事务来解决这个问题，那就太遗憾了，而且希望会话默认事务级别是正确的也并非绝对可靠。 - beldaz

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Peter Eisentraut · Accepted Answer

你不能这样做。

你可以让你的函数检查当前事务隔离级别，如果不是想要的级别就中止。你可以通过运行SELECT current_setting('transaction_isolation')并检查结果来实现此操作。