Use SqlTransaction & IsolationLevel for lengthy read operation?

Question

I am executing several long-running SQL queries as part of a reporting module. These queries are constructed dynamically at run-time. Depending on the user's input, they may be single or multi-statement, have one or more parameters and operate on one or more database tables - in other words, their form cannot be easily anticipated.

Currently, I am just executing these statements on an ordinary SqlConnection, i.e.

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

Because these queries (really query batches) can take a while to execute, I am concerned about locks on tables holding up reads/writes for other users. It is not a problem if the data for these reports changes during the execution of the batch; the report queries should never take precedence over other operations on those tables, nor should they lock them.

For most long-running/multi-statement operations that involve modifying data, I would use transactions. The difference here is that these report queries are not modifying any data. Would I be correct in wrapping these report queries in an SqlTransaction in order to control their 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();
    }
}

Would this achieve my desired outcome? Is it correct to commit a transaction, even though no data has been modified? Is there another approach?

Do the reports have to be [correct](http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx) or not? — Remus Rusanu, Sep 24 '12 at 07:35
@RemusRusanu The reports provide an instantaneous snapshot of the data over a long period of time; they do not need to take into account changes that may occur mid-transaction. The underlying queries are likely to be run multiple times by the user in short succession, and the user will not expect the results to be identical each time. — Bradley Smith, Sep 24 '12 at 07:46
This is exactly the kind of reports that get affected (badly) by dirty reads. Counts and aggregates will jump randomly up and down and data within a single run (a report) will not be consistent (eg. sum debit != sum credit). Your users will loose confidence in the report as it will seem to produce random data. Have you considered [`SNAPSHOT`](http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx) instead? — Remus Rusanu, Sep 24 '12 at 07:49

score 5 · Accepted Answer · answered Sep 24 '12 at 07:13

5

Another approach might be to issue, against the connection:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

which achieves the same intent, without messing with a transaction. Or you could use the WITH(NOLOCK) hint on the tables in your query, which has the advantage of not changing the connection at all.

Importantly, note that (unusually): however it gets changed (transaction, transaction-scope, explicit SET, etc), the isolation level is not reset between uses of the same underlying connection when fetching it from the pool. This means that if your code changes the isolation level (directly or indirectly), then none of your code knows what the isolation level of a new connection is:

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 the WITH(NOLOCK) quite tempting.

answered Sep 24 '12 at 07:13

Marc Gravell

1,026,079
266
2,566
2,900

1

Wow, thanks for the warning about setting the isolation level... that could be dangerous. – Bradley Smith Sep 24 '12 at 07:23
By setting the isolation level through BeginTransaction, do we avoid the "isolation level not reset between connections" problem you mentioned ? If so, is there any drawback in using a transaction ? – digEmAll Jan 21 '15 at 14:27
1

@digEmAll the *drawback* is that you can exacerbate blocking (perhaps even causing a deadlock in some cases), but this needs to be traded with the *advantage* of getting *ACID* – Marc Gravell Jan 21 '15 at 15:35
@MarcGravell: so wrapping inside a transaction is "stronger" than "SET TRANSACTION ISOLATION LEVEL" and can cause deadlocks... thanks for the help :) – digEmAll Jan 21 '15 at 16:03

score 1 · Answer 2 · answered Sep 24 '12 at 07:20

I agree with Marc, but alternatively you could use the NOLOCK query hint on the affected tables. This would give you the ability to control it on a table by table level.

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

A better approach may be to investigate either SNAPSHOT or READ_COMMITED_SNAPSHOT isolation levels. These give you protection against transactional anommolies without taking locks. The trade off is that they increase IO against TempDB. Either of these levels can be applied either to the session as Marc suggested or the table as I suggested.

Hope this helps

Use SqlTransaction & IsolationLevel for lengthy read operation?

2 Answers2