Postgres Pro
Downloads
Home   >   mailing lists

Thread: Basic locking question

Basic locking question

From
Ben
Date:
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?

Re: Basic locking question

From
"Joshua D. Drake"
Date:
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/

Re: Basic locking question

From
Ben
Date:
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 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
>
>
>

Re: Basic locking question

From
Tom Lane
Date:
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

Re: Basic locking question

From
Ben
Date:
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:

>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
>
>

Re: Basic locking question

From
Michael Fuhr
Date:
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