Transaction in Stored Proc + Client Code

I have a SQL Server Sto开发者_运维百科red Procedure that creates a TRANSACTION like this:

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO Table1 ...
    INSERT INTO Table2 ...

    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

This stored procedure will insert into two separate tables. If one of them fails it will roll back.

I also have the following .net code that creates a transaction scope:

using( var scope = new TransactionScope() )
{
    SqlCommand cmd1 = connection.CreateCommand(); 
    SqlCommand cmd2 = connection.CreateCommand();

    // ...

    cmd1.ExecuteNonQuery();
    cmd2.ExecuteNonQuery();

    scope.Complete();
}

What happens if both my stored procedure and my code is creating a transaction? Will this cause problems / need to be a distributed transaction or is this okay as long as I only create one connection to the database?

Unless you had good reason, I would do the transactions in only one place or the other.

And if possible, I would have that place be the database. That reduces round trips, is more easily testable, isolates it from other components of the system, reduces exposed database surface area, and protects database perimeter integrity by forcing actions to go through a well-defined interface.

In this scenario, if one of your stored procedure fails, it will roll back using the internal transaction. Your code will think it has completed and commit the distributed transaction. Is this what you want, as the distirbuted transaction is pointless as each will run individually.

If you want both to run or neither to run, remove your transaction from the stored procedue and just use the distributed transaction.