Does TransactionScope work with pre-existing connections?

Question

I have a code like this:

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        some_db_function();

        for (i = 0; i < 10; i++)
        {
            some_other_db_function();
        }

        scope.Complete();
    }
}
catch (Exception ex)
{
   MessageBox.Show(ex.Message + " all done transactions will rollback");   
}

and inside the db functions something like this happens:

private void some_db_functions()
{
    using (TransactionScope scope = new TransactionScope())
    {
       //some processing on db
       scope.Complete();
    }
}

It is supposed to be that if there was any problem in the database transactions, like an error inserting or updating in the functions; all the transactions that had been done so far get rolled back. But it does not work like that; and although it throws an exception and the scope.Complete() in the parent function never gets triggered, still nothing get rolled back.

Where is the problem?

you just don't need to use scope.Complete() , using the Transaction scope inside an using statement takes care of everything. — Flowerking, Feb 13 '12 at 09:15
are your DB methods opening a connection or using an existing one? — Mitch Wheat, Feb 13 '12 at 09:16
it does not. even without scope.Complete() it does not work properly. — Siyavash, Feb 13 '12 at 09:18
they are all using the same connection, declared previously. — Siyavash, Feb 13 '12 at 09:19
You shouldn't need to add a seperate TransactionScope in the some_db_functions as that method will still fall under the scope of the initial TransactionScope. What could be happening is the method is commiting the transaction but then the caller is throwing the exception but there is nothing in the callers transaction to roll back — David Esteves, Feb 13 '12 at 09:21
@Flowerking no, you explicity need `Complete()` at the end of the success scenario. If you don't call `Complete()`, it is always rolled back during the `Dispose()` (via `using`, usually) — Marc Gravell, Feb 13 '12 at 09:23

Mitch Wheat · Answer 1 · 2012-02-13T09:28:56.803

If the open connection already exists it will not automatically enlist in an ambient transaction. You would have to set it explicitly.

Implicitly enlisting connections is not supported. To enlist in a transaction scope, you can do the following:

Open a connection in a transaction scope.

Or, if the connection is already opened, call EnlistTransaction method on the connection object.

Ref.

This will enlist an existing connection:

connection.EnlistTransaction(Transaction.Current)

score 14 · Accepted Answer · edited Jul 18 '18 at 19:03

14

IIRC, automatic enlisting into ambient transactions happens at connection creation/opening time; if you create the connection inside the scope of the transaction, all should be good. However:

they are all using the same connection, declared previously

if the connection exists outside of the transaction, it won't enlist.

Best practice is to create/open a connection only around a unit of work, not forever (and: let connection pooling do its job). If you follow that practice, it should work fine. So:

This won't work:

using(var conn = CreateAndOpenConnection()) {
    // ...
    using(var tran = new TransactionScope()) {
        SomeOperations(conn);
        tran.Complete();
    }
    // ...
}

where-as this should work:

using(var tran = new TransactionScope()) {
    // ...
    using(var conn = CreateAndOpenConnection()) {
        SomeOperations(conn);
    }
    tran.Complete();
    // ...
}

edited Jul 18 '18 at 19:03

neizan

2,291
2
37
52

answered Feb 13 '12 at 09:22

Marc Gravell

1,026,079
266
2,566
2,900

@Mitch given that there's ~30 seconds between our posts, I'm pretty sure we were typing away in parallel. Yes, we concur. – Marc Gravell Feb 13 '12 at 09:27
so how about those scope.Complete()s are they all needed in every function ? – Siyavash Feb 13 '12 at 09:37
@Siyavash huh? they are needed once per transaction – Marc Gravell Feb 13 '12 at 09:38
so there should be a transactionscope for each child method and also a scope.complete() for each of them ? – Siyavash Feb 13 '12 at 09:45
1

@Siyavash that depends on whether you expect those child methods to be called separately. At the simplest, you could just use the encapsulating (outermost) transaction. But if you **do** have inner-transactions, then yes: they all need to complete before being disposed, else they instantly doom the **outermost** transaction. – Marc Gravell Feb 13 '12 at 09:53
because [here](http://msdn.microsoft.com/en-us/library/ms172152(v=vs.90).aspx) it says that there should be a transaction using in every child method. im kinda confused! – Siyavash Feb 13 '12 at 09:55
1

@Siyavash where exactly does it say that? if you mean the example: that is to illustrate that you **can** nest transactions, and if you do: essentially the outermost transaction controls "commit", and **any** failure controls "abort". You are not **required** to use transaction-scope at multiple levels, and indeed one of the most useful features of `TransactionScope` is that it applies to nested code automatically **without** having to explicitly implement any changes relating to transactions. – Marc Gravell Feb 13 '12 at 09:58

Does TransactionScope work with pre-existing connections?

2 Answers2

Linked