CODEWITHSUNDEEP
.netsql-serverunit-testingtransactionscope
alexey
alexey

Reputation: 8480

Is it possible to rollback only nested transaction for autotesting

Consider the following code:

ExecuteSQL("UPDATE ...");

using (var t = new TransactionScope())
{
    ExecuteSQL("UPDATE ...");

    if (SomeCondition)
        t.Commit();
    else
        t.Rollback();
}

var result = ExecuteSQL("SELECT ...");

Then we write an auto test using outer TransactionScope to rollback changes after each test:

[TestInitialize]
public override void Initialize()
{
    _transaction = new TransactionScope();
}

[TestCleanup]
public override void Cleanup()
{
    _transaction.Dispose();
}

It's not possible to write a correct test for a case when SomeCondition is false. Because nested transaction ROLLBACK rollbacks the whole outer transaction including the first UPDATE statement.

Do you know any workarounds?

Upvotes: 2

Views: 254

Answers (3)

Oleg Dok
Oleg Dok

Reputation: 21776

Unnamed rollback rolls all thansactions back up to the top of the stack.

The only thing which can help is to use named SAVE TRANSACTION

Warning: Does not work with distributed transactions.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

No. You can rollback to a savepoint, see Exception Handling and Nested Transactions but that is not the same as what you want.

What is what you're really trying to achieve? Testing should occur under similar conditions to production. Adding an uber transaction and testing methods in inner transactions is nothing like how the system would behave in production.

Upvotes: 1

Andomar
Andomar

Reputation: 238276

SQL Server doesn't really support nested transactions. It just has one transaction that has seen multiple start transaction statements, and requires multiple commit transaction statements before it really commits.

As a workaround, don't nest the transactions. It is possible to run two transactions simultaneously on different connections.

Upvotes: 0

Related Questions