Re: select for update question

On Wed, January 13, 2010 9:55 am, A B wrote:
>>> I'm looking into how to update a row in a table while protecting it
>>> from access by others.
>>>
>>> so far I've come up with this solution:
>>>
>>> create function dostuff() returns void as $$
>>> declare
>>>   tmp integer;
>>> begin
>>>   select id into tmp from tableX where id>305 limit 1 for update;
>>>   update tableX set some_field = some_value where id=tmp;
>>> end; $$ language plpgsql;
>>>
>>> will that guarantee that  the row I selected wil be updated within
>>> this function and no one else can sneak in between and update or
>>> delete the row?
>>>
>>>
>>> What would I use if I would write
>>>
>>> lock table   tableX  IN .... MODE  at the start of my function?
>>>
>>> Any particular benefit with either method?
>>
>> If you lock the table, the whole table is locked. The first method (with
>> select for update) locks only the one record you want to update.
>>
>> For real multi-user-access the first method are better.
>
> When will it stop beeing a better method? When you select a large
> enough percentage of the rows?

Where 'large enough' is some number greater than 95%, maybe.

In general, unless you are actually doing something on the _entire_ table,
you don't want to lock the table.  Save that for table
maintenance/revision.

After all, you are using a database, and one of the points of a database
is that more than one process can use it at a time.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------