Hi!
I want to increment the value of field SocialSecurityNo for a centrally
controlled table SocialSecurityTable. SocialSecurityTable contains only
one row and one field - SocialSecurityNo. Because, I think,
SocialSecurityNo in the table should not be seen by any other user when
it is being incremented by a function (or the number will duplicate), 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 calculation on UsedNumber and then generate the next free
SocialSecurityNo
-- and assign the value to UsedNumber.
UPDATE SocialSecurityTable SET SocialSecurityNo=UsedNumber;
COMMIT WORK;
RETURN UsedNumber;
END;' LANGUAGE 'plpgsql';
Question A: Is this above function apporpriately designed, or are there
more efficient ways than table locking?
Question B: What problem will happen and how to fix it when statement
"LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE;" fails because this
user (user A) started this transaction AFTER another user (user B) and
user B has not yet COMMITed the transaction?
Regards,
CN
