Re: select for update question

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: select for update question
Date: 2010-01-13 06:05:46
Message-ID: 20100113060546.GA6897@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message A B 2010-01-13 14:55:48 Re: select for update question
Previous Message A B 2010-01-12 23:34:13 select for update question