为 postgresql 存储过程设置隔离级别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6274457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
set isolation level for postgresql stored procedures
提问by beldaz
Hopefully a simple question, but one for which I haven't readily found a decent answer. I'm reliably informed that stored procedures (user-defined DB functions) in PostgreSQL (specifically, version 9.0.4) are inherently transactional, inasmuch as they are called through a SELECT statement which itself is a transaction. So how does one choose the isolation level of the stored procedure? I believe in other DBMSs the desired transactional block would be wrapped in a START TRANSACTION block for which the desired isolation level is an optional parameter.
希望是一个简单的问题，但我还没有轻易找到一个像样的答案。我被可靠地告知 PostgreSQL（特别是 9.0.4 版）中的存储过程（用户定义的 DB 函数）本质上是事务性的，因为它们是通过本身是一个事务的 SELECT 语句调用的。那么如何选择存储过程的隔离级别呢？我相信在其他 DBMS 中，所需的事务块将包装在 START TRANSACTION 块中，所需的隔离级别是可选参数。
As a specific made-up example, say I want to do this:
作为一个特定的虚构示例，假设我想这样做：
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;
And imagine I want to make sure this function is always performed as a serializable transaction (yes, yes, PostgreSQL SERIALIZABLE isn't proper serializable, but that's not the point). I don't want to require it to be called as
想象一下，我想确保这个函数总是作为一个可序列化的事务来执行（是的，是的，PostgreSQL SERIALIZABLE 不是正确的可序列化的，但这不是重点）。我不想要求它被称为
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT add_new_row('foo');
COMMIT;
So how do I push the required isolation level down into the function? I believe I cannot just put the isolation level in the BEGINstatement, as the manual says
那么如何将所需的隔离级别下推到函数中呢？我相信我不能只是把隔离级别放在BEGIN声明中，正如手册所说
It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in.
重要的是不要将 PL/pgSQL 中用于分组语句的 BEGIN/END 的使用与用于事务控制的类似命名的 SQL 命令混淆。PL/pgSQL 的 BEGIN/END 仅用于分组；它们不会开始或结束事务。函数和触发器过程总是在由外部查询建立的事务中执行——它们不能启动或提交该事务，因为没有上下文供它们执行。
The most obvious approach to me would be to use SET TRANSACTIONsomewhere in the function definition, e.g.,:
对我来说最明显的方法是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;
While this would be accepted, it's not clear than I can rely on this to work. The documentationfor SET TRANSACTIONsays
虽然这会被接受，但不清楚我可以依靠它来工作。该文档的SET TRANSACTION说
If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it will appear to have no effect, since the transaction will immediately end.
如果 SET TRANSACTION 在没有事先 START TRANSACTION 或 BEGIN 的情况下执行，它似乎没有任何效果，因为事务将立即结束。
Which leaves me puzzled, since if I call a solitary SELECT add_new_row('foo');statement I would expect (provided I haven't disabled autocommit) the SELECT to be running as a single-line transaction with the session default isolation level.
这让我感到困惑，因为如果我调用一个单独的SELECT add_new_row('foo');语句，我希望（假设我没有禁用自动提交）SELECT 作为具有会话默认隔离级别的单行事务运行。
The manualalso says:
该手册还写道：
The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY) of a transaction has been executed.
事务的第一个查询或数据修改语句（SELECT、INSERT、DELETE、UPDATE、FETCH 或 COPY）执行后，事务隔离级别无法更改。
So what happens if the function is called from within a transaction with a lower isolation level, e.g.,:
那么如果从隔离级别较低的事务中调用该函数会发生什么，例如：
START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE row_counts_table SET count=0;
SELECT add_new_row('foo');
COMMIT;
For a bonus question: does the language of the function make any difference? Would one set the isolation level differently in PL/pgSQL than in plain SQL?
对于一个额外的问题：函数的语言有什么不同吗？在 PL/pgSQL 中设置隔离级别会与在普通 SQL 中不同吗？
I'm a fan of standards and documented best practices, so any decent references would be appreciated.
我是标准和记录最佳实践的粉丝，所以任何体面的参考将不胜感激。
采纳答案by Peter Eisentraut
You can't do that.
你不能那样做。
What you could do is have your function check what the current transaction isolation level is and abort if it's not the one you want. You can do this by running SELECT current_setting('transaction_isolation')and then checking the result.
您可以做的是让您的函数检查当前事务隔离级别是什么，如果它不是您想要的，则中止。您可以通过运行SELECT current_setting('transaction_isolation')然后检查结果来做到这一点。
回答by Denis de Bernardy
The language of the function makes no difference whatsoever.
函数的语言没有任何区别。
This fails:
这失败了：
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
Note that in your particular example, you could do this using a trigger on your first table. Just make sure that row count updates are done in a consistent orderto avoid dead-locks, and you'll do fine in repeatable-read mode.
请注意，在您的特定示例中，您可以使用第一个表上的触发器来执行此操作。只需确保以一致的顺序完成行计数更新以避免死锁，并且在可重复读取模式下您会做得很好。
I'm a fan of standards
我是标准的粉丝
The PL/languages are platform specific.
PL/语言是特定于平台的。
回答by jordani
Transaction isolation means which changes made in other concurent transactions you can access.
事务隔离意味着您可以访问在其他并发事务中所做的更改。
If you want to serialize execution you have to use locks.
如果要序列化执行，则必须使用锁。
You may use after row trigger and update count. "UPDATE row_counts_table" will lock table and all transactions will be serialized. It is slow.
您可以使用后行触发器和更新计数。“UPDATE row_counts_table”将锁定表，所有事务将被序列化。它很慢。
In your example you have two statements. Insert is executed but update have to wait other transactions and count is not valid in this period.
在您的示例中，您有两个语句。执行插入但更新必须等待其他事务并且计数在此期间无效。
回答by Kuberchaun
In PG your procedures aren't separate transactions. That is the stored procedure takes part in an existing transaction.
在 PG 中，您的程序不是单独的事务。即存储过程参与现有事务。
BEGIN TRAN
SELECT 1;
SELECT my_proc(99);
ROLLBACK TRAN;
With that said you have to set the transaction level where the transaction starts which is outside the stored procedure.
话虽如此，您必须在存储过程之外设置事务开始的事务级别。
One option would be to configure the server to run in the isolation you mostly want to use and do a SET for the edge cases where it differs from your server setting.
一种选择是将服务器配置为在您最想使用的隔离中运行，并针对与服务器设置不同的边缘情况执行 SET。
