Message-ID: <65027.66.212.203.144.1059701298.squirrel@$HOSTNAME>
Date: Thu, 31 Jul 2003 21:28:18 -0400 (EDT)
Subject: Re: Does a the block of code within a stored procedure constitute a
	transaction?
From: <btober@seaworthysys.com>
To: <rbt@rbt.ca>
In-Reply-To: <1059698237.33557.8.camel@jester>
References: <64821.216.238.112.88.1059509762.squirrel@$HOSTNAME>
	<1059698237.33557.8.camel@jester>
Importance: Normal
Cc: <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

>> What I need to know is whether or not this is multi-user safe, i.e.,
>> will the block of code in the procedure execute as a transaction so
>> that if more than one clerk creates an expense report for the same
>> employee simultaneously is it possible or impossible that value of
>> the
>> employee.expense_report_seq gets updated by the second clerk between
>> the SELECT and UPDATE statements invoked by the first clerk?
>>
>> And as a follow-up, should I add the FOR UPDATE clause to the SELECT
>> statement?
>
> SELECT .. FOR UPDATE would be appropriate.
>
> However, you could also do an update + 1 first and the select second to
> find what you changed the value to.


Yes, and I like the fact that the UPDATE first approach eliminates a
local variable declaration as well!

~Berend Tober