如何在PostgreSQL函数中设置REPEATABLE READ事务隔离级别？

PostgreSQL是支持在函数中使用REPEATABLE READ隔离级的，你遇到的错误是用法问题，并非不支持该特性。

错误原因 #

你使用的SET TRANSACTION ISOLATION LEVEL命令有严格的执行时机限制：它必须在事务启动后、事务内的第一个查询执行之前调用。但函数的执行逻辑导致该命令无法满足这个要求：

• 如果函数在显式事务中被调用，此时事务已经启动，甚至可能已经执行过其他语句；
• 如果是自动提交模式下调用函数，函数本身会被包裹在一个隐式事务中，进入函数体时这个隐式事务已经启动，相当于错过了SET TRANSACTION的执行时机。
  这就触发了报错提示的“必须在任何查询前调用”的限制。

解决方案 #

有两种可行的修正方式：

1. 在调用函数前设置隔离级 #

如果业务场景允许，在调用函数前先设置事务隔离级，再执行函数：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT my_function();

2. 在函数内使用SET LOCAL修改隔离级 #

把函数里的SET TRANSACTION换成SET LOCAL，这个命令允许在事务执行过程中修改隔离级，且作用域仅限当前事务：

CREATE OR REPLACE FUNCTION my_function() RETURNS void AS $$
BEGIN
  SET LOCAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  PERFORM now();
END;
$$ LANGUAGE plpgsql;

注意：SET LOCAL只能在活跃事务中生效，自动提交模式下调用函数时，函数的执行会被包裹在一个隐式事务里，所以这个写法是有效的。

内容的提问来源于stack exchange，提问作者KIM