FOR UPDATE lock postgresql 9.6

Question

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)

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

Your code will avoid a lost update, but you can achieve the same with a simple SQL statement:

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

There can be no lost update, because the first UPDATE will lock the row in exclusive mode, and any concurrent UPDATE will have to wait until the transaction holding the lock is done, and then the result of the first UPDATE is visible to the second one.

FOR UPDATE lock postgresql 9.6

1 Answers1