Re: SELECT ... FOR UPDATE performance costs? alternatives?

On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:

> All,
>
> I have a stored procedure that I use to manage a queue.  I want to
> pop an item off the queue to ensure that only one server is
> processing the queue item, so inside PGSQL, use SELECT ... FOR
> UPDATE to lock the row.  Here's how I pop the queue item:
>
> ----------------------------------------------------------------------
> CREATE OR REPLACE FUNCTION
> "public"."reserve_next_tcqueue" (in_hostname varchar, in_status
> char, in_new_status char) RETURNS bigint AS
> $body$
> DECLARE
>     my_reserved_id BIGINT;
> BEGIN
>      /* find and lock a row with the indicated status */
>      SELECT tcq_id
>      INTO my_reserved_id
>      FROM queue q
>      WHERE q.status = in_status
>      ORDER BY tcq_id ASC
>      LIMIT 1
>      FOR UPDATE;
>
>     /* we didn't find anything matching */
>     IF NOT FOUND THEN
>         RETURN 0;
>     END IF;
>
>     /* change the status to the new status */
>     UPDATE queue SET
>       status = in_new_status,
>       ts_start = NOW(),
>       ts_end = NULL,
>       hostname = COALESCE(in_hostname, hostname)
>     WHERE tcq_id = my_reserved_id;
>
>     /* send back our reserved ID */
>     RETURN my_reserved_id;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
> INVOKER;
> ----------------------------------------------------------------------
>
> When my server is under severe load, however, this function begins
> to take a long time to execute and I begin to suspect that the FOR
> UPDATE lock might be locking the whole table and not just the row.
>
> How do I know if this is the case, how can I optimize this
> procedure, and how should I be doing this differently?  I'm
> guessing stacks and queues would be common patterns handled in the
> PostgreSQL community.
>
> Thoughts?

SELECT ... FOR UPDATE should only be locking the rows returned by
your the select statement, in this case the one row.  You can check
what locks exist on a table (and their type) with the pg_locks system
view.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com