How to Use System.Data with System.Transactions and Maintain Atomicity and Data Consistency

The Bug

I call it a bug. Initially I was persuaded to believe it was a feature; later I was "convinced" that it is now a matter of app compat and it can't be changed anymore.

Let’s look at the following code:

SqlConnection connection1 = new SqlConnection(connectionString);

using(TransactionScope txScope = new TransactionScope())

{

    connection1.Open();

  SqlCommand command1 = new SqlCommand(commandString1, connection1);

  command1.ExecuteNonQuery();

  SqlCommand command2 = new SqlCommand(commandString2, connection1);

  command2.ExecuteNonQuery();

    txScope.Complete();

}

…

What are your expectations from this code? I’m sure you are saying that both commands will execute with success or none will execute, for instance if command1 succeeds and command2 fails and throws, the transaction rollbacks and command1 is undone. Right?

Let’s add more data to the equation: transactions can abort at any time due to various reasons, most common one being timeouts. Let’s imagine that the transaction timeout is 30 seconds. Then, let’s imagine that command1 takes 29.(9) seconds to execute. This means that the transaction aborts immediately after that. Let’s imagine that it aborts before command2 starts executing. Now, what are the expectations here? Well, transaction aborts and then command1 is undone and everything goes back to the state before the transaction was started.

Unfortunately, by using the defaults for System.Data this is not what happens. What really happens is:

- command1 is undone

- command2 is executed outside the transaction

There is no typo there, command2 executes even if the transaction aborted. In other words, if one expected atomicity for the code inside TransactionScope, the code just corrupted the data due to the partial rollback.

The “Feature”

 Apparently the following code is common:

SqlConnection connection1 = new SqlConnection(connectionString);

using(TransactionScope txScope = new TransactionScope())

{

    connection1.Open();

  SqlCommand command1 = new SqlCommand(commandString1, connection1);

  command1.ExecuteNonQuery();

  SqlCommand command2 = new SqlCommand(commandString2, connection1);

  command2.ExecuteNonQuery();

    txScope.Complete();

}

SqlCommand command3 = new SqlCommand(commandString3, connection1);

command3.ExecuteNonQuery();

In other words, the “feature” allows the use of the connection in auto-commit mode after it was previously used in a transaction. The connection automatically unbinds from the transaction when the transaction completes (either abort or commit) and becomes an auto-commit non-transacted connection.

The App Compat Issue

Now that we know that the automatic unbind can lead to data corruption, changing it to not unbind automatically will make the code for command3 to fail to execute, since the connection1 is trying to run a command in a transaction that completed. Thus, if the code is indeed common, changing the behavior of the connection will indeed break existing apps.

The Solution

The solution implemented by System.Data in “Orcas” is to pass a new keyword to the connection string called “Transaction Binding” and give it the value of “Explicit Binding” to disable the auto-unbind. This will cause command2 to try to execute under the same transaction as command1, even after the abort occurred. See ConnectionString and Integration with Transactions for details.

The following thread might also be useful: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3055387&SiteID=1

Hopefully, the default behavior will change to explicit unbind in the future releases (non-SP) of .Net Framework.