SqlDataReader and database access concurrency

Question

The easiest way to illustrate my question is with this C# code:

using (SqlCommand cmd = new SqlCommand("SELECT * FROM [tbl]", connectionString))
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        //Somewhere at this point a concurrent thread, 
        //or another process changes the [tbl] table data

        //Begin reading
        while (rdr.Read())
        {
            //Process the data
        }
    }
}

So what would happen with the data in rdr in such situation?

I assume that you get the old data. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. But that depends on the isolation level. Have a look at this answer: http://stackoverflow.com/a/1539005/284240 — Tim Schmelter, Jun 25 '13 at 22:43
@TimSchmelter: OK. Thanks for the link, although your own answer is somewhat ambiguous. You say that I'd get the old data, but then at the end you say that it depends on the isolation level. So which one is it? And what is this "isolation level"? — ahmd0, Jun 25 '13 at 23:02
if it would be an answer I would edit it and delete the first sentence ;-) — Tim Schmelter, Jun 25 '13 at 23:08
@ahmd0 I just make some tests and I get the old data - the data that I have the moment I run the `select *` — Aristos, Jun 25 '13 at 23:11
@Aristos: Thanks. So what about the SQL Server isolation level that Tim brought up? — ahmd0, Jun 26 '13 at 00:26
More complex than you would think. I do this a lot. It appears SqlDataReader will grab like 1000 rows at a time. If I need to process THE CURRENT value in a loop I do it in server side cursor. In a server side cursor you get the the current value and if you are going to mutate a cursor is faster than a Reader with an update. With Reader you might as well "with (nolock)". — paparazzo, Jun 26 '13 at 03:13
@ahmd0 I always use mutex on case like that, or other synchronizations, because I like to be absolutely sure that I do have it synchronization. Now, if you have them on pages with session, then the session lock of the page is synchronize it. If you do not use session, or make background process is better to lock the common process. Also I use web garden (many working process together) and many threads together, so I usually lock all similar to that cases. — Aristos, Jun 26 '13 at 09:00
@Aristos: Hmm, mutex on a simple `SELECT`. Wouldn't it be an overkill? You'd be better off with a `ReaderWriterLock` in that case. The issue though, what if the database is accessed from outside your app? — ahmd0, Jun 26 '13 at 18:21
@ahmd0 Of course, ReaderWriterLock is better and I use it, or I use different mutex names base on what ID I read/write and other tricks. — Aristos, Jun 26 '13 at 18:23

user1429080 · Accepted Answer · 2013-06-27T07:26:41.890

I actually tested this. Test code:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
{
    conn.Open();
    using (SqlCommand comm = new SqlCommand("select * from test", conn))
    {
        using (var reader = comm.ExecuteReader())
        {
            int i = 0;
            while (reader.Read())
            {
                if ((string)reader[1] == "stop")
                {
                    throw new Exception("Stop was found");
                }
            }
        }
    }
}

To test, I initialized the table with some dummy data (making sure that no row with the value 'stop' was included). Then I put a break point on the line int i = 0;. While the execution was halted on the break point, I inserted a line in the table with the 'stop' value.

The result was that depending on the amount of initial rows in the table, the Exception was thrown/not thrown. I did not try to pin down where exactly the row limit was. For ten rows, the Exception was not thrown, meaning the reader did not notice the row added from another process. With ten thousand rows, the exception was thrown.

So the answer is: It depends. Without wrapping the command/reader inside a Transaction, you cannot rely on either behavior.

Obligatory disclaimer: This is how it worked in my environment...

EDIT:

I tested using a local Sql server on my dev machine. It reports itself as:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

Regarding transactions:

Here's code where I use a transaction:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    using (SqlCommand comm = new SqlCommand("select * from test", conn, trans))
    {
        using (var reader = comm.ExecuteReader())
        {
            int i = 0;
            while (reader.Read())
            {
                i++;
                if ((string)reader[1] == "stop")
                {
                    throw new Exception("Stop was found");
                }
            }
        }
        trans.Commit();
    }
}

In this code, I create the transaction without explicitly specifying an isolation level. That usually means that System.Data.IsolationLevel.ReadCommitted will be used (I think the default isolation level can be set in the Sql Server settings somewhere). In that case the reader behaves the same as before. If I change it to use:

...
using (var trans = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
...

the insert of the "stop" record is blocked until the transaction is comitted. This means that while the reader is active, no changes to underlying the data is allowed by Sql Server.

Thanks for doing this test. What version of SQL Server did you run it on? Also, can you show how you'd wrap it in a `transaction`? — ahmd0, Jun 26 '13 at 18:02

SqlDataReader and database access concurrency

1 Answers1