none
TransactionScope, Aborting transaction and SqlConnection.Open timeout RRS feed

  • Question

  • Hello!

    I use MSSQL2005 SP3, .NET 3.5 SP1 on Windows 2003 SP2 machine. The task is to run a distributed transaction with two connections with the same connection string inside, abort it by exception and then open а new connection with the same connection string outside the transaction. SqlConnection.Open for the non-transactional connection results in exception "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". So I can't open new connection. I use a connection pool with 3 connection maximum.
    When I clear transaction pool, I get other transactions aborted. So that is not the case.
    When thansaction is aborted completely (I can see it in Component Services span-in), the connection can be opened without a problem. Can you please tell me what is wrong? May be I could wait somehow till distributed transaction is aborted completely?
    Tuesday, June 30, 2009 3:48 PM

All replies

  • Hi,

    I'm not able to reproduce the issue you've described. Would you be able to post a code sample which reproduces the issue?

    Thanks,
    Jared

    Postings are provided "As Is" with no warranties and confer no rights.
    Tuesday, June 30, 2009 9:24 PM
  • I get it again and again. I thought "Transaction Binding=Explicit Unbind" could help me, but it didn't. It seems like connection pool returns a connection with SPID that is running rollback operation on SQL Server, so I get this error.

    I can tell now just about me test application, not about production code. The case is a long transaction. In the production code it continues about 1 hour and then rolls back, but I can send to you much more "shorter" sample.

    Please create a test database and run the script below. It fills one of the tables with 8 million records. No indexes, just two hash tables with one integer column.

    CREATE TABLE [dbo].[a](
        [id] [int] NOT NULL
    )

    CREATE TABLE [dbo].[b](
        [id] [int] NOT NULL
    )

    -- Creating 15625 records
    DECLARE @c int
    SET @c = 1
    WHILE (@c <= 15625)
    BEGIN
        INSERT INTO a(id) values (@c)
        SET @c = @c + 1
    END

    -- Creating 8 million records
    SET @c = 0
    WHILE (@c < 9)
    BEGIN
        INSERT INTO a (id)
        SELECT id + (SELECT COUNT(*) FROM a) FROM a
        SET @c = @c + 1
    END

    So one table is empty and another is filled.

    Please compile the test code:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Transactions;
    using System.Data.SqlClient;

    namespace TestTx2
    {
        class Program
        {
            public const string connStr =
                "Server={YOUR SERVER}; Database={YOUR DATABASE}; Integrated Security=true; Enlist=true; Application Name=NORMA Application Server; Pooling=true; Min Pool Size=1; Max Pool Size=5; Connection reset=true; MultipleActiveResultSets=true;Transaction Binding=Explicit Unbind";
            static void Main(string[] args)
            {
                // Do transactional work and rollback a transaction
                try
                {
                    TransactionWork();
                }
                catch (Exception e)
                {
                    // Here I run away from transaction context (TransactionScope).
                    // When you get the message, please look at Component Services span-in.
                    // Transaction is aborting yet.
                    // I would like to wait for its complete abortion. It would be ok.
                    Console.WriteLine("Transaction exception raised: " + e.ToString());
                }
               
                // Do non-transactional work
                try
                {
                    Test2();
                }
                catch (Exception e)
                {
                    // Here I get an error message. As you can see, this is not a full-pool error.
                    // I can't open connection with SPID that is rolling back yet.
                    Console.WriteLine("Non-transaction exception raised: " + e.ToString());
                }
            }
            public static void TransactionWork()
            {
                TransactionOptions to = new TransactionOptions();
                to.IsolationLevel = IsolationLevel.Serializable;
                to.Timeout = TimeSpan.FromHours(1);
                using (TransactionScope ts = new TransactionScope(
                    TransactionScopeOption.Required, to))
                {
                    Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(Current_TransactionCompleted);
                    Test();
                    ts.Complete();
                }
            }

            public static void Test()
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("DELETE FROM b", conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                }
                Console.WriteLine("Deletion complete");

                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO b (id) select id from a", conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    Console.WriteLine("Insertion complete");
                    throw new Exception();
                }
            }


            static void Current_TransactionCompleted(object sender, TransactionEventArgs e)
            {
                // Here the transaction is beginning rollback. Look at Component Services snap-in
                Console.WriteLine("TransactionCompleted event raised with status: " +
                    e.Transaction.TransactionInformation.Status.ToString());
            }
           
            public static void Test2()
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    // Here I get the error
                    // The error appears in System.Transactions and ComponentServices transactions.
                    // So I think this is .NET MSSQL provider issue.
                    // I would be glad if I could wait the aborting completion of transaction
                    // before I could run away from TransactionScope.
                    conn.Open();
                }
            }
        }
    }

    Please don't forget to edit connStr and add a reference to System.Transactions. Open Component Services span-in and MSSQL Activity Monitor so you can see transaction processing. Please use MSSQL2005, because MSSQL2008, as I can see, doesn't promote local transaction to distributed when two connections has the same connection string. Thank you.
    Wednesday, July 1, 2009 7:45 AM
  • Thanks for providing such nice repro code! I can reproduce your problem on Sql 2005. I'll get back to you if I can figure anything out about this :)

    You're right about the transaction not being promoted on Sql 2008 due to improvements to lightweight promotable transactions.
    Postings are provided "As Is" with no warranties and confer no rights.
    Wednesday, July 8, 2009 8:56 PM
  • We haven't fully investigated yet, but we think this might be a bug in the DbConnection pooling code. We plan to look into this further, but if it is a bug in our code then this would be a high risk change so it is unlikely that it will be fixed in .NET 4.0 this late in the development cycle.

    For the moment you have a few workarounds. You could try disabling connection pooling, but unfortunately that will probably have some performance penalty. If you have some way of knowing in advance whether or not a SqlConnection will execute a large command that is likely to be rolled back, then you could disable connection pooling just for that connection.

    Another alternative is that if you just need a transaction on one Sql Server (i.e. you don't need a distributed transaction) you could try using DbTransaction instead which shouldn't have this problem.


    Postings are provided "As Is" with no warranties and confer no rights.
    Monday, July 20, 2009 12:44 AM
  • Hello, Jared!

    I had a rest time and was out. Now I'm here.

    The bad thing is that I can't use DbTransaction, because I have to support MSSQL2000.

    My current solution is:
    Connection Lifitime = 10
    Min Pool Size = 0
    So, with connection timeout of 15 seconds I get a stable result.

    I shall use another connection without pooling as you told, if my solution will cause errors. So thank you for advice!

    It would be nice, if you could fix this problem in future. Even if it would be later than 4.0 release.
    Tuesday, July 21, 2009 11:25 AM
  • My collegues found another solution: all connections should be closed after transaction abortion. It works completely.

    <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:204; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:2.0cm 42.5pt 2.0cm 3.0cm; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

            public static void TransactionWork()

            {

                using (TransactionScope ts = new TransactionScope (

                    TransactionScopeOption .Required, to))

                {

                    try

                    {

                        Transaction .Current.TransactionCompleted += new TransactionCompletedEventHandler (Current_TransactionCompleted);

                        Test();

                        ts.Complete();

                    }

                    catch (Exception e)

                    {

                        Transaction .Current.Rollback(e);

                        ts.Dispose();

                        conn1.Close();

                        conn2.Close();

                        throw ;

                    }

                }

            }


    I think it would be helpful.
    Wednesday, July 22, 2009 7:04 AM