What lock to use?

From: Boris Folgmann <boris(at)folgmann(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: What lock to use?
Date: 2004-09-22 15:50:02
Message-ID: cis6vf$pn3$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Boris Folgmann 2004-09-22 16:00:10 Re: Trouble with query logging
Previous Message Konstantin Danilov 2004-09-22 10:07:40 start psql