Two phase commit/Distributed Transaction

[newbie14]

Dear All,
I am using an application based on C# and mysql database. I have a requirement that I need to update my local and remote database on one single transaction. So I was currently using the .net connector but unfortunately it doesnt support distributed transaction. So I have to move to something else. Is there any one with experience of other connection method which support this maybe odbc,oledb.net etc. Hope to hear some good news from your past experience.

 

[AlexCuse]

I haven't worked a whole lot with MySQL but I have found the .net connector to be the most predictable way to access it. Could you control the transactions from your application?

For example, you could implement your own "DistributedQuery" class (probably not the best name but you get the idea). Give it command text, a dictionary for parameters, and whatever else you need for the query. Only difference is, query has 2 connections (or you give it two connection strings and it manages connections internally).

This is no where near complete, but a starting point (need to add reference to System.Transactions, and change from SqlClient classes to MySql classes):

using (SqlConnection conn1 = new SqlConnection())
{
	using (SqlConnection conn2 = new SqlConnection())
	{
		try
		{
			using (var scope = new System.Transactions.TransactionScope())
			{
				//when opening the connection, its' implicitly enlisted in the transaction scope
				conn1.Open();
				SqlCommand command1 = new SqlCommand(cmdText, conn1);
				//set parameters
				command1.ExecuteNonQuery();

				conn2.Open();
				SqlCommand command2 = new SqlCommand(cmdText, conn1);
				//set parameters
				command2.ExecuteNonQuery();

				//transaction commit/rolls back if an exception is thrown
				scope.Complete();
			}
		}
		catch (System.Transactions.TransactionAbortedException ex)
		{
			//handle aborted transaction
		}
		catch (Exception ex)
		{
			//handle other exceptions
		}
	}
}

Hope this helps,

Alex


[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[newbie14]

Dear Alex,
I know but I have gone to this bug site (

http://bugs.mysql.com/bug.php?id=37283)

of mysql and it is said that .net connector for mysql doesn't support distributed transaction. So I know your method above will work for sql server but I dont think so for mysql right? Any other method please?

 

[jmeckley]

if you are using a MySql .net driver then you probably have 2 choices.
1. manage the transactions manually
2. use OleDb to manage your mysql connection and use transaction scope.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[AlexCuse]

I got the impression from reading this:

http://bugs.mysql.com/bug.php?id=28709

that using the Transaction Scope would work, and that the normal transaction management (using BeginTransaction, Commit) would not. I could be mistaken though.

If you tried using the Transaction Scope before, then I suppose you will need to use OleDb to get it done. Sorry.

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[newbie14]

Dear Jason,
Actually I am confuse now what is oledb and odbc what is the difference? So when you say manage the transaction manually do u have any snippet of code (so you want me to use .net connector is it? ? Thank you.

 

[craigber]

ODBC is an OS vendor neutral, standardized way to access data. OLE-DB is a Microsoft developed, COM-based method of accessing data and works only on Windows. ADO (not ADO.NET) is a wrapper around OLE-DB.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[newbie14]

Dear Craigber,
So which will you suggest for my application of the two phase commit problem? Thank you.

 

[chiph]

Please don't start two threads on the same subject.

http://tek-tips.com/viewthread.cfm?qid=1543412&page=1#newpost

Chip H.



____________________________________________________________________
www.chipholland.com

 

[craigber]

I have no experience with MySQL, so I can't say which approach is better.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[jmeckley]

the "best" approach is one which balances extensibility, maintainability, readability and testability.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newbie14]

Dear Jason,
So in my scenario which is the best scenario will you suggest for me ? Thank you.

 

[newbie14]

Dear Craig,
So based on your other experience which is the best method you have used so far? Thank you.