By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

SELECT statement within transaction

P: n/a
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.GRPConnectionS tring)
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?
Aug 11 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Bob,

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

Cor

"BobRoyAce" <br**@omegasoftwareinc.comschreef in bericht
news:70**********************************@79g2000h sk.googlegroups.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.SqlConnection(My.Settings.GRPConnectionS tring)
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?
Aug 11 '08 #2

P: n/a
BobRoyAce <br**@omegasoftwareinc.comwrote:
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
Aug 11 '08 #3

P: n/a

"BobRoyAce" <br**@omegasoftwareinc.comwrote in message
news:70**********************************@79g2000h sk.googlegroups.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.SqlConnection(My.Settings.GRPConnectionS tring)
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.

Aug 11 '08 #4

P: n/a
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.GRPConnectionS tring)
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?
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.
Aug 11 '08 #5

P: n/a
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...
Aug 11 '08 #6

P: n/a

"BobRoyAce" <br**@omegasoftwareinc.comwrote in message
news:9a**********************************@m45g2000 hsb.googlegroups.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...timeout. So, was just trying to
figure out why that would be happening...
MS.Public.SQLserver.server

Aug 11 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.