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

On Aug 15, 2007, at 2:39 PM, btober(at)ct(dot)metrocast(dot)net wrote:

> 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"'.

You will notice that SELECT ... FOR UPDATE is not in that list. It's
covered in the next section on row level locks.
>
> 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.

That won't work because the update won't lock the row until the
select returns. So, if two process execute that at the same time
they will both execute the subquery and return the same result, the
first will update it and the second will then (redundantly) update it.

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