SELECT ... FOR UPDATE performance costs? alternatives?
От | D. Dante Lorenso |
---|---|
Тема | SELECT ... FOR UPDATE performance costs? alternatives? |
Дата | |
Msg-id | 46C34148.3040609@lorenso.com обсуждение исходный текст |
Ответы |
Re: SELECT ... FOR UPDATE performance costs? alternatives?
Re: SELECT ... FOR UPDATE performance costs? alternatives? |
Список | pgsql-general |
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? -- Dante
В списке pgsql-general по дате отправления: