SQL Server and TransactionScope (with MSDTC): sporadically unable to get connection

Question

I wrote some tests for the .net code that makes calls on my SQL Server. It seems that using System.Transactions is a great choice to roll back any changes to the database that produce the result. I know that some purists suggest that I might want to make fun of the database, but I will not go this way; this is not pure testing.

When I write and run some tests, it works exactly as expected. I just put the code to initialize and abort .net transactions in the testing and testing methods. It seemed like a great solution.

However, the problem is that when I try to run 100 of these tests, many of them will throw an exception that they could not connect to SQL Server, even if they are passed one after another at startup. To make matters worse, tables in my database are periodically locked sometimes when I run my tests. I have to ask my DBA to manually remove the locks.

As many of you will learn, using TransactionScope for code running on a development workstation (as when running this test) with a SQL server will make use of the .NET MSDTC environment.

Here is a sample code to illustrate what I am 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 is nothing particularly interesting here. I just have a testing method that is going to write some things to my database that I want my method to find. This is just one example; There are many other similar tests.

The logic of my tests seems sound. What can make my tests not only fail, but sporadically block users from tables?

Answer 1

I found a problem. One of the methods I tested uses the SqlDataReader object. Apparently, the Close() method should be called before SqlDataReader goes out of scope to release the connection, but in my case I did not.

mySqlDataReader.Close() simply adding mySqlDataReader.Close() to the end of the test method, the problem is fixed. In addition, the testing method for testing this particular method was based on data with over 100 test cases, so this explains how I could end up from connections.