IsolationLevel.ReadUncommitted blocking Reading on different connection to SQL Server 2008

Question

Hi I've got a problem with reading from my database when the calling code has a pending transaction. Like this:

f1() {
DbTransaction t = Connection1.BeginTransaction(IsolationLevel.ReadUncommitted);
  ...
  f2();
  ...
  t.Commit();
}

f2() {
  Connection2.Execute("SELECT...");
}

The call to f2() results in a timeout exception.

That behaviour was surprising to me because I thought that IsolationLevel would allow reading access to the db. When I remove the transaction from f1() the problem disappears.

I am thankful for any comment son this and on how to solve the problem. I would like to keep the transaction!

(Visual Studio 2010, C#, .NET 4.0, SQL Server 2008 compatibility level 80)

Answer 1

As @Vijay says, it doesn't seem like you need to create a second connection here, so this problem is easily resolved by using the same connection.

For reference, though, to explain why the second connection is blocking:

Your first connection is running at an isolation level of READ UNCOMMITTED, which can still create exclusive locks. At some point between creating Connection1 and calling f2(), you do something to create an exclusive lock, which blocks the statement executed by f2(), which is running at the default isolation level of READ COMMITTED.

If you want to see what is occurring with locks, open two SSMS windows. In the first window, run these statements:

set transaction isolation level read uncommitted
go

begin transaction 
-- alter some data
delete MyTable
waitfor delay '00:00:15'

rollback transaction

In the second window, run sp_lock to see the locks on MyTable.