Thread: select for update question
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?
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
>> 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?
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. ---------------------------------------------------------------