List:Internals« Previous MessageNext Message »
From:Shaukat Mahmood Ahmad Date:December 4 2012 3:41pm
Subject:Re: Can I use tables with different engines in single transaction?
View as plain text  
Thanks Zardosht for quick reply, I am using MySQL v 5.6.7-rc, Can you
explain two-phase commit a little bit more, furthermore  regarding
bugs  do you mean some concurrency related bugs, can I have details of
these bugs along with status? What about fixes you already have made,
in which future release these bugs are planned to be incorporated?

One more thing, you have used (by theory) in your reply,is it just
because of bugs you have referred in your reply or its?

Thanks again for your quick, detailed and very helpful reply.

Regards,
Shaukat Mahmood Ahmad

On Tue, Dec 4, 2012 at 8:30 PM, Zardosht Kasheff <zardosht@stripped> wrote:
> In theory, both engines will need to support two-phase commit in order for
> this to work in a crash safe manner. MySQL will notify both handlers to
> create transactions for the subset of work for each engine. So, in your
> example, assuming table1 and table2 use different transactional engines, the
> handler for table1 will have a transaction for the first statement, and the
> handler for table2 will have a transaction for the other two statements.
> Then, MySQL will call handlerton->prepare and handlerton->commit to commit
> the transaction.
>
> I say "in theory", because we have a transactional storage engine that
> supports XA, and we have run into many bugs in MySQL with the simple example
> that you suggest. To get this working, you will have to fix some bugs. If
> you get that far, email me and I can get you the fixes we have made.
>
> What version of MySQL are you doing this on?
>
>
> On Tue, Dec 4, 2012 at 10:15 AM, Shaukat Mahmood Ahmad <write@stripped> wrote:
>>
>> Dear All,
>>
>> I am planning to develop some mysql storage engines with transaction
>> support, however I have a confusion, i,e.  can I use tables from
>> different tables in a single transaction, if yes what will happen on
>> handler (concrete storage engine) level, will each involved engine
>> process its part as a septate part (in context of following example/
>> scenario will all three engine receive their part as septate
>> transaction?)? And then mysql will make decision (success or failure
>> of transaction)  from the  result of these individual / sub
>> transactions.
>>
>> I am unable to validate this scenario, because  I have only one
>> transactional storage engine (InnoDB).
>>
>> [Example Tables]
>>
>>
> ----------------------------------------------------------------------------------------
>> Table Name                              Engine
>>
>>
> ----------------------------------------------------------------------------------------
>> Table1                                      [InnoDB]
>> Table2                                      [MyTransactionalEngine1]
>> Table3                                      [MyTransactionalEngine2]
>>
>>
> ----------------------------------------------------------------------------------------
>>
>> [Example Transaction]
>>
>>
> ----------------------------------------------------------------------------------------
>> START TRANSACTION;
>> INSERT INTO Table1 VALUES (1, 'SMA');
>> INSERT INTO Table2 VALUES ('url', 'www.sma.im');
>> INSERT INTO Table2 VALUES (1001, 'sample entry');
>> COMMIT;
>>
>>
> ----------------------------------------------------------------------------------------
>>
>> Regards,
>> Shaukat Mahmood Ahmad
>>
>> --
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:    http://lists.mysql.com/internals
>>
>
Thread
Can I use tables with different engines in single transaction?Shaukat Mahmood Ahmad4 Dec
Re: Can I use tables with different engines in single transaction?Shaukat Mahmood Ahmad4 Dec
  • Re: Can I use tables with different engines in single transaction?Shaukat Mahmood Ahmad5 Dec
    • Re: Can I use tables with different engines in single transaction?Kristian Nielsen5 Dec