Re: select for update question

In response to A B :
> Hello there.
> 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.

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99