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(at)myemma(dot)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