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

Re: select for update question

From: "Daniel Staal" <DStaal(at)usa(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: select for update question
Date: 2010-01-13 18:11:03
Message-ID: 35e1764dd57bfb435f81184ffd39ea37.squirrel@www.magehandbook.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.
---------------------------------------------------------------


In response to

pgsql-novice by date

Next:From: Lonni J FriedmanDate: 2010-01-14 19:49:01
Subject: \dt is listing tables from all databases
Previous:From: A BDate: 2010-01-13 14:55:48
Subject: Re: select for update question

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