SqlDataReader - result set cached?

Question

When you use a SqlDataReader, is the return set completely determined by the ExecuteReader step, or can you influence what you get by writing to the source table(s) while reading? Here is an example in very rough pseudo code.

sc = new SqlCommand("select * from people order by last, first",db) ;
sdr = sc.ExecuteReader() ;

while (sdr.read())
{
    l = (string) sdr["last"] ;
    k = (string) sdr["key"] ;
    if (l.Equals("Adams")) 
    {
       sc2 = new SqlCommand("update people set last = @nm where key = @key") ;
       sc2.Parameters.Add(new SqlParameter("@nm", "Ziegler"));
       sc2.Parameters.Add(new SqlParameter("@key", k));
       sc2.ExecuteNonQuery() ;
    }
}

I've seen a lot of bad errors in other environments caused by writing to the table you are reading. Here record k gets bumped from the top of the list (Adams) to the bottom (Ziegler). I've assumed (ha!) that SqlDataReader is immune. True? False?

score 4 · Answer 1 · answered May 28 '09 at 14:22

4

It depends on your transaction isolation level or other locking hints, but iirc by default reading from a table in sql server locks those records, and therefore the code you posted will either deadlock (sc2 will eventually timeout) or the updates will go into the the transaction log and none will be written until your reader is finished. I don't remember which off the top of my head.

answered May 28 '09 at 14:22

Joel Coehoorn

399,467
113
570
794

My actual implementation worked fine as best I can tell. It certainly updated the records so the table was not locked, but I did not change a field that would affect the sort order. – SeaDrive May 28 '09 at 15:54
Sounds like the 2nd then: updates where kept in the transaction log. That or you are setting a weaker isolation level somewhere. – Joel Coehoorn May 28 '09 at 16:00

score 1 · Answer 2 · answered May 28 '09 at 14:26

1

One issue I see is that when the reader is open it owns the database connection, nothing else can use it while the reader is open. So the only way possible to do this is using a different database connection, and still it would depend on transaction level

answered May 28 '09 at 14:26

Brettski

19,351
15
74
97

I did use a separate database connection. – SeaDrive May 28 '09 at 15:48

score 0 · Answer 3 · answered May 28 '09 at 14:27

If you want to assume that the read data is not altered by those updates, could you read the data into a temporary object container, and then after all the reading is done, then do your updates? It would make the issue moot.

Of course, I did find the question interesting from a "how does this really work" standpoint.

score 0 · Answer 4 · answered May 28 '09 at 14:30

If you want to do updates while you're iterating on the query results you could read it all into a DataSet.

I know you didn't ask about this, and I also know this is pseudo-code, but be sure to wrap your sc, sdr, and sc2 variables in using () statements to ensure they're disposed properly.

SqlDataReader - result set cached?

4 Answers4