CREATE OR REPLACE FUNCTION public.updatedata(userid_ integer)
RETURNS integer
AS $$
DECLARE
userdata_ integer;
BEGIN
LOOP
BEGIN
PERFORM 1 FROM public.footable f WHERE f.userid=userid_ LIMIT 1 FOR UPDATE ;
userdata_:=(SELECT f.userdata FROM public.footable f WHERE f.userid=userid_ );
UPDATE public.footable f SET userdata = userdata_ + 1 WHERE f.userid=userid_ ;
EXIT ;
EXCEPTION WHEN others THEN
END;
END LOOP;
RETURN userdata_ + 1;
EXCEPTION WHEN others THEN
END $$ language plpgsql;
Can i prevent "lost update"
issue for userdata column, when i use "FOR UPDATE"
lock like this?
Actually i wanted to use serializable isolation
in the first place, but it turns out that in serializable isolation
some errors can only be detected after commıt(link)
Your code will avoid a lost update, but you can achieve the same with a simple SQL statement:
There can be no lost update, because the first
UPDATE
will lock the row in exclusive mode, and any concurrentUPDATE
will have to wait until the transaction holding the lock is done, and then the result of the firstUPDATE
is visible to the second one.