TransactionScope and rollback

Question

Consider the following table scheme:

T2 --> T1 <-- T3

T2 refers to T1, T3 refers to T1, thus I cannot delete rows from T1 that are referred in either T2 or T3.

Now, using the following piece of code:

using (var sqlConnection = new SqlConnection(ConnectionString))
{
    sqlConnection.Open();

    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
    {
        sqlCommand.CommandText = "USE [TransactionScopeTests]; DELETE FROM T3; DELETE FROM T1;"; // DELETE FROM T1 will cause violation of integrity, because rows from T2 are still using rows from T1.

        sqlCommand.ExecuteNonQuery();
    } 
}

This code, executed on a SQL Server 2014 LocalDb, or a Sql Server 2012 Developer edition yield the same result. The table T3 is empty, but the table T1 remains untouched.

Hooking up a profiler to this query gives the following SQL being executed:

Audit Login:

-- network protocol: Named Pipes
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

And then SQL:BatchStarting

USE [TransactionScopeTests]; DELETE FROM T3; DELETE FROM T1;

Then an Audit Logout, followed by a RPC:Completed

exec sp_reset_connection 

Looking at the Tables, T3 is empty, T1 is not.

However if we were to wrap the code in a TransactionScope:

using (var transactionScope = new TransactionScope())
{
    using (var sqlConnection = new SqlConnection(ConnectionString))
    {
        sqlConnection.Open();

        using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
        {
            sqlCommand.CommandText = "USE [TransactionScopeTests]; DELETE FROM T3; DELETE FROM T1;";

            sqlCommand.ExecuteNonQuery();
        }

        transactionScope.Complete();
    }
}

This will cause the correct behavior. When the connection is closed, the transaction is rolled back.

However, what bothers me is that I cannot see this behavior when profiling the SQL being sent. I was expecting a connection to be opened with BEGIN TRAN or a SET XACT_ABORT ON or something like that, but I cannot see the difference (I literally saved the output to a textfile and compared them with a difftool, they're identical to the letter.)

This is the output of the profiler:

Audit Login:

-- network protocol: Named Pipes
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

And then SQL:BatchStarting

USE [TransactionScopeTests]; DELETE FROM T3; DELETE FROM T1;

Then an Audit Logout, followed by a RPC:Completed

exec sp_reset_connection 

However, in this case T3 reverts to it's original state because the whole statement has failed.

Can somebody elaborate on how .NET makes sure the code is executed in a Transaction?

Answer 1

Try changing your query to:

SELECT @@OPTIONS USE [TransactionScopeTests]; DELETE FROM T3; DELETE FROM T1;

You should see different options at the level of the session, as this is what a transaction scope alters. Give it a try and the options should be different!