After a transaction is committed or rolled back, the isolation level of the transaction persists for all subsequent commands that are in autocommit mode (the ssNoVersion default). This can produce unexpected results, such as an isolation level of REPEATABLE READ persisting and locking other users out of a row. To reset the isolation level to the default (READ COMMITTED), execute the tsql SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement, or call SqlConnection.BeginTransaction followed immediately by SqlTransaction.Commit. For more information on ssNoVersion isolation levels, see "Isolation Levels in the Database Engine" in ssNoVersion Books Online.

When your query returns a large amount of data and calls BeginTransaction, a System.Data.SqlClient.SqlException is thrown because ssNoVersion does not allow parallel transactions when using MARS. To avoid this problem, always associate a transaction with the command, the connection, or both before any readers are open.