* Zardosht Kasheff <zardosht@stripped> [10/07/03 06:33]:
> So are stored procedures basically meant to act like a single
> statement? Meaning, if a user does;
>
> begin;
> insert into foo values (1);
> some stored procedure;
> commit;
>
> the stored procedure is meant to have the same properties as the
> insert? The stored procedure does not cause an implicit commit? And it
> is for this reason that stored procedures do not call commits for
> individual statements within them, because they are meant to act as
> one statement?
MySQL does commit individual statements of a stored procedure.
There is a notion of sub-statement in the server: any
sub-statement is part of the "statement" transaction of the
enclosing top-level statement.
As an example, if you do CALL p1(), each individual statement
of p1() is not a sub-statement of any other statement (CALL does
not introduce a "sub-statement").
If you do SELECT f1(), all statements in f1() are sub-statements
of SELECT. In the same manner if f1() calls p1(), all statements
of p1() become sub-statements of SELECT.
Indeed, committing or rolling back inside individual
sub-statements does not make sense -- a sub-statement may be
invoked many times per statement.
What does make sense, however, is acquiring a savepoint, or
starting a nested transaction. MySQL doesn't do that yet, however.
--
