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

Re: select for update question

From: A B <gentosaker(at)gmail(dot)com>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: select for update question
Date: 2010-01-13 14:55:48
Message-ID: dbbf25901001130655t9beeaa8ia5bd836426fc0e2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
>> 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?

In response to

Responses

pgsql-novice by date

Next:From: Daniel StaalDate: 2010-01-13 18:11:03
Subject: Re: select for update question
Previous:From: A. KretschmerDate: 2010-01-13 06:05:46
Subject: Re: select for update question

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