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.SqlCo nnection(My.Set tings.GRPConnec tionString)
cnn.Open()
Dim trx As SqlClient.SqlTr ansaction = cnn.BeginTransa ction()

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

"BobRoyAce" <br**@omegasoft wareinc.comschr eef in bericht
news:70******** *************** ***********@79g 2000hsk.googleg roups.com...

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.SqlCo nnection(My.Set tings.GRPConnec tionString)
cnn.Open()
Dim trx As SqlClient.SqlTr ansaction = cnn.BeginTransa ction()

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?

[J.B. Moreno]

BobRoyAce <br**@omegasoft wareinc.comwrot e:

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.

--
J.B. Moreno

[Mr. Arnold]

"BobRoyAce" <br**@omegasoft wareinc.comwrot e in message
news:70******** *************** ***********@79g 2000hsk.googleg roups.com...

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.SqlCo nnection(My.Set tings.GRPConnec tionString)
cnn.Open()
Dim trx As SqlClient.SqlTr ansaction = cnn.BeginTransa ction()

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.

[Phil]

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.SqlCo nnection(My.Set tings.GRPConnec tionString)
cnn.Open()
Dim trx As SqlClient.SqlTr ansaction = cnn.BeginTransa ction()

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?

I've not used transactions with Sql Server and Visual Studio yet, but I have
used other DBMSs with other programming languages. How I would expect it to
work is that any query that you perform with a single connection, within the
scope of the same transaction, would see the changes that you have made,
even though the changes have not been committed to the database. Any other
connection, not within the scope of this transaction would not see the
changes until they have been committed. So whether you use a separate
connection or not depends on what you want to see.
Think of a transaction as working on a separate temporary copy of the
database.
For example
If I have a table with field 1 set to 1 in record 1, and I execute the
following SQL commands

--Begin Transaction --
UPDATE table1 SET field1=2 WHERE id=1
SELECT field1 FROM table1 WHERE id=1

I would expect this to return 2. However if the SELECT were performed using
a different connection I would expect it to return 1 (unless and until the
transaction is committed).
Things get a little more complicated if you have multiple transactions both
trying to update the same data.

[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...timeou t. So, was just trying to
figure out why that would be happening...

[Mr. Arnold]

"BobRoyAce" <br**@omegasoft wareinc.comwrot e in message
news:9a******** *************** ***********@m45 g2000hsb.google groups.com...

>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...timeou t. So, was just trying to
figure out why that would be happening...

MS.Public.SQLse rver.server