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