From: Shaukat Mahmood Ahmad Date: December 5 2012 10:54am Subject: Re: Can I use tables with different engines in single transaction? List-Archive: http://lists.mysql.com/internals/38649 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 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 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 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 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 >>> >>