Bug in CommittableTransaction or SqlConnection when timing out against Sql Server 2005?

Question

Hi

 

I have found what I believe to be a disturbing bug in CommittableTransaction or SqlConnection. The error occurs if a transaction timeout against a Sql Server 2005 database. Commands issued before the timeout are rolled back, but commands issued between timeout and commit aren't. The result is that the second half of the transaction isn't rolled back and the commit fails with timeout or transaction aborted. The same bug has been reported in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=882767&SiteID=1 with a sample using TransactionScope.

 

I get this when I run my sample program (pauses 1 second after each insert):

 

Table recreated
Transaction started lasting 2 seconds
Server is 09.00.3054
Clr is 2.0.50727.1433
Row inserted
Row inserted
Row inserted
Row inserted
Row inserted
Committing... now we get the timeout (sort of)
The transaction has aborted.
Table contains 3 rows!!!

 

The CommittableTransaction class is using database transactions against Sql Server 2005. Theese transactions don't have a timeout associated, so the framework has handle it. The timeout occurs after 2 seconds and the framework sends a rollback to the database server. This ends the transaction on the daqtabase server. But my app isn't notified and the connection is now a plain non-transacted connection. The last 3 inserts are thus done outside of the transaction! This is quite surprising to me. Is it "by design" or is it a bug? If by design, what is the correct way to handle timeout?

 

My current workaround is to escalate the transactions to a distributed transaction by calling

TransactionInterop.GetTransmitterPropagationToken on the transaction.

 

 

Best regards Søren Mondrup

 

 

 

Code Snippet

//

// Save code in TimeoutBug.cs and compile like this:

// \> csc TimeoutBug.cs

//

// run like this:

// \> TimeoutBug.exe "Persist Security Info=False;Integrated Security=SSPI;database=MyDatabase;server=MyServer;"

//

using System;

using System.Data.SqlClient;

using System.Transactions;

using System.Threading;

class Demo

{

  static string connectionString;

  static void Main(string[] argv)

  {

    if( argv.Length < 1 )

    {

      Console.WriteLine( "Usage: TimeoutBug <connection string>" );

      return;

    }

    try

    {

      connectionString = argv[0];

      ReCreateTable();

      Console.WriteLine("Table recreated");

      try

      {

        using (CommittableTransaction tx = new CommittableTransaction(TimeSpan.FromSeconds(2)))

        {

          Console.WriteLine("Transaction started lasting 2 seconds");

          using (SqlConnection con = new SqlConnection(connectionString))

          {

            con.Open();

            Console.WriteLine("Server is {0}", con.ServerVersion);

            Console.WriteLine("Clr is {0}", Environment.Version);

            con.EnlistTransaction(tx);

            for (int i = 0; i < 5; i++)

            {

              using (SqlCommand cmd = con.CreateCommand())

              {

                cmd.CommandText = "insert into TXTEST values ( " + i + " )";

                cmd.ExecuteNonQuery();

                Console.WriteLine("Row inserted");

              }

              Thread.Sleep(TimeSpan.FromSeconds(1));

            }

            Console.WriteLine("Committing... now we get the timeout (sort of)");

            tx.Commit();

          }

        }

      }

      catch (Exception e)

      {

        Console.WriteLine(e.Message);

      }

      Console.WriteLine("Table contains {0} rows!!!", CountTable());

      DropTable();

    }

    catch (Exception e)

    {

      Console.WriteLine("Unexpected error:");

      Console.WriteLine(e.ToString());

    }

  }

 

  static void ReCreateTable()

  {

    try

    {

      DropTable();

    }

    catch (Exception) { }

    using (SqlConnection con = new SqlConnection(connectionString))

    {

      con.Open();

      using (SqlCommand cmd = new SqlCommand("create table TXTEST ( F1 int )", con))

        cmd.ExecuteNonQuery();

    }

  }

 

  static int CountTable()

  {

    using (SqlConnection con = new SqlConnection(connectionString))

    {

      con.Open();

      using (SqlCommand cmd = new SqlCommand("select count(*) from TXTEST", con))

        return (int) cmd.ExecuteScalar();

    }

  }

 

  static void DropTable()

  {

    using (SqlConnection con = new SqlConnection(connectionString))

    {

      con.Open();

      using (SqlCommand cmd = new SqlCommand("drop table TXTEST", con))

        cmd.ExecuteNonQuery();

    }

  }

}

 

 

Answer 1

I agree; this bug is incredibly disturbing, probably the most severe bug I've seen to date in the .NET Framework.  Especially so because it looks like you even have the latest .NET service packs installed...

 

Here is the workaround I've found helpful, by prefixing the commands with a @@trancount verification that a transaction is actually present.  If you are using stored procedures, then you can put this check inside your stored procs.

 

Code Snippet

cmd.CommandText = "if @@trancount = 0 begin raiserror('No transaction', 16, 1) return end; insert into TXTEST values ( " + i + " )";

 

 

 

Answer 2

Incredibly disturbing. Atomicity - the first of the ACID properties - is broken. My app can tolerate lots of errors. But this one leads to data inconsistency in the database!

 

I could implement your workaround. But it would be hard to enforce. I'm building a framework being used by a few hundred developers. The other thread suggests registering an eventhandler on TransactionCompleeted and forcing the main thread to abort, which seems a bit harsh. I will stick to my own workaround forcing distributed transactions until something better turn up. A bugfix would best.

 

 

Answer 3

I have given TransactionCompleeted a little more thought. I don't think it will guard against race conditions where sql is executed between timeout and the event being handled.

 

Another unsafe idea is that I check for timeout myself. This will also race if I don't use a fat time margin. It would be safe though if CommittableTransaction wouldn't try to enforce the timeout (i.e. didn't call Rollback).

 

So the two best options are still forcing Distributed transactions or checking transaction presense as you suggest.

 

There must be some clever people at MS with a better idea...

Answer 4

You are quite right. The TransactionCompleted handling is definitely not the best option, although it does currently work for us.

Does elevating the transaction really help in this case though? I don't see, why the connection would not go back to a non-transacted connection, even if you used the distributed transaction. And checking for the transaction using the trancount in sql is good, if you only use Sql. This does not guard other transacted stores though.

I really hope, the people at MS do come up with some clever ideas. We've been waiting for quite a while now and have not received any answer whatsoever (as you may have noticed in my thread). Let's hope this one yields better results :-)

Answer 5

Elevating the transaction does help because the database server reports an error if a timeout has occurred.

 

If I add TransactionInterop.GetTransmitterPropagationToken(tx);

and set the timeout to 3 seconds in my sample I get:

 

Table recreated
Transaction started lasting 3 seconds
Server is 09.00.3054
Clr is 2.0.50727.1433
Row inserted
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Table contains 0 rows!!!

 

The first database operation after the timeout fails as expected.

 

The workaround does have a catch though. Short transaction around 1 og 2 seconds seem to be to short to achieve any work. In the run above I only get one row inserted in 3 seconds. But integrity is more important than speed.

Using TransactionInterop.GetExportCookie(tx,TransactionInterop.GetWhereabouts()); seems a tiny bit faster.

Answer 6

Hi,

 

Did you look into the Spring.net Transaction support: http://www.springframework.net/doc-1.1-P3/reference/html/transaction.html

 

Personally I think that the abstractions are better.

 

Regards,

Charles

 

Answer 7

It seems that the spring framework doesn't try to implement timeout if the underlying transactions system doesn't support it. That's good! But I I'm not keen on switching to an unknown transaction wrapper.

Answer 8

I ran the repro and what I see in SQL Profiler is that the transaction rolls back but this does not raise an exception to your code.  The first couple records are inserted, then rolled back, then the loop continues and adds a few more records using normal auto-commit transactions.

 

Given you have a 2 second timeout, this is expected behaviour, i.e. it is no different than adding if (1 == i) tx.Rollback(); to the loop.

 

Rolling back the txn rolls back all work but does not invalidate the connection or throw an exception.

 

Hope this makes sense.

Answer 9

Yes it is no different than running the following tsql:

 

create table t1(f1 int)

go

begin tran

go

insert t1 values (0)

insert t1 values (1)

insert t1 values (2)

go

rollback tran -- <- this is triggered by the 2 second timeout

go

insert t1 values (3)

insert t1 values (4)

insert t1 values (5)

go

select count(*) from t1

go

 

I guess what you want is some "signal" when the transaction is rolled back, I'll see if there is any way to get this.

Answer 10

Yes this class is a bit hard to comprehend at first.  It gives you "Mac Gruber" transaction semantics, i.e. if you don't get your work done when the timer runs out you get "blown up" because you are not signalled that the txn aborted and your code keeps writing data in auto-commit mode.

 

But it does provide your application with a nice way to ensure your transactions are not held open too long, I think that was the purpose.  It gives you a hard upper limit of time.

 

The question is, when this time runs out, how do you know this and signal the code so it does not go on writing data in auto-commit mode.   Adding @@TRANCOUNT canary in the coal mine check sounds bad, lots of overhead but it might be the only thread safe way to do this since any checks in your code prior to ExecuteNonQuery could pass but then 1 millisecond later the txn is aborted and then you call ExecuteNonQuery.

Answer 11

Perhaps we could add a feature to the SqlClient class that says:  I assert I expect to be in manual commit mode and if I send anything to you (SQL Server) that results in auto-commit txn don't do it and raise an error.

 

We know internally when client side rolls back txn at least, but I think the only safe solution is have server validate the state prior to execution.  For example server side could abort txn, but I think in this case we are invalidated because we send in a transaction cookie and it won't match.

 

I'll talk to SqlClient main dev and figure out if there is something we can do here to make life easier.

 

Answer 12

I do think, something like your proposal would be helpful in most cases. But this would, again, only solve the problem for SqlServer-based commands in transactions. What happens if you use a transactive file system or other transactive resources? You would run into the same problems. I don't see the core problem with ADO.NET but in the Transactions namespace itself.

I do not know, how to solve the problem once and for all, since you would have to enforce abortion of any commands in a thread-safe manner. And I can imagine this will become a real hassle to implement. But this is IMHO the only real solution to the problem. Nonetheless, as I said, your proposal would be helpful for most of us, as I believe SqlServer is the most used transactive resource in current projects.

Answer 13

On that note: Why is the ambient transaction completely removed anyhow? Wouldn't it be easier to leave the ambient transaction in place until the final commit/rollback, but in a "timed out" state. This way, you at least wouldn't get the inconsistencies due to auto-commits. This would not stop any processing after the timeout, but at least you would get the expected behavior concerning atomicity.

Answer 14

Thanks for the replies. I think that something that would "poison" the connection at timeout would be great. Anything to get the same semantics as distributed transactions.

 

I'm on vacation the next couple of weeks, so I won't be very active in that period. Hope the SqlClient team comes up with a good idea.

 

 

Answer 15

 

At the risk of repeating myself (from http://forums.microsoft.com/forums/ShowPost.aspx?PostID=3029043&SiteID=1)...

1. Poisoning the transaction would work, but may result in the client performing large amounts of wasted work which is doomed when it attempts to commit.
   
2. The semantics of the TransactionScope should not vary depending on whether the transaction is lightweight or distributed.
   
3. The System.Transactions namespace obviously implements the timeout. All I want is an additional constructor parameter for TransactionScope which allows me to say "please thow an exception the first time I try to use the transacted connection after I run out of time", so the timeout both aborts the transaction and stops my code from bashing blithely on. This should work for any transactional resource manager, "lightweight" or otherwise.

 

BTW: What does "Mac Gruber" mean?

 

----------------------------------------

Alasdair Cunningham-Smith

Answer 16

 

Yes, this is a bad bug which was discovered in System.Data after release.  The result is a new SqlConnection parameter called "Transaction Binding"

 

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

 

By default it is "Implicit Unbind" which means SqlConnection will fall-back to auto-commit mode silently.  You can set this parameter to "Explicit Unbind" to hopefully achieve what you would like.

 

Definitly let us know if that parameter does not fix this issue.

Answer 17

Talked to internal folks.  Looks like we are way ahead of you.  We already fixed this by adding a SqlConnection parameter named "Transaction Binding"

 

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx 

 

The “Transaction Binding” parameter whose default is still to fall back to auto-commit mode.

 

Controls connection association with an enlisted System.Transactions transaction.

Possible values are:

Transaction Binding=Implicit Unbind;

Transaction Binding=Explicit Unbind;

 

Implicit Unbind causes the connection to detach from the transaction when it ends. After detaching, additional requests on the connection are performed in autocommit mode. The System.Transactions.Transaction.Current property is not checked when executing requests while the transaction is active. After the transaction has ended, additional requests are performed in autocommit mode.

 

Explicit Unbind causes the connection to remain attached to the transaction until the connection is closed or an explicit SqlConnection.TransactionEnlist(null) is called. An InvalidOperationException is thrown if Transaction.Current is not the enlisted transaction or if the enlisted transaction is not active.

 

Hence this is what you want:

 

Code Snippet

using System;

using System.Text;

using System.Data.SqlClient;

using System.Transactions;

using System.Threading;

namespace TranTest3

{

class Program

{

const string CONNECT_EXPLICIT = "server=server1;integrated security=sspi;transaction binding=Explicit Unbind;";

const string CONNECT_NORMAL = "server=server1;integrated security=sspi;";

static void Main(string[] args)

{

SqlConnection conn = new SqlConnection(CONNECT_EXPLICIT);

conn.Open();

xsql_noerror(conn, "drop table txn1");

xsql(conn, "create table txn1(f1 int)");

// Start inserting records using 2 second txn...

using (CommittableTransaction tx = new CommittableTransaction(TimeSpan.FromSeconds(2)))

{

System.Transactions.Transaction.Current = tx;

conn.EnlistTransaction(tx);

for (int i = 1; i <= 5; i++)

{

Console.WriteLine("Iteration {0}", i.ToString());

try { xsql(conn, "insert txn1 values (1)"); }

catch (Exception ex) { Console.WriteLine("insert exception-> {0}", ex.Message); };

Thread.Sleep(1000);

}

// Commit here raised txn already aborted error...

try { tx.Commit(); }

catch (Exception ex) { Console.WriteLine("tx.Commit exception-> {0}", ex.Message); };

}

// Clear current transaction and close connection.

System.Transactions.Transaction.Current = null;

conn.Close();

// Open new clean connection to check number of rows.

conn = new SqlConnection(CONNECT_NORMAL);

conn.Open();

Console.WriteLine("txn1 table has {0} records.", xsql(conn, "select count(*) from txn1"));

Console.WriteLine("Press <enter> to exit.");

Console.ReadLine();

}

static int xsql(SqlConnection conn, string sql)

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

object x = cmd.ExecuteScalar();

if (null == x) return 0;

return (int)x;

}

}

static int xsql_noerror(SqlConnection conn, string sql)

{

try { return xsql(conn, sql); } catch (Exception) { };

return 0;

}

}

}

 

Oh bleeding MSDN, why can't code snippet blocks understand tabs?

Answer 18

Ahh, my reference to "Mac Gruber" is a reference to the Saturday Night Live skit based on TV show Mac Gryver.  Search for it on You Tube (MacGruber).  Basically in every episode he is trying to defuse a bomb and time runs out.  Hence MacGruber transaction times out and you are in trouble.

Answer 19

EDITED:  I wrote this without reading through page 2 of the posting.  Apologies, the answers from MSFT cover this and you can disregard what follows.  Thanks.

 

> Yes this class is a bit hard to comprehend at first.  It gives you "Mac Gruber" transaction semantics, i.e. if you don't get your work done when the timer runs out you get "blown up" because you are not signalled that the txn aborted and your code keeps writing data in auto-commit mode.

 

As far as I'm concerned, reverting to auto-commit mode is a bug.  When database activity occurs during an enlisted or .NET ambient transaction, the database activity is expected to be a part of that transaction.  The programmer has not done anything to dismiss that transaction!  In the event that the transaction is in an error state (such as timed out), any attempts to perform operations in that transaction should throw an exception (not auto-commit).  IMHO, the SqlClient code should be checking the transaction's state for this condition before it executes the command against the database.

 

I think it was mentioned in this thread that the "correct" behavior occurs in a DTC transaction.  So, that suggests that this is a problem in the promotable transaction implementation.

 

Answer 20

That's a result - and I guess it makes sense because IIRC SQL Server is the only driver which works with the lightweight transactions, and because this doesn't require you the change the API. The only issues I can think of are:

1. Is there a specific version of the framework and/or SqlClient required for this to work?
2. It isn't "discoverable" - the documentation for TransactionScope, CommittableTransaction etc. might benefit from an update to draw attention to this issue and the Transaction Binding setting in the connection string.

I haven't tried it yet, but thanks very much for the solution and for notifying us here.

 

------------------

Alasdair C-S

Answer 21

MacGruber: I've looked, and laughed. There was me thinking this was some kind of advanced academic work on transaction behaviour...

Answer 22

I should write a paper discussing my MacGruber transactions.

 

Answer 23

I can only repeat alasdair's reply. Adding a comment to the documentation would be reeeeeally helpful. I have also not tried it yet, but I'm glad we finally got a useful solution. Thank you very much!

Answer 24

I'm currently working on my app that has the possible timeout issue. The problem is, it is a smart client app that uses SqlServerCe to store the data. I looked, but could not find the Transaction Binding connection string parameter. We are using version 3.0.5300. Is there any way to have the same transaction safety there?

Answer 25

I believe my question just got obsolete. SqlServerCE soes not seem to enlist in ambient transactions whatsoever. Can anyone confirm this?

Answer 26

Thanks for the Transaction Binding ansvers. I believe it's just what I need. I'll check when I return from vacation.

 

 

Answer 27

1. You must be using SqlClient v2.0 SP1 (aka "Orcas" or Visual Studio 2008 version of .Net Framework) in order to use the Transaction Binding keyword.
2. I'll have a word with our documentation folks to see if they can add some more visibility to this issue.
   

Answer 28

I tried the code in the initial post and added the transaction binding=explicit unbind in the connection string but I get the following exception:

"The transaction is either not associated with the current connection or has been completed"

It seems to work fine for TransactionScope though.

Also I'm running VS2005 on .NET 2.0 but the transaction binding keywords seem to solve the issue (with TransactionScope at least)

Could anyone confirm this? Or I'm missing something

Answer 29

"The transaction is either not associated with the current connection or has been completed" is the expected error when you have specified "Explicit Unbind" and either

1. System.Transactions.Transaction.Current != the transaction the connection enlisted in, or
2. the enlisted transaction is no longer in the active state.

Here's a psuedo-code example that demonstrates some of the different situations that give rise to this exception:

using new transaction scope Z
{
  open connection // enlists in Z

  execute command A

  using new transaction scope Y (RequiresNew or Supress)
  {
     execute command B
  }

  interesting point C

  execute command D
}

execute command E

In this example, explicit unbind will cause B & E to throw the exception because Transaction.Current is not Z.
If the transaction is aborted at point C (by timeout, for example), D will will throw the exception because Z, while in Transaction.Current, is not in an continuable state.

Answer 30

Hi alazela,

Thanks for the reply. I'm encountering this issue with Committable Transaction. Here's part of the code. The exception is thrown by EnlistTransaction (line 50) and caught by catch block at line 74

   37 try

   38         {

   39             ReCreateTable();

   40             Console.WriteLine("Table recreated");

   41 

   42             try

   43             {

   44                 using (CommittableTransaction tx = new CommittableTransaction(TimeSpan.FromSeconds(3600)))

   45                 {

   46                     Console.WriteLine("Transaction started lasting 3600 seconds");

   47                     using (SqlConnection con = new SqlConnection(connectionString))

   48                     {

   49                         con.Open();

   50                         con.EnlistTransaction(tx);

   51                         Console.WriteLine("Server is {0}", con.ServerVersion);

   52                         Console.WriteLine("Clr is {0}", Environment.Version);

   53 

   54 

   55                         for (int i = 0; i < 5; i++)

   56                         {

   57                             using (SqlCommand cmd = con.CreateCommand())

   58                             {

   59                                 cmd.CommandText = "insert into TXTEST values ( " + i + " )";

   60                                 cmd.ExecuteNonQuery();

   61                                 Console.WriteLine("Row inserted");

   62                             }

   63 

   64                             Thread.Sleep(TimeSpan.FromSeconds(1));

   65                         }

   66 

   67                         Console.WriteLine("Committing... now we get the timeout (sort of)");

   68 

   69                         tx.Commit();

   70                     }

   71                 }

   72             }

   73 

   74             catch (Exception e)

   75             {

   76                 Console.WriteLine(e.Message);

   77             }

   78 

   79             Console.WriteLine("Table contains {0} rows!!!", CountTable());

   80             DropTable();

   81         }

   82 

   83         catch (Exception e)

   84         {

   85             Console.WriteLine("Unexpected error:");

   86             Console.WriteLine(e.ToString());

   87         }

Answer 31

Yes, that will run into problems with Explicit Unbind.  The issue is that Expicit Unbind is designed to work with Transaction.Current, to help enforce strict scoping within a TransactionScope. Manually binding to a transaction that isn't located in Transaction.Current isn't addresssed by this option.

Three ways I can think of off hand to work around the issue for now, none of them really ideal:

1. Don't use Explicit Unbind. Instead, to account for the auto-detach nature of the command, test the transaction state immediately prior to every execute and do not execute if the state is not active. This will leave a small race window where the transaction could be unbound after your check but before the command locks the connection for execution.
2. Use Explicit Unbind and set Transaction.Current to be your transaction for the duration of command execution. This really is just what TransactionScope does, although you can have more fine-grained control if you do it yourself.
3. Force DTC transactions from the start. DTC transaction interaction doesn't actually release the connection (server-side) until the transaction is ended (timeout doesn't count).

Sounds like what you'd want is an Explicit-but-not-scoped-Unbind option. I'll add that thought to our discussions about future development. Not promising results, mind, but I'll make sure we consider it.

Answer 32

TransactionScope would be enough for my requirements. I was just wondering why it was failing with CommittableTransaction.

Thanks for looking into this.

Answer 33

Hi,

 

My application is build in VB6.0. Unfortunately the Transaction Binding option is not available there.

 

The connection string i used is mentioned below:

Call m_oConnection.Open("PROVIDER=SQLOLEDB;SERVER=" & m_tConfigSettings.strDatabaseServer & ";DATABASE=" & m_tConfigSettings.strDatabaseName & ";Transaction Binding=Explicit Unbind;", m_tConfigSettings.strDatabaseUserName, m_tConfigSettings.strDatabasePassword)

 

And it did not work.....

 

What happens is after some time the transaction (in our case its an explict transaction which we are starting) times out and the sql server (we r using Sql Server 2k5 sp2)internally starts an implicit transaction (At that particular moment i see the SqlServer firing the command Set Implicit transaction ON, though in the sp's i haven't specified and in the Sql Server setting i haven't checked the Implicit transaction check box). And then we see a few records getting rolled back.

 

I have changed the Transaction time out setting @ the COM+ level but that too did not help.

 

I have also changed the transaction time out setting for table designer and for the database designer in SQL Server Management Studio but that too did not help.

 

Any suggetions or idea which can solve this problem. thanks

 

~nitin

Answer 34

 

Hi, My application is build in VB6.0. Unfortunately the Transaction Binding option is not available there.   The connection string i used is mentioned below: Call m_oConnection.Open("PROVIDER=SQLOLEDB;SERVER=" & m_tConfigSettings.strDatabaseServer & ";DATABASE=" & m_tConfigSettings.strDatabaseName & ";Transaction Binding=Explicit Unbind;", m_tConfigSettings.strDatabaseUserName, m_tConfigSettings.strDatabasePassword)   And it did not work.....   What happens is after some time the transaction (in our case its an explict transaction which we are starting) times out and the sql server (we r using Sql Server 2k5 sp2)internally starts an implicit transaction (At that particular moment i see the SqlServer firing the command Set Implicit transaction ON, though in the sp's i haven't specified and in the Sql Server setting i haven't checked the Implicit transaction check box). And then we see a few records getting rolled back.   I have changed the Transaction time out setting @ the COM+ level but that too did not help.   I have also changed the transaction time out setting for table designer and for the database designer in SQL Server Management Studio but that too did not help.   Any suggetions or idea which can solve this problem. thanks   ~nitin

Answer 35

Nitin - I did try to reply to your last post but the forum fell over and I hadn't saved it and didn't have time to write it again.

 

Anyway, you are in the wrong place - this thread is dedicated to a serious transaction semantics issue in the System.Transactions part of the .NET 2.0 framework which MS admit and for which they have provided a working but (in my opinion) wrong workaround. You are using not using .NET, you are using old-school COM (or possibly COM+ if you've built it that way and your using Component Services). Your problem is not well described and your answer is not to be found here. I suggest you rewrite your message and post is to an appropriate forum. Good luck.

Answer 36

Hi Nitin,

 

You are not using SqlConnection.  The Transaction Binding keyword does not work for SQLOLEDB provider so it will not help you here.

 

In general transactions can in rare cases time out and this is normal.  If you see this happen often this points to some bigger issues you need to investigate, primarily why do you have such low concurrency -- why are transactions taking so long.  There are many issues that can cause this.  The best thing to do is get your DBA to analyze with Sql Profiler to help narrow down what is the root cause of blocking.

Answer 37

This issue has been fixed in .Net 4.0. Now the connection will only auto-detach from the transaction when the TransactionScope is disposed. So if the transaction is aborted then any command on that connection will throw an InvalidOperationException instead of being autocommitted. In other words, in .Net 4.0 it is not necessary to use Explicit Unbind.

 

Cheers,

Jared

---

Postings are provided "As Is" with no warranties and confer no rights.

Answer 38

Just recapping the discussion: The bug is indeed fixed in .NET 4.0.

Søren