Use SqlTransaction & IsolationLevel for long read operation?

Question

I execute several lengthy SQL queries as part of the reporting module. These queries are built dynamically at runtime. Depending on user input, they can be single or multi-tasking, have one or more parameters and work with one or more database tables - in other words, their form cannot be easily expected.

Currently, I just follow these instructions in a regular SqlConnection , i.e.

 using (SqlConnection cn = new SqlConnection(ConnectionString)) { cn.Open(); // command 1 // command 2 // ... // command N } 

Since these queries (actually query packets) may take some time, I am worried about table locks that support reading / writing for other users. This is not a problem if the data for these reports changes during package execution; report queries should never take precedence over other operations on these tables and should not block them.

For most of the lengthy / multitasking operations involved in changing data, I will use transactions. The difference here is that these report requests do not modify any data. Will I be right to wrap these report requests in SqlTransaction in order to control the isolation level?

i.e:

 using (SqlConnection cn = new SqlConnection(ConnectionString)) { cn.Open(); using (SqlTransaction tr = cn.BeginTransaction(IsolationLevel.ReadUncommitted)) { // command 1 // command 2 // ... // command N tr.Commit(); } } 

Will my desired result be achieved? Is it right to make a transaction, even if the data has not been changed? Is there any other approach?

Answer 1

Another approach might be a connection issue:

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

which achieves the same intention without interfering with the transaction. Or you can use the WITH(NOLOCK) hint for tables in your query, which has the advantage of not changing the join at all.

It is important to note that (unusual): however, it changes (transaction, transaction scope, explicit SET , etc.), the isolation level does not reset between using the same basic connection when retrieving from the pool. This means that if your code changes the isolation level (directly or indirectly), then none of your code knows what isolation level the new connection has:

 using(var conn = new SqlConnection(connectionString)) { conn.Open(); // isolation level here could be **ANYTHING**; it could be the default // if it is a brand new connection, or could be whatever the last // connection was when it finished } 

Which makes WITH(NOLOCK) pretty enticing.

Answer 2

I agree with Marc, but as an alternative, you can use the NOLOCK query hint in the affected tables. This would give you the ability to manage it on a table level table.

The problem with running any queries without using common locks is that you leave yourself open to non-deterministic results, and business decisions should not be made based on this data.

A better approach would be to study the isolation levels of SNAPSHOT or READ_COMMITED_SNAPSHOT. This gives you protection against transactional anomalies without blocking. The tradeoff is that they increase IO against TempDB. Any of these levels can be applied either to a session, as suggested by Mark, or to a table, as I suggested.

Hope this helps