Re: Does the block of code in a stored procedure execute

btober(at)seaworthysys(dot)com wrote:

>Thank you very much.
>
>Further clarification on two points, though, please.
>
>1) When I add the FOR UPDATE clause to the SELECT statement, do also have
>to add a COMMIT statement somewhere?
>
*no* Don't even think about it.:-)
You are running this from inside a trigger, right?
So the user executes a statement like

insert into foo values (bar);

If the user did begin before that, you are already in transaction, and
it will be committed when the user commits explicitly. If there was no
explicit begin, there is still an implicit transaction around your
insert statement (imagine that there is begin; before the insert, and
commit immediately after it).
So, either way, your trigger function is running inside a transaction,
that will be committed at the right time. You don't want to screw that
up by committing too early.

>2) I don't see how doing UPDATE first helps. What if the other user,
>calling the same function, happens to have their UPDATE statement execute
>between my UPDATE and SELECT statements? Then we again both get the same
>new "sequence" value, don't we?
>
When you UPDATE a row, it gets locked (just like when you do
select...for update), and stays locked until the end of the transaction.
So, once you UPDATE it, nobody else can until your transaction is finished.

I hope, it helps...

Dima