mssql and mysql transaction

[[gja]]

Hello There,
im just curious on how to do this, 1 transaction and two different server queries inside it..

ex:

BeginTransaction

'MSSQL Query
INSERT query

'MYSQL Query
INSERT query

CommitTransaction

the question is which transaction will im going to use?if i will going to use sqltransaction and mysql query failed on insert will be rolling back by not inserting the mssql also?..

thanks
glen

[jmcilhinney]

You have two choices:

1. Use both. Begin the two transactions, perform all your data access against both databases, commit or rollback both transactions. That still leaves you vulnerable if there's an issue with the second commit, which is unlikely but possible.

2. Use a single distributed transaction. Don't use a SqlTransaction or a MySqlTransaction. Wrap all your data access in a Using block with a TransactionScope object. That is the "proper" way to manipulate multiple data sources in an atomic manner. That said, I'm not sure that Connector/Net supports distributed transactions. You should give it a test run and see if it works with the latest version. If it doesn't, you may be able to add that support, although I don;t know exactly how. That would take some research.

[[gja]]

thank you for a very quick reply jmcilhinny.

its lighten my way.i dont know about single distributed transaction.glad you mention it.its a new learning to me..for now,i just take a research on this..ill get back to you later..


thank you,
glen