Basic locking question

Ben wrote:

I'm not very familiar with explicitly locking things in SQL, so this may
be a dumb question, but.....

I've got a stored proc that takes a text key and returns an ID. If the
key isn't in a lookup table, it adds it and will return the
auto-generated serial number. If it's already there, it simply returns
the existing serial number.

I'm concerned about multiple sessions calling this at the same time with
the same key. While I *could* just put in a unique constraint and let
things fail, that's hardly graceful. It seems I should be able to lock
the lookup table, but I don't really know what mode to lock that table
in. Will ROW EXCLUSIVE keep the same key from being entered twice?

You are probably looking for select for update:

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Doesn't select ... for update only lock the rows returned in the select?
In my case, I'm worried about situations when no rows will be returned
and two separate transactions will try to insert the same key.

Joshua D. Drake wrote:

Ben <bench@silentmedia.com> writes:

Doesn't select ... for update only lock the rows returned in the select?

Right.

In my case, I'm worried about situations when no rows will be returned
and two separate transactions will try to insert the same key.

Pretty much the only thing you can do about that is to take an exclusive
table-level lock. This is simple but pretty awful for concurrency
(since the lock blocks everyone else from inserting ANY key, not only
the one you are inserting).

Consider using a sequence object to generate the keys, instead.

regards, tom lane

Right, I understand the badness of the situation, but unfortunately the
keys are externally generated and I have no control over them.

So I'm looking for "lock <tablename> in exclusive mode"?

Tom Lane wrote:

On Tue, Sep 06, 2005 at 04:25:38PM -0700, Ben wrote:

So I'm looking for "lock <tablename> in exclusive mode"?

What version of PostgreSQL are you using? In 8.0 and later a
PL/pgSQL function could trap a unique constraint violation and issue
a SELECT query instead. If that sounds ugly then I'd say locking
the entire table is even uglier.

Here's a possible solution (only minimally tested):

CREATE FUNCTION getkey(k text) RETURNS integer AS $$
DECLARE
retval integer;
BEGIN
LOOP
SELECT INTO retval id FROM foo WHERE keyval = k;

EXIT WHEN FOUND;

BEGIN
INSERT INTO foo (keyval) VALUES (k);
RETURN currval(pg_get_serial_sequence('foo', 'id'));
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
NULL;
END;
END LOOP;

RETURN retval;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

This function should handle race conditions, and it should only
block when multiple transactions try to insert the same key. If
the key already exists then the expensive exception-handling code
won't be entered. Alternatively, you could try the INSERT first
and then do the SELECT if the INSERT failed.

--
Michael Fuhr