Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group