SqlDataReader and concurrency database access

Question

The easiest way to illustrate my question is with 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 will happen to the data in rdr in this situation?

Answer 1

I really 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 (make sure that the line with the value "stop" was not included). Then I put a breakpoint on the line int i = 0; . Although execution was stopped at a breakpoint, I inserted a row into the table with the value "stop".

The result was that depending on the number of source rows in the table, an Exception was thrown / not thrown. I did not try to determine exactly where the line limit was. For ten lines, no Exception was thrown, which means that the reader did not notice the line added from another process. With ten thousand lines, an exception was thrown.

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

Mandatory disclaimer: this is how it worked in my environment ...

EDIT:

I tested using the local Sql server on my dev machine. He communicates himself as:

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

Regarding Transactions:

Here is the code where I use the 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 a transaction without explicitly specifying the isolation level. Usually this means that System.Data.IsolationLevel.ReadCommitted will be used (I think that the default isolation level can be set in the Sql server settings somewhere). In this case, the reader behaves the same as before. If I change it, use:

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

insertion of the stop record is blocked until the transaction is completed. This means that while the reader is active, no changes to the underlying data are allowed by the Sql server.