Autocommit and multiple SqlConnections

Question

Is an insert with autocommit guaranteed to be immediately visible to any/every SqlConnection on the database, or only on it's own SqlConnection?

I have a procedure with 3 SqlConnections. Connection One is associated with a SqlDataReader which is driving a loop. Withing the loop, Connection Two is used to get data for test, and Connection Three is used to insert rows in a table. The insert autocommits.

We have a situation where the test (using Connection Two data) is failing. One possible explanation is that Connection Two sometimes does not see the row inserted by Connection Three on the previous pass through the loop. Is this possible?

I don't see a problem with combining Two and Three, and I'm going to do that, but I don't have any way to force this error to occur, and I'd like to know if this explanation is even possible.

Answer 1

Yes, auto-committed inserts are the same as any other committed inserts and will be visible on other connections.

However, inserting rows won't change the rows returned by an already-existing SqlDataReader, since the SqlDataReader object encapsulates a record set - the results of a query - and not the query itself.

I'm not sure what your "Connection Two" is doing, but if it's depending on the results of the INSERT in C3 being visible to the SqlDataReader, then it will definitely fail for this reason.

Answer 2

Is this what you're doing?

1. You use open a SQLDataReader which will get the data and then loop through it.
2. During this loop, you insert a record that you then expect via the select statement used in the reader.
3. You don't see it in the loop, as the data has already been selected.