Hello!
I have read pgsql online docs but still need helps. (And pardon me for
cross posting to pgsql-novice as I have not received responses to my
problem.)
I want to increment the value of field SocialSecurityNo in a centrally
controlled table SocialSecurityTable. SocialSecurityTable contains only
one row and one field - SocialSecurityNo. Because, I think,
SocialSecurityNo in SocialSecurityTable should not even be read by any
other user when it is being incremented by a user (or the number will
duplicate), and setting transaction isolation level seems to be not
restrictive enough in this case, I use table lock as follows:
CREATE FUNCTION GetAndSetNextNumber () RETURNS TEXT AS '
DECLARE UsedNumber TEXT;
BEGIN BEGIN WORK; LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE; SELECT SocialSecurityNo INTO UsedNumber FROM
SocialSecurityTable;
--Do a lot of calculation on UsedNumber
-- and generate the next free SocialSecurityNo
-- and assign this free value to UsedNumber. UPDATE SocialSecurityTable SET SocialSecurityNo=UsedNumber; COMMIT WORK;
RETURNUsedNumber;
END;' LANGUAGE 'plpgsql';
Question A: Is this above function apporpriately designed, or are there
more efficient ways than table locking?
Question B: I think statement "LOCK SocialSecurityTable IN ROW EXCLUSIVE
MODE;" will fail when this user (user A) executed this statement AFTER
another user (user B) and user B has not yet COMMITed his transaction?
What are the solutions for this (LOCK TABLE fails in function)?
Regards,
CN