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?

Answer 1

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.