From: Zardosht Kasheff Date: July 3 2010 2:07am Subject: Re: handler interface question: transactions and "create function" List-Archive: http://lists.mysql.com/internals/37986 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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? -Zardosht On Fri, Jul 2, 2010 at 9:49 PM, Venu Kalyan wrote: > If you look at sql/handler.cc and search for thd->in_sub_stmt; then > you can see the comments why MySQL skips to send to commit/rollback to > storage engine.. > > The behavior when handling functions is storage engine dependent... > You should probably check this section of the manual: > http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html > > Which states the following is not allowed in functions: > > Statements that perform explicit or implicit commit or rollback. > Support for these statements is not required by the SQL standard, > which states that each DBMS vendor may decide whether to allow them. > > and a open bug on InnoDB as well: > http://bugs.mysql.com/bug.php?id=3D51006 > > Thanks > Venu Anuganti > > On Fri, Jul 2, 2010 at 6:27 PM, Zardosht Kasheff wro= te: >> >> I have seen some curious behavior with storage engines, and I was >> wondering what the motivation is. >> >> Suppose autocommit=3D0; >> >> If I do the following: >> lock tables foo write; >> insert into foo values (1); >> insert into foo values (2); >> unlock tables; >> >> Our engine is transactional. In the above case, each insert statement >> gets a call to start_stmt registers a transaction by calling >> trans_register_ha, and at the end of each statement, >> handlerton->commit is called. >> >> However, if I have a function: >> create function bug13825_2() returns int >> begin >> =A0insert into t1 values (1); >> =A0insert into t1 values (2); >> =A0return 1; >> end| >> >> and if I execute this function, for each statement, start_stmt is >> called, but handlerton->commit is NOT called. >> >> Why is handlerton->commit not called in this case? Is this a bug? >> >> Thanks >> -Zardosht >> >> -- >> MySQL Internals Mailing List >> For list archives: http://lists.mysql.com/internals >> To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dmydb08@g= mail.com >> >