Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA50936 for ; Wed, 2 Jun 1999 21:25:40 -0400 (EDT) (envelope-from vadim@krs.ru) Received: from krs.ru (dune.krs.ru [195.161.16.38]) by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id JAA25413; Thu, 3 Jun 1999 09:24:57 +0800 (KRSS) Sender: root@sunpine.krs.ru Message-ID: <3755D968.D3955172@krs.ru> Date: Thu, 03 Jun 1999 09:24:56 +0800 From: Vadim Mikheev Organization: OJSC Rostelecom (Krasnoyarsk) X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386) X-Accept-Language: ru, en MIME-Version: 1.0 To: Mark Wright CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Are PL/pgSQL calls atomic? References: <001f01bead45$d72f9100$c62812ac@markw_compaq> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Mark Wright wrote: > > If I call a PL/pgSQL function, and another client makes the same call, does > the second client have to wait for the first to complete execution before it > begins? If not, is there some sort of mechanism I can use to prevent more > than one call to a function from happening at the same time (something like > a mutex in Win32). > > I need to select a row and then mark it as unavailable for other clients to > use. The table looks like: > create table xyz (id serial, status char default 'N', ...); > > My function finds a row by doing: > select id into my_id_variable from xyz where id > = (select min(id) from xyz where status = 'N'); 6.5 allows you SELECT FOR UPDATE... > > and then marks that row as unavailable by setting status: > update xyz set status = 'Y' where id = my_id_variable; > > Obviously, if a second client calls this function before the UPDATE takes > place, it will pick up the same row as the first client, causing rending of > hair and gnashing of teeth. Vadim