Re: Can I use tables with different engines in single transaction?

Is there any mysql storage engine other than InnoDB (preferably open
source) supporting XA transactions?

Regards,
Shaukat Mahmood Ahmad

On Tue, Dec 4, 2012 at 8:41 PM, Shaukat Mahmood Ahmad <[email protected]> wrote:
> 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 <[email protected]> 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 <[email protected]> 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
>>>
>>

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals