SQL Server and TransactionScope (with MSDTC): Sporadically can't get connection

Question

I've written some tests for .net code that invokes calls to my SQL Server. It appears that using System.Transactions is an excellent choice for rolling back any modifications to the database that result. I'm aware that some purists would suggest that I might want to mock the database, but I'm not going down that road; this is not strictly pure unit-testing.

When I write and run several tests, this works exactly as expected. I simply put the code to initialize and abort .net transactions in the test setup and test teardown methods. It seemed like a great solution.

However, the problem I have is that when I try to run 100 of these tests, many of them will throw exception that they could not connect to the SQL Server even though they pass when run one-by-one. To make matters even worse, the tables in my database sporadically get locked sometimes when I run my tests. I have to ask my DBA to manually remove the locks.

As many of you will know, using TransactionScope on code that runs on a development workstation (as in running this test) against a SQL server will make the .net framework use MSDTC.

Here's a code sample to illustrate what I'm doing:

 <TestInitialize()> Public Sub MyTestInitialize()
    _scope = New System.Transactions.TransactionScope( _
        System.Transactions.TransactionScopeOption.Required, New TimeSpan(0, 2, 0))
End Sub

<TestCleanup()> Public Sub MyTestCleanup()
    _scope.Dispose()
End Sub

<TestMethod()> Public Sub CurrentProgramUser_Get_UserID()
    Dim ProgramSessionId As String
    Dim CurrentProgramUserId As Integer
    Dim dSession As ProgramSession
    CurrentDCMAUserId = Convert.ToInt32( _
    SqlHelper.ExecuteScalar(testDcmaConnString, System.Data.CommandType.Text, _
    "INSERT into Program_Users(UserName,FirstName,LastName) " & _
    "VALUES('GuitarPlayer','Bob','Marley')" & _
    "SELECT IDENT_CURRENT('Program_users') ") _
                         )
    ProgramSessionId = session.getCurrentSession()
    session.WriteUserParam("Program", ProgramSessionId, "USERID", CurrentProgramUserId.ToString(), testSource, testConnString)

    Dim readValue As Integer
    readValue = session.User.UserID

    Assert.AreEqual(CurrentProgramUserId, readValue)
End Sub

As you can see, there's nothing particularly fancy here. I just have a test method that's going to write some stuff to my database that I want my method to find. This is only one example; there are many other tests like it.

The logic of my tests appears to be sound. What could be causing my tests to not only fail, but sporadically lock users out of tables?

Answer 1

I found the problem. One of the methods I was testing uses a SqlDataReader object. Apparently, the Close() method must be called before the SqlDataReader goes out of scope in order to release the connection, but in my case, I failed to do so.

Simply adding mySqlDataReader.Close() to the end of the method under test fixed the problem. Also, the test method to test this particular method was data-driven with over 100 test cases, so that explains how I could have run out of connections.