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
The behavior when handling functions is storage engine dependent...
You should probably check this section of the manual:
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:
On Fri, Jul 2, 2010 at 6:27 PM, Zardosht Kasheff <zardosht@stripped> wrote:
> I have seen some curious behavior with storage engines, and I was
> wondering what the motivation is.
> Suppose autocommit=0;
> 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
> insert into t1 values (1);
> insert into t1 values (2);
> return 1;
> 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?
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1