none
Using statement for connection for multiple server single transaction RRS feed

  • Question

  • Dear All,

                 I have a snippet of my code below. The problem here is that I want to further extend to make sure that at the same time my codes are execute on 2 different remote server. So the problem is I want to make sure all are under one transaction that means is either my remote faill then my local n both my remote server should be updated and vice versa. Any idea please ?

     

    using (MySqlConnection connection = new
    MySqlConnection(connectionString))
      {
    connection.Open();
    MySqlTransaction txn = connection.BeginTransaction();

    MySqlCommand cmd = new MySqlCommand(updateQuery, connection);
    cmd.ExecuteNonQuery();
    cmd.CommandText = insertQuery;
    cmd.ExecuteNonQuery();
    txn.Commit();
      }

    Friday, July 18, 2008 3:42 AM

Answers

  • From digging around a bit, it does not look like MySql supports distributed transactions.

     

    http://forums.mysql.com/read.php?38,212745,212745

     

    Note in Linux world, XA transactions are equivalent of MS DTC.

    Reason I know this is because I wrote the XA -> MSDTC translation code for MSFT JDBC driver.

     

    I would be difficult to work around this restriction I think.  The benefit of distributed transaction is you can do a 2 phase commit and thus have assurances that all entities can either commit or rollback.  Hopefully someone will implement distributed txns for MySql and you will be set.

    Monday, July 21, 2008 5:03 PM

All replies

  • You need to implement distributed transaction in this case. There are several ways to implement it in .NET 2.0

    1. You could use System.Transactions, which is simpliest way in .NET. Here are article that explains how it works

     

    http://msdn.microsoft.com/en-us/library/ms973865.aspx

     

    2. You could implement it using EnterpriseServices class and deploy component as COM+. Here are couple of links about it. Even though second link is for VB.NET it is 100% applicable to C# as well.

     

    http://msdn.microsoft.com/en-us/library/3x7357ez(VS.80).aspx

    http://support.microsoft.com/kb/315707

     

    Friday, July 18, 2008 9:43 AM
    Moderator
  • Dear VMazur,

                        I went through the articles but I find they cant solve my problem. Infact what I am doing now already is create 3 connection and 3 transaction. The accordingly I link them to the particular query which need to be updated. So I keep a variable so if any one of me query faily in any of my 3 server then the rest wont commint also. But strangely it works well but sometimes my local is update and my other 2 server is not update. So I dont know where is the problem is it at the commit or where ? By the way I am using mysql. Thanks.

    Friday, July 18, 2008 4:33 PM
  • What makes you believe articles cannot solve your problem? Distributed transactions work different way from local one your are using. If you need to commit against multiple servers with multiple connections, you cannot use local transactions you are using right now, because they will not participate in one global transaction. Instead you need to create serviced transactional component or use System.Transactions namespace that basically does similar staff for you and code will be managed under Distributed Transaction Coordinator (DTC). Only in a case if you implement distributed transation it will be commited as one batch of data even if you have multiple database servers.

    Friday, July 18, 2008 10:01 PM
    Moderator
  • Dear Vmazur,

                        Thanks for clearing my doubts and mistakes. So that means I cant use the variable method to run distributed transaction. The problem here is that I am using Mysql as my database so how must I go about with this ? Thanks once again.

     

    Sunday, July 20, 2008 3:01 PM
  • I am not sure about if MySQL will participate in distributed transaction. The main condition for it is that database server must participate in DTC. SQL Server has built-in support for it, Sybase has separate installer that enables this functionality. I am not sure about MySQL.

    What is definition of variable method in your case?

    Monday, July 21, 2008 9:46 AM
    Moderator
  • Dear VMazur,

                        My code for the application is quite complex. So I have tried to cut it short as much as possible and below is a just a snippet of my code and I will explain to you what I am doing exactly. First I check is my local connection is up and running if not immediately I set rollbackBoolean =1 so all of it will rollback. Then next I check if my remote 1 is up and running and if it is then I set leave my backUpCentralCopy = 0 else I set to 1 and the same goes for my remote 2. So then below I have shown you how I am inserting a query first to my local server followed by remote 1 and followed by remote 2. So incase any mistake or error all the queries will not be committed as I have stated in the if (rollbackBoolean == 1). Thus in short this is how my application is working. Most of the times is working fine only for certain cases is not working well very very rare cases. So do you think there is mistake in my coding ? Thanks for your help.

     

    //control variable definition

    int backUpCentralCopy = 0, backUpCentral = 0;

    int rollbackBoolean = 0;

     

    //local connection and transaction
                        MySqlTransaction transactionLocal = null;
                        MySqlConnection connectionLocal = null;
                        transactionConnectionLocal1 callTransactionConnectionLocal1 = null;

                        try
                        {
                            callTransactionConnectionLocal1 = new transactionConnectionLocal1();
                            connectionLocal = callTransactionConnectionLocal1.localConnection1;
                            connectionLocal.Open();
                            transactionLocal = connectionLocal.BeginTransaction();
                        }
                        catch (MySql.Data.MySqlClient.MySqlException ex)
                        {
                            rollbackBoolean = 1;
                            MessageBox.Show("Error From Database Connection (Local Server Is Down) " + ex.Message);
                        }
                        catch (System.Net.Sockets.SocketException ex)
                        {
                            rollbackBoolean = 1;
                            MessageBox.Show("Error Sockets From Database Connection (Local Server Is Down) " + ex.Message);
                        }

     

    //remote connection and transaction 1
                       globalConnectionLocal1 myConnect1 = null;
                        MySqlDataReader myReader1 = null;
                        MySqlTransaction transactionCentralCopy = null;
                        MySqlConnection connectionCentralCopy = null;
                        transactionConnectionCentralCopy1 callTransactionConnectionCentralCopy1 = null;
                        try
                        {
                              callTransactionConnectionCentralCopy1 = new transactionConnectionCentralCopy1();
                              connectionCentralCopy = callTransactionConnectionCentralCopy1.centralCopyConnection1;
                              connectionCentralCopy.Open();
                              transactionCentralCopy = connectionCentralCopy.BeginTransaction();
                              
                        }
                        catch (MySql.Data.MySqlClient.MySqlException ex)
                        {
                            backUpCentralCopy = 1;
                            MessageBox.Show("Error From Database Checking (Central C Server Is Down) " + ex.Message);
                        }
                        catch (System.Net.Sockets.SocketException ex)
                        {
                            backUpCentralCopy = 1;
                            MessageBox.Show("Error Sockets From Checking Connection (Central C Server Is Down) " + ex.Message);
                        }
                        

     

    //remote connection and transaction 2

                        MySqlDataReader myReader2 = null;
                        MySqlTransaction transactionCentral = null;
                        MySqlConnection connectionCentral = null;
                        transactionConnectionCentral1 callTransactionConnectionCentral1 = null;
                        try
                        {
                           
                                    callTransactionConnectionCentral1 = new transactionConnectionCentral1();
                                    connectionCentral = callTransactionConnectionCentral1.centralConnection1;
                                    connectionCentral.Open();
                                    transactionCentral = connectionCentral.BeginTransaction();
                              
                           
                        }
                        catch (MySql.Data.MySqlClient.MySqlException ex)
                        {
                            backUpCentral = 1;
                            MessageBox.Show("Error From Database Checking (Central Server Is Down) " + ex.Message);
                        }
                        catch (System.Net.Sockets.SocketException ex)
                        {
                            backUpCentral = 1;
                            MessageBox.Show("Error Sockets From Checking Connection (CentralServer Is Down) " + ex.Message);
                        }
                       

     

                        String myInsertQuery1 = "Insert into tblReceipt " +
                                                "Set receiptID=" + receiptID + ", " +
                                                "totalCostAmount=" + totalCostPrice + ", " +
                                                "totalSellingAmount=" + totalSellingPrice + ", " +
                                                "totalProfitAmount=" + totalProfit ;
                                              
                                            

                        // inserting local server

                        MySqlCommand myCommandLocal3 = new MySqlCommand(myInsertQuery1);
                        myCommandLocal3.Connection = connectionLocal;
                        myCommandLocal3.Transaction = transactionLocal;
                        try
                        {
                            myCommandLocal3.ExecuteNonQuery();
                        }
                        catch (MySql.Data.MySqlClient.MySqlException ex)
                        {
                            rollbackBoolean = 1;
                            MessageBox.Show("Error From myCommandLocal3 myInsertQuery1" + ex.Message);
                        }
                        catch (System.Net.Sockets.SocketException ex)
                        {
                            rollbackBoolean = 1;
                            MessageBox.Show("Error Sockets From myCommandLocal3 myInsertQuery1" + ex.Message);
                        }
                        finally
                        {
                            myCommandLocal3.Dispose();
                        }

                      

                       //check if my remote server 1 is up and running then only update it directly

                        if (backUpCentral == 0)
                        {
                            MySqlCommand myCommandCentral3 = new MySqlCommand(myInsertQuery1);

                            try
                            {
                                myCommandCentral3.Connection = connectionCentral;
                                myCommandCentral3.Transaction = transactionCentral;
                                myCommandCentral3.ExecuteNonQuery();
                            }
                            catch (MySql.Data.MySqlClient.MySqlException ex)
                            {
                                rollbackBoolean = 1;
                                MessageBox.Show("Error From myCommandCentral3 myInsertQuery1" + ex.Message);
                            }
                            catch (System.Net.Sockets.SocketException ex)
                            {
                                rollbackBoolean = 1;
                                MessageBox.Show("Error Sockets From myCommandCentral3 myInsertQuery1" + ex.Message);
                            }
                            finally
                            {
                                myCommandCentral3.Dispose();
                            }
                        }
                        else
                       

                        if (backUpCentralCopy == 0)
                        {
                            MySqlCommand myCommandCentralCopy3 = new MySqlCommand(myInsertQuery1);

                            try
                            {
                                myCommandCentralCopy3.Connection = connectionCentralCopy;
                                myCommandCentralCopy3.Transaction = transactionCentralCopy;
                                myCommandCentralCopy3.ExecuteNonQuery();
                            }
                            catch (MySql.Data.MySqlClient.MySqlException ex)
                            {
                                rollbackBoolean = 1;
                                MessageBox.Show("Error From myCommandCentralCopy3 myInsertQuery1" + ex.Message);
                            }
                            catch (System.Net.Sockets.SocketException ex)
                            {
                                rollbackBoolean = 1;
                                MessageBox.Show("Error Sockets From myCommandCentralCopy3 myInsertQuery1" + ex.Message);
                            }
                            finally
                            {
                                myCommandCentralCopy3.Dispose();
                            }
                        }
                       

                       //check if incase any of my query fail then do dont do any of the commit.

                       if (rollbackBoolean == 1)
                        {
                            transactionLocal.Rollback();
                            if (backUpCentral == 0) // to check if in the first place my remote 1 is up and running
                            {
                                transactionCentral.Rollback();
                            }
                            if (backUpCentralCopy == 0) // to check if in the first place my remote2 is up and running

                            {
                                transactionCentralCopy.Rollback();
                            }
                        }
                        else
                        {
                            try
                            {
                                transactionLocal.Commit();
                                if (backUpCentral == 0) // to check if in the first place my remote 1 is up and running
                                {
                                    transactionCentral.Commit();
                                }
                                if (backUpCentralCopy == 0)  // to check if in the first place my remote 2is up and running
                                {
                                    transactionCentralCopy.Commit();
                                }
                            }
                            catch (MySql.Data.MySqlClient.MySqlException ex)
                            {
                                try
                                {
                                    transactionLocal.Rollback();
                                    if (backUpCentral == 0)  // to check if in the first place my remote 1 is up and running
                                    {
                                        transactionCentral.Rollback();
                                    }
                                    if (backUpCentralCopy == 0) // to check if in the first place my remote 2 is up and running
                                    {
                                        transactionCentralCopy.Rollback();
                                    }
                                }
                                catch (MySqlException ex1)
                                {
                                    if (transactionLocal.Connection != null)
                                    {
                                        MessageBox.Show("An exception of type " + ex1.GetType() +
                                                          " was encountered while attempting to roll back the transaction.");
                                    }
                                }

                                MessageBox.Show("An exception of type " + ex.GetType() +
                                                " was encountered while inserting the data.");
                                MessageBox.Show("Neither record was written to database.");
                            }
                            finally
                            {
                                connectionLocal.Close();
                                if (backUpCentral == 0)
                                {
                                    connectionCentral.Close();
                                }
                                if (backUpCentralCopy == 0)
                                {
                                    connectionCentralCopy.Close();
                                }
                            }
                        }


     

     

    Monday, July 21, 2008 4:56 PM
  • From digging around a bit, it does not look like MySql supports distributed transactions.

     

    http://forums.mysql.com/read.php?38,212745,212745

     

    Note in Linux world, XA transactions are equivalent of MS DTC.

    Reason I know this is because I wrote the XA -> MSDTC translation code for MSFT JDBC driver.

     

    I would be difficult to work around this restriction I think.  The benefit of distributed transaction is you can do a 2 phase commit and thus have assurances that all entities can either commit or rollback.  Hopefully someone will implement distributed txns for MySql and you will be set.

    Monday, July 21, 2008 5:03 PM
  • Also note the MySql server supports XA transactions, so this is a client driver limitation.

     

    Monday, July 21, 2008 5:05 PM
  • Dear Matt,

                    Thank you very much for your kind information. Infact I read some where also they are saying that .net connector group for mysql is working hard towards this problem solution. Yes as what you said is right the mysql server itself support only the windows .net connector is not yet able to support XA in full. By the way I have also attached a snippet of my code currently to achieve the distributed transaction and it works fine only for very rare cases I find that either my remote 1 or remote 2 is not updated. Thanks.

     

    Monday, July 21, 2008 5:09 PM