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.
