List:Internals« Previous MessageNext Message »
From:Venu Kalyan Date:July 3 2010 1:49am
Subject:Re: handler interface question: transactions and "create function"
View as plain text  
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=51006

Thanks
Venu Anuganti

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
> begin
>  insert into t1 values (1);
>  insert into t1 values (2);
>  return 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:    http://lists.mysql.com/internals?unsub=1
>
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