ISOLATION LEVEL SERIALIZABLE锁定PostgreSQL 9.6 [英] ISOLATION LEVEL SERIALIZABLE locking postgresql 9.6

问题描述

DO $$
BEGIN
raise notice '%', (SELECT * from public.clientcalledthisfunction(1,2));
END $$;

CREATE OR REPLACE FUNCTION  public.clientcalledthisfunction(userid1_ integer, userid2_ integer)
RETURNS integer
AS $$
DECLARE
result integer;
BEGIN
result:=(SELECT  * from public.call_updatedata(userid1_, userid2_)) ;
RETURN result;
EXCEPTION WHEN others THEN
End $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.call_updatedata(userid1_ integer, userid2_ integer)
RETURNS integer
AS $$
DECLARE
userdata_1 integer;
userdata_2 integer;
userdata_total integer;
BEGIN

SELECT * FROM public.updatedata(userid1_) INTO userdata_1;

SELECT * FROM public.updatedata(userid2_) INTO userdata_2;

userdata_total:=(userdata_1 + userdata_2);
RETURN userdata_total;
EXCEPTION WHEN others THEN
End $$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.updatedata(userid_ integer)
RETURNS integer
AS $$
DECLARE
userdata_ integer;
BEGIN

LOOP
SET  TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
SAVEPOINT foo;

SELECT userdata FROM public.footable WHERE userid=userid_ INTO userdata_;

UPDATE public.footable SET userdata = userdata_ + 1 WHERE userid=userid_ ;

EXIT ;
EXCEPTION WHEN others THEN
    ROLLBACK TO SAVEPOINT foo;
END;
END LOOP;
RETURN userdata_ + 1;
EXCEPTION WHEN others THEN
END $$ language plpgsql;

客户端调用public.client namedthisfunction（）函数;

我需要在 SELECT + UPDATE ISOLATION LEVIEL SERIALIZABLE $ c>在 updatedata（）函数中，因为我不希望丢失更新 ...我想在中仅设置SERIALIZABLE隔离级别 code> public.updatedata函数（）。

I need to implement ISOLATION LEVEL SERIALIZABLE on SELECT + UPDATE in updatedata() function, because i dont want "lost update"...I want to set SERIALIZABLE isolation level only in public.updatedata function().

如果有例外，请在 updatedata（）函数中；我希望它回滚到 savepoint foo 并重试 select + update 再次循环执行...

And in updatedata() function if there is an exception; i want it to rollback to savepoint foo and retry select + update process through loop again...

但是我收到错误说控件到达功能末尾而没有返回 ...我不明白问题出在哪里。

But i get error saying "control reached end of function without RETURN"...I can't understand where the problem is.

推荐答案

我要设置仅在public.updatedata函数（）中可SERIALIZABLE隔离级别。

I want to set SERIALIZABLE isolation level only in public.updatedata function().

您不能。隔离是事务级别的属性。

You can't. Isolation is a transaction-level property.

如果有异常，则在updatedata（）函数中；我希望它回滚到保存点foo

And in updatedata() function if there is an exception; i want it to rollback to savepoint foo

您可以使用 BEGIN ... EXCEPTION ... <

You can use a BEGIN ... EXCEPTION ... block for that.

但是如果您使用序列化隔离，这实际上将不起作用，因为某些序列化失败只能在提交时找到。

But this wouldn't actually work if you were using serializabile isolation since some serialization failures can only be found at commit time.

这篇关于ISOLATION LEVEL SERIALIZABLE锁定PostgreSQL 9.6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！