When a SqlConnection is kept open, does it update?

In my ASP.NET project, I create/open a SqlConnection when necessary, and close it at Application_EndRequest

Nevermind that, I am just wondering what would happen if, while a connection (say Connection A) is opened, the database is updated from another request (say Connecti开发者_如何学Con B).

So it's like this

(Say initially x is 1)

A.Open()

B.Open()

B.UpdateX() --> SET x=2

B.Close()

A.SelectX() --> would this return 1 or 2?

A.Close()

The data returned depends on the state of the DB at the time you run the query and not at the time when you opened the connection.

Let's say you don't use any specific transaction management in your code and let's assume that the update statement is one distinct row or table update. In this case connection a will see X=2. this is because both connections will use the default transaction level which is read committed for both connections.

Now, in your example there is no way to make connection A read X=1 if it is a single value update on a single row. But if you use a transaction on connection B and don't commit and leave the connection open. The query for connection A will block until its timeout would expire. Basically, X would not be accessible until B was done.

Also, If you are updating 10 million rows in one transaction of connection B and connection A was in a different thread and transaction. There is a possibility for connection A to read some old/stale/invalid data by using the transaction isolation level of "read uncommitted".

Hope this helps.

A SQL connection never updates - it is nothing more than a pipe to the server. This is saying like your car updates when you move it somewhere else.

The transaction server side of the session the connection is atached to (note: you can have multiple connections to the same transaction - though I am sure most people dont know that) (which is either implicit or explicit) shows data according to it's configured isolation level. Sometimes you want tone thing, sometimes the other. Sit down and design.

Now, one item with keeping the connection open is that it does not properly reset between pages which may lead to all kinds of stupid issues down the road. Pretty much antipatten. We had an issue here recently in a project with Oracle where the server disconnected clients after 2 hours without data request.-... and the connection died not "close" (or show closed) until the next sql was send. Leads to funny errors down the line that you dont want - use connectin pooling to offset the peformance overhead.