List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:July 3 2010 2:07am
Subject:Re: handler interface question: transactions and "create function"
View as plain text  
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 <mydb08@stripped> 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=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