TransactionScope causing blocking?

Question

I'm writing some Unit tests against a database, and we're using transactions to make sure that our test data gets removed at the end.

I'm running into a problem where methods that I'm testing are using their own TransactionScope objects, and it seems to be blocking when hitting the database.

This is inside my test's base class:

BaseScope = new CommittableTransaction(new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUnCommitted, Timeout = new System.TimeSpan(0, 5, 0) });

and then inside the method I'm testing, it does:

using (TransactionScope scope = new TransactionScope())

The first time that the code inside the 2nd scope their touches the database, it hangs. Do I have any way around this problem?

Answer 1

If you are using a Database then you are not doing Unit testing, and the problems you are experiencing are one of the reason why true Unit testing uses Mocks and Stubs.

Now the tests you are doing are very valuable, and in some cases I would actually do them instead of Unit Testing. I label this Early Integration Testing (EIT). The key point here is that we find a whole new class of bugs when working with the real thing not the Unit Test mocks. And the key here is Real Thing. As soon as you fake up the environment with artifical transaction scopes etc. you are losing much of the benefit of EIT because you don't catch subtle interaction errors, or (as in your case) introduce artificial problems.

I would find a way to quickly populate the database with sufficient test data, and restore it to that state outside the test. A "reset to known state" script is very helpful for these kind of tests.

Answer 2

When you nest TransactionScope instances you can end up with a distributed transaction, rather than a simple local transaction. This behavior varies somewhat between database being used. SQLServer 2008, for instance doesn't escalate to a DTX unless multiple databases are actually involved. Oracle, on the other hand, will always escalate to a distributed transaction since it can't does not support sharing connections for a single local transaction.

Depending on which database and what TransactionScopeOption you are using, you may end up with a deadlock. This occurs because DTXs often require table locks to ensure that they can be committed atomically. In Oracle, for example, if you start a DTX and crash or lose your connection before you complete it, you can end up with an "In Doubt Distributed Transaction". This "In Doubt" transaction may lock one or more tables preventing other session from modifying them until a DBA performs a ROLLBACK FORCE command on the pending transaction ID. Some databases (like SQLServer) attempt to detect such deadlocks and terminate one of the offending transactions ... but this is guaranteed to happen.

I would suggest one of two options for you:

1. Decide if you really need to write tests that hit the database. Often times, you can use a mock or stub to avoid the need to write tests that alter and then roll-back the database. Avoiding such problems makes sense since it both speeds up your tests and eliminates a potential dependency from them. However, sometimes you can't do this.
2. If you really need to test your logic against the database, consider modifying your code so that all methods use the same database connection to execute their SQL. This will eliminate the creation of a distributed transaction, and hopefully overcome your issue.

You may also want to look into your database's pending transaction view (in Oracle it's called PENDING_TRANS$ ... in SQLServer there's the XACT_STATE() function).

Answer 3

You'll have to commit your base transaction in order to unblock your test method, which I assume isn't the sort behavior you desire. You need to get your test method's transaction to join the outer "ambient" (umbrella/parent/base/outer) transaction created in your base class, rather than try to create its own.

From MSDN CommittableTransactionClass Remarks (emphasis mine):

It is recommended that you create implicit transactions using the TransactionScope class, so that the ambient transaction context is automatically managed for you. You should also use the TransactionScope and DependentTransaction class for applications that require the use of the same transaction across multiple function calls or multiple thread calls. For more information on this model, see the Implementing An Implicit Transaction Using Transaction Scope topic.

Creating a CommittableTransaction does not automatically set the ambient transaction, which is the transaction your code executes in. You can get or set the ambient transaction by calling the static Current property of the global Transaction object. For more information on ambient transactions, see the " Managing Transaction Flow using TransactionScopeOption" section of the Implementing An Implicit Transaction Using Transaction Scope topic. If the ambient transaction is not set, any operation on a resource manager is not part of that transaction. You need to explicitly set and reset the ambient transaction, to ensure that resource managers operate under the right transaction context.

Until a CommittableTransaction has been committed, all the resources involved with the transaction are still locked.

As djna pointed out, using transactions to rollback changes made during testing is rather abusive. You test should be a good citizen and undo and changes it makes itself to the database in a finally clause so that other tests that may run after it never are affected. If you have lots of tests that aren't well behaved already, then you're probably not going to go this route now. In that case, change your base to use implicit transactions with a scope set to RequiresNew, and in your test method, use Required.