List:Internals« Previous MessageNext Message »
From:Konstantin Osipov Date:July 3 2010 7:18am
Subject:Re: handler interface question: transactions and "create function"
View as plain text  
* 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.

-- 
Thread
handler interface question: transactions and "create function"Zardosht Kasheff3 Jul
  • Re: handler interface question: transactions and "create function"Venu Kalyan3 Jul
    • Re: handler interface question: transactions and "create function"Zardosht Kasheff3 Jul
      • Re: handler interface question: transactions and "create function"Konstantin Osipov3 Jul