none
how do i avoid the block on using TransactionScope RRS feed

  • Question

  • i used the TransactionScope in a distributed transaction,but when i was connecting to the second database,it stoke,i just cant figure it out why, it blocks in  connection2.Open(); in the following code,gjdwillupload is a LIST<T>

    my code is

                string[] connstr= new string[3];
                connstr[0] = "server =192.168.0.25;database=db1;uid=sa;pwd=";
                connstr[1] = "server =192.168.0.25;database=db2;uid=sa;pwd=";
                connstr[2] = "server =192.168.0.20;database=db3;uid=sa;pwd=";

       TransactionOptions tpt = new TransactionOptions();

                tpt.IsolationLevel  = System.Transactions.IsolationLevel.ReadCommitted;

                tpt.Timeout = new TimeSpan(0, 3, 0);

                try
                {
                    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, tpt))
                    {
                        using (SqlConnection connection1 = new SqlConnection(connstr[0]))
                        {
                            try
                            {
                                connection1.Open();

                                if (insertthegjd(connection1, 1, gjdwillupload, gjdmaindc)<0)//  gjdwillupload is a LIST<T>
                                    return 10;


                                using (SqlConnection connection2 = new SqlConnection(connstr[1]))
                                {
                                    try
                                    {

                                        connection2.Open(); //  it block! 

                                        if (insertthegjd(connection2, 2, gjdwillupload, gjdmaindc) < 0)
                                            return 10;

                                        using (SqlConnection connection3 = new SqlConnection(connstr[2]))
                                        {
                                            try
                                            {
                                                connection3.Open();

                                                if (insertthegjd(connection3, 3, gjdwillupload, gjdmaindc) < 0)
                                                    return 10;


                                            }
                                            catch (Exception ex)
                                            {
                                                UploadthegjdFault upgjdf = new UploadthegjdFault();
                                                upgjdf.Operation = "when insert the records";
                                                upgjdf.ProblemType = "occur the exception";
                                                string expmessag = ex.ToString();
                                                if (expmessag.Length > 900)
                                                    expmessag = ex.ToString().Substring(0, 900);
                                                logtheexp(ip, myusername, upgjdf.Operation + upgjdf.ProblemType + expmessag);
                                                throw new FaultException<UploadthegjdFault>(upgjdf);
                                            }

                                        }


                                    }




                                    catch (Exception ex)
                                    {
                                        UploadthegjdFault upgjdf = new UploadthegjdFault();
                                        upgjdf.Operation = "when insert the records";
                                        upgjdf.ProblemType = "occur the exception ";
                                        string expmessag = ex.ToString();
                                        if (expmessag.Length > 900)
                                            expmessag = ex.ToString().Substring(0, 900);
                                        logtheexp(ip, myusername, upgjdf.Operation + upgjdf.ProblemType + expmessag);
                                        throw new FaultException<UploadthegjdFault>(upgjdf);
                                    }


                                }

                            }
                            catch (Exception ex)
                            {
                                UploadthegjdFault upgjdf = new UploadthegjdFault();
                                upgjdf.Operation = "when insert the records";
                                upgjdf.ProblemType = "insert exception happen";
                                string expmessag = ex.ToString();
                                if (expmessag.Length > 900)
                                    expmessag = ex.ToString().Substring(0, 900);
                                logtheexp(ip, myusername, upgjdf.Operation + upgjdf.ProblemType + expmessag);
                                throw new FaultException<UploadthegjdFault>(upgjdf);
                            }
                           



                        }




                       





                        // The Complete method commits the transaction. If an exception has been thrown,
                        // Complete is not  called and the transaction is rolled back.
                        scope.Complete();

                        return 1;
                    }
                }
                catch (TransactionAbortedException ex)
                {
                    UploadthegjdFault upgjdf = new UploadthegjdFault();
                    upgjdf.Operation = "when the transaction is coming";
                    upgjdf.ProblemType = "insert exception";
                    string expmessag = ex.ToString();
                    if (expmessag.Length > 900)
                        expmessag = ex.ToString().Substring(0, 900);
                    logtheexp(ip, myusername, upgjdf.Operation + upgjdf.ProblemType + expmessag);
                    throw new FaultException<UploadthegjdFault>(upgjdf);
                }
                catch (ApplicationException ex)
                {
                    UploadthegjdFault upgjdf = new UploadthegjdFault();
                    upgjdf.Operation = "when insert the records ";
                    upgjdf.ProblemType = "ocur exception";
                    string expmessag = ex.ToString();
                    if (expmessag.Length > 900)
                        expmessag = ex.ToString().Substring(0, 900);
                    logtheexp(ip, myusername, upgjdf.Operation + upgjdf.ProblemType + expmessag);
                    throw new FaultException<UploadthegjdFault>(upgjdf);
                }

    //my function of insertthegjd code is following :

     public int insertthegjd(SqlConnection connection1, int thebelongto, Uploadthegjdsubver2List gjdwillupload, Uploadthegjdmaindcver2 gjdmaindc)
            {


                 string clinozy = gjdmaindc.Myclinozymain;
                string clinonormal = gjdmaindc.Myclinomain;
                string listnozy = gjdmaindc.Mylistnomain.Substring(0, 14) + "d" + clinozy;
                string listnorecipe = gjdmaindc.Mylistnomain.Substring(0, 13) + "rd" + clinonormal;
                string listnozynormal=gjdmaindc.Mylistnomain.Substring(0, 13) + "zd" + clinonormal;

                int dbtotalcount = (from d in gjdwillupload
                               where d.Mybelong == thebelongto
                                select d.Myprodnosub).Count();

               
                if (dbtotalcount > 0)
                {

                    try
                    {
                        var query = (from d in gjdwillupload
                                     where d.Mybelong == thebelongto
                                     select new { d.Mylistnosub, d.Myreason }).Distinct();



                        foreach (var q in query)
                        {
                            string sqlinsmain = @"insert into gjd_main (list_no,cli_no,local_time,remote_time,belong_to,now_status) values
                             (@mylistno,@myclino,@mylocaltime,@myremotetime,@mybelongto,@mynowstatus)";


                            SqlCommand cmdinsmain = connection1.CreateCommand();
                            cmdinsmain.CommandText = sqlinsmain;
                            cmdinsmain.Parameters.Clear();
                            cmdinsmain.Parameters.Add("@mylistno", SqlDbType.Char, 19);
                            cmdinsmain.Parameters.Add("@myclino", SqlDbType.Char, 4);
                            cmdinsmain.Parameters.Add("@mylocaltime", SqlDbType.DateTime);
                            cmdinsmain.Parameters.Add("@myremotetime", SqlDbType.DateTime);
                            cmdinsmain.Parameters.Add("@mybelongto", SqlDbType.Int);
                            cmdinsmain.Parameters.Add("@mynowstatus", SqlDbType.Int);
                            cmdinsmain.Parameters["@mylistno"].Value = q.Mylistnosub;
                            cmdinsmain.Parameters["@myclino"].Value = clinonormal;
                            cmdinsmain.Parameters["@mylocaltime"].Value = gjdmaindc.Mylocaltimemain;
                            DateTime dt = System.DateTime.Now;
                            cmdinsmain.Parameters["@myremotetime"].Value = dt;
                            cmdinsmain.Parameters["@mybelongto"].Value = 1;
                            cmdinsmain.Parameters["@mynowstatus"].Value = gjdmaindc.Mynowstatusmain;
                            cmdinsmain.ExecuteNonQuery();
                        }

                        var query2 = from d in gjdwillupload
                                     where d.Mybelong == thebelongto
                                     select new { d.Mylistnosub, d.Myprodnosub, d.Myneednumsub, d.Mystatusub, d.Mylesttimesub };



                        foreach (var q2 in query2)
                        {
                            if (q2.Myprodnosub != null)
                            {
                                string sqlinssub = @"insert into gjd_sub (list_no,prod_no,need_num,status,lest_time)
                                      values (@mylistnosub,@myprodno,@myneednum,@mystatus,@mylesttime)";
                                SqlCommand cmdinssub = connection1.CreateCommand();
                                cmdinssub.CommandText = sqlinssub;
                                cmdinssub.Parameters.Clear();
                                cmdinssub.Parameters.Add("@mylistnosub", SqlDbType.Char, 19);
                                cmdinssub.Parameters.Add("@myprodno", SqlDbType.Char, 8);
                                cmdinssub.Parameters.Add("@myneednum", SqlDbType.Decimal);
                                cmdinssub.Parameters.Add("@mystatus", SqlDbType.Int);
                                cmdinssub.Parameters.Add("@mylesttime", SqlDbType.DateTime);
                                cmdinssub.Parameters["@mylistnosub"].Value = q2.Mylistnosub;
                                cmdinssub.Parameters["@myprodno"].Value = q2.Myprodnosub;
                                cmdinssub.Parameters["@myneednum"].Value = q2.Myneednumsub;
                                cmdinssub.Parameters["@mystatus"].Value = q2.Mystatusub;
                                cmdinssub.Parameters["@mylesttime"].Value = q2.Mylesttimesub;
                                cmdinssub.ExecuteNonQuery();
                            }
                        }

                        return 1;
                    }
                    catch
                    {


                        throw;
                      
                    }

                }

                return 0;

            }

    many thanks

    best regards



    • Edited by ken yup Thursday, August 1, 2013 11:59 PM
    Thursday, August 1, 2013 4:15 AM

Answers

  • finally i resolve this issue! i would like to share ,the fault is not in my codes,but all because  the firewall of windows,on win 2003 ,you must let the program MSDTC.EXE  which is in WIDNOWS\SYSTEM32\ in Exceptions tab on win 2003, in other hand on windows 2008  On the Exceptions tab, select the Distributed Transaction Coordinator check box.
    • Marked as answer by ken yup Saturday, August 3, 2013 12:55 AM
    Saturday, August 3, 2013 12:55 AM

All replies

  • Hello ken yup,

    Thank you for posting in MSDN Forum.

    From your description, I notice the issue you are experiencing is that when your program opens connection2 and it blocks.

    With the code provided by you,I made a test,however,it does not appear what you mentioned.

    Following is the sample code:

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Transactions;
    namespace ADONET
    {
        class OPTransactionScopCodeTest
        {
            internal void Show()
            {
                string[] connstr = new string[3];
                connstr[0] = "Data Source=(localdb)\\v11.0;Initial Catalog=DB1;Integrated Security=True";
                connstr[1] = "Data Source=(localdb)\\v11.0;Initial Catalog=DB2;Integrated Security=True";
                connstr[2] = "Data Source=np:\\\\.\\pipe\\LOCALDB#AEA83A68\\tsql\\query;Initial Catalog=TestDataBase;Integrated Security=True";
                TransactionOptions tpt = new TransactionOptions();
                tpt.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
                tpt.Timeout = new TimeSpan(0, 3, 0);
                try
                {
                    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, tpt))
                    {
                        using (SqlConnection connection1 = new SqlConnection(connstr[0]))
                        {
                            try
                            {
                                connection1.Open();
                                using (SqlConnection connection2 = new SqlConnection(connstr[1]))
                                {
                                    try
                                    {
                                        connection2.Open(); //  it block!  
                                        using (SqlConnection connection3 = new SqlConnection(connstr[2]))
                                        {
                                            try
                                            {
                                                connection3.Open();
                                            }
                                            catch (Exception ex)
                                            {
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                            }
                        }
                        scope.Complete();
                    }
                }
                catch (TransactionAbortedException ex)
                {
                }
                catch (ApplicationException ex)
                {
                }
            }
        }
    }

    So would please provide the error message where it blocks.

    With these message I think we will give you a help more better.

    I look forward to hearing from you.

    Best Regards.

    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 2, 2013 6:25 AM
    Moderator
  • thank you for replying  my thread,i apperciate it.

    it stuck in connection2.Open(); it just stuck,after a while (about 3-5 minutes) the exception message occur 

    :-        exception    {"Communication with the underlying transaction manager has failed."}    System.Exception {System.Transactions.TransactionManagerCommunicationException}

    +        InnerException    {"The MSDTC transaction manager was unable to pull the transaction from the source transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers. (Exception from HRESULT: 0x8004D02B)"}    System.Exception {System.Runtime.InteropServices.COMException}

    but my MSDTC have configured & DTCping is successful,i followed these steps:

    To enable MSDTC on each Web server on Windows Server 2003

    1. Click Start, point to Programs, point to Administrative Tools, and then click Component Services.

    2. In the Component Services window, in the left pane, expand Component Services, expand Computers, right-click My Computer, and then click Properties.

    3. In the My Computer Properties dialog box, on the MSDTC tab, in the Transaction Configuration section, click Security Configuration.

    4. In the Security Configuration dialog box, select or verify that the following features are enabled:

      • In the Security Settings section, enable Network DTC Access, Allow Remote Clients, and Allow Remote Administration.

      • In the Transaction Manager Communication section, enable Allow Inbound and Allow Outbound, and select No Authentication Required.

      • Click Enable XA Transactions.

        Click OK.


    • Edited by ken yup Friday, August 2, 2013 4:10 PM
    Friday, August 2, 2013 4:04 PM
  • finally i resolve this issue! i would like to share ,the fault is not in my codes,but all because  the firewall of windows,on win 2003 ,you must let the program MSDTC.EXE  which is in WIDNOWS\SYSTEM32\ in Exceptions tab on win 2003, in other hand on windows 2008  On the Exceptions tab, select the Distributed Transaction Coordinator check box.
    • Marked as answer by ken yup Saturday, August 3, 2013 12:55 AM
    Saturday, August 3, 2013 12:55 AM
  • Hello ken yup,

    I am glad you hear you solve the problem and thanks for sharing your solution that I think it will also help others.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Saturday, August 3, 2013 3:27 AM
    Moderator
  • Dont metion it
    Saturday, August 3, 2013 12:05 PM