After a Read Uncommitted query, do I have to set it back to Committed?

Question

Imagine executing a query using code similar to this here:

using (SqlConnection TheConnection = GetSqlConnectionNoCatch(SQLConnectionStr))
using (SqlDataAdapter TheDataAdapter = new SqlDataAdapter(SQLStatement, TheConnection) { MissingSchemaAction = SchemaAction })
{
    DataSet TheDataSet = new DataSet();
    TheDataAdapter.SelectCommand.CommandTimeout = SQLTimeout;
    TheDataAdapter.Fill(TheDataSet, TableName);

    return TheDataSet;
}

And imagine having to read a database table that is absolutely getting pounded, non-stop, with writes, which leads to a lot of deadlocks and failures so you have to perform that read using a Read Uncommitted Isolation level.

If my normal query was:

SELECT Field1, Field2 FROM Table WHERE some_type_of_clause

I often see that you would change it to:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT Field1, Field2 FROM Table WHERE some_type_of_clause

OK, so here's what led me to ask this question, this particular link: http://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/

His example has the following:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON
sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate
-- Set isolation level to original isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Do I need to set it back to READ COMMITTED at the end of my query too? Or is my read uncommitted only good for that one query? Or maybe the life of the connection (which examining my code would mean as soon as I return the DataSet, as my Connection closes right then)?

Most examples on the web leave out the 'set it back to original isolation level', so it puzzled me that he included it. Thanks!

Answer 1

The answer here may be helpful: WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The transaction isolation level is a connection level setting. This means that any nested stored procedure calls, etc will use your new isolation level setting, but other users/query windows/session from the same user will use the applicable default (or whatever was last set).

In your case, you are not required to set the isolation level back to committed unless you have other statements or data sets to return that shouldn't be using uncommited later in the same session. Resetting is simply a good practice.