SELECT statement within transaction

[BobRoyAce]

Let's say that I am performing a bunch of insert/update queries within
a transaction that is created as follows:

Dim cnn As New
SqlClient.SqlConnection(My.Settings.GRPConnectionString)
cnn.Open()
Dim trx As SqlClient.SqlTransaction = cnn.BeginTransaction()

Further suppose that, in the middle of all of the insert/update
queries, I want to execute a SELECT query against one of the tables
that is being affected by the aforementioned queries.

Will the SELECT query reflect changes made by them even if the
transaction has not yet been committed?

Do I have to use a separate connection to do the SELECT query?

 

[Cor Ligthert[MVP]]

Bob,

You have normally only one connection, so everything is done one by one.

Cor

 

[J.B. Moreno]

Let's say that I am performing a bunch of insert/update queries within
a transaction that is created as follows:
-snip-
Will the SELECT query reflect changes made by them even if the
transaction has not yet been committed?

Yes. All commands within the transaction share the same scope. Think
about it, it has to be that way, otherwise a transaction would consist
of a single command.

Do I have to use a separate connection to do the SELECT query?

That depends upon what you want...

You should really play with Query Analyzer or SSMS, using transactions
and rollbacks and commits to see what does what you want to happen.

But let's briefly examine what *MIGHT* happen if you used two
connections


C1 -> UPDATE Tbl Set Value = 1 Where Value = 2 -- ten rows effected

C2 -> SELECT * FROM Tbl Where Value = 2 -- ten rows returned

But with two connections, that could happen because the command on C2
was executed before the command on C1 or while the command on C1 was
executing, or after and the transaction wasn't committed.

 

[Mr. Arnold]

Let's say that I am performing a bunch of insert/update queries within
a transaction that is created as follows:

Dim cnn As New
SqlClient.SqlConnection(My.Settings.GRPConnectionString)
cnn.Open()
Dim trx As SqlClient.SqlTransaction = cnn.BeginTransaction()

Further suppose that, in the middle of all of the insert/update
queries, I want to execute a SELECT query against one of the tables
that is being affected by the aforementioned queries.

The commit must be made, otherwise, the insert and updates have not been
applied to that database table.

Will the SELECT query reflect changes made by them even if the
transaction has not yet been committed?

Yes, you have to commit the changes.

Do I have to use a separate connection to do the SELECT query?

You do the Select after the transitions are committed, you don't need a
second connection, you use the same connection that's already being used
leave it open after the commit and you move the Select statement out from
the middle of the code that executing the transactions.

 

[BobRoyAce]

I would conclude that I need to do the SELECT within the same
connection. However, the problem I was having, when trying to run with
a different connection, was that the query would just time out every
time. If I debugged through code, all the way up to, but not including
the SELECT query, then went to SQL Server Management Studio and tried
to run the SELECT query from there (while program execution is
suspended), I would get same result...timeout. So, was just trying to
figure out why that would be happening...

 

[Mr. Arnold]

I would conclude that I need to do the SELECT within the same
connection. However, the problem I was having, when trying to run with
a different connection, was that the query would just time out every
time. If I debugged through code, all the way up to, but not including
the SELECT query, then went to SQL Server Management Studio and tried
to run the SELECT query from there (while program execution is
suspended), I would get same result...timeout. So, was just trying to
figure out why that would be happening...

MS.Public.SQLserver.server