ISOLATION LEVEL SERIALIZABLE locking postgresql 9.6

Question

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;

Client calls public.clientcalledthisfunction() function;

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().

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.

freakish · Answer 1 · 2017-01-06T12:16:32.733

0

In every function you have

EXCEPTION WHEN others THEN
End

at the end. If any exception occures then this piece of code fires. And since there is no return statement afterwards it crashes with "control reached end of function without RETURN" error.

Analyze those exceptions and you will know exactly what is going on. Or even better, simply remove those lines. After all EXCEPTION WHEN others is a root of all evil.

EDIT: After some searching it seems that you can't change isolation level inside a function. After all calling a function is a query and isolation level has to be set before any query.

edited Jan 06 '17 at 12:16

answered Jan 06 '17 at 11:25

freakish

54,167
9
132
169

if i remove it as you said, then it gives an error saying "SET TRANSACTION ISOLATION LEVEL must be called before any query" – sommeguyy Jan 06 '17 at 11:46
@sommeguyy Here you go. You have `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;` inside loop. I assume that if you move it outside (i.e. 1 line up) then it should be fine. – freakish Jan 06 '17 at 12:05
@sommeguyy Right, after some searching it seems that you can't change isolation level inside a function. Read this: http://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures After all calling a function is already a query. – freakish Jan 06 '17 at 12:12

score 0 · Accepted Answer · answered Jan 06 '17 at 13:38

0

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

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

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

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.

answered Jan 06 '17 at 13:38

Craig Ringer

307,061
76
688
778

...."But this wouldn't actually work if you were using serializabile isolation since some serialization failures can only be found at commit time.".....There is no other way to do this? I mean a work around? – sommeguyy Jan 06 '17 at 15:50
@sommeguyy You haven't clearly explained the underlying problem you are trying to solve, so it's hard to say. – Craig Ringer Jan 07 '17 at 05:22
eventually i solved my problem by using "FOR UPDATE" lock. – sommeguyy Jan 08 '17 at 21:43

ISOLATION LEVEL SERIALIZABLE locking postgresql 9.6

2 Answers2

Linked