Tonight I was trying to debug a couple of failing unit tests for Subtext and I came across a common (though not exactly obvious) issue. These particular tests required access to a database, so I was using MBUnit's Rollback attribute to make sure that any changes made during execution of the test were rolled back when the test was finished.

Essentially all the Rollback attribute is doing is wrapping the entire test in a SQL Transaction - smart!

So what's wrong?

As I was debugging one of the failing tests I realized that I was not getting the expected data back from my calls to the database. Hmm, that's odd.

So, I opened up a new Query Analyzer instance and ran a SELECT on the table in question to see if it really contained the data I was after. What did I get... a SQL Server Timeout. WFT!?!

What's the deal?

It didn't take me too long to realize the problem was that nifty Rollback attribute I had on my test method. Like I said, that attribute was just wrapping my entire test in a big Transaction... which was locking the database tables that I was trying to read from in my SELECT statement.

If a transaction changes a row, it holds exclusive locks on the changed data. The default behavior in SQL Server is that no other transactions can read the row until the writer commits.

So... the quick and dirty way to get around the lock contention is to use the NOLOCK keyword in your query. Below is an example of the query that I ran (notice the NOLOCK keyword in bold):

SELECT * FROM subtext_Content 
  WITH (NOLOCK) 
WHERE BlogId = 323

Bam! Just like that I was able to run queries on tables that were locked by a currently running transaction.

And for the record, I discovered that it wasn't my SQL Query that was causing the unit test to fail... it was the data in the table. I had bad data that was caused by a previously undiscovered bug. I've since fixed both issues. :)