Hi!
I've got a function that inserts a new record if it does not already exist.
The construction is very simple:
SELECT INTO ... FROM tab WHERE pkey=$1;
IF NOT FOUND THEN
INSERT INTO tab ...;
END IF;
What lock has to be aquired on table tab before the SELECT INTO so that I
can be sure, that parallel calls of the function do not try to insert the
same record twice, which results in the second transaction to be aborted
due to the violation of the pkey UNIQUE restriction?
Of course I'm looking for the least restricive one, to get the best
performance for the entire multi-threaded applications.
see you,
boris
I've got a function that inserts a new record if it does not already exist.
The construction is very simple:
SELECT INTO ... FROM tab WHERE pkey=$1;
IF NOT FOUND THEN
INSERT INTO tab ...;
END IF;
What lock has to be aquired on table tab before the SELECT INTO so that I
can be sure, that parallel calls of the function do not try to insert the
same record twice, which results in the second transaction to be aborted
due to the violation of the pkey UNIQUE restriction?
Of course I'm looking for the least restricive one, to get the best
performance for the entire multi-threaded applications.
see you,
boris