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

Erik Jones wrote:
> On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
>
>> ...to ensure that only one server is processing the queue item, so
>> inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
>>
>> 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? ...
>>
>> 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.
>

Is that correct? Documentation section 12.3.1. Table-Level Locks states
'The list below shows the available lock modes ...Remember that all of
these lock modes are table-level locks, even if the name contains the
word "row"'.

I wonder why bother with the SELECT statement at all. Why not just go
straight to the UPDATE statement with something like

UPDATE queue SET
status = in_new_status,
ts_start = NOW(),
ts_end = NULL,
hostname = COALESCE(in_hostname, hostname)
WHERE tcq_id = (SELECT tcq_id FROM queue q WHERE q.status =
in_status ORDER BY tcq_id ASC LIMIT 1);

He may need to trap an exception for the "not found" case, but what's
the big deal with that?

UPDATE statements acquire a ROW EXCLUSIVE on the table, which conflicts,
among other things, with ROW EXCLUSIVE, so it will block other UPDATE
statements initiated by other transactions.