Creating a transaction scope with new SqlConnection within existing SqlConnection using block

Question

I want to execute a single SELECT query followed by a sequence of UPDATE queries (all on the same table); UPDATE is implemented in a separate method which is called repeatedly. If one of the UPDATE queries fail, I want them all to fail/rollback - so I want to enlist them in a transaction. However, I'm unsure where SqlConnection should be opened to avoid any issues. My current implementation looks like this:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // execute a single SELECT here

    using (TransactionScope scope = new TransactionScope())
    {
        for (int i=0; i<...; i++)
        {
            Update(); // UPDATE query
        }

        scope.Complete();
    }
}


Update()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        // execute a single UPDATE here
    }
}

Should this work as expected in all situations?

Is it OK to open a connection before SELECT, then open a new connection in Update() method? Due to connection pooling, the same connection will be used for both SELECT and UPDATE queries (connectionString is the same), but only UPDATE queries will be enlisted in the transaction, right? But what happens if different connection gets used in Update()? Will all UPDATE queries still enlist in a transaction as expected and execute atomically?

If I understand things correctly, creating a transaction scope after closing the first connection (after the using block which executes SELECT) would still work, but would reduce performance, because connection would be closed and needed to be re-opened, correct? Or is in fact a new connection created for the transaction scope, and gets opened and closed every time Update() is called?

Answer 1

Due to connection pooling, the same connection will be used for both SELECT and UPDATE queries

No it won't; it can only do that if you release the connection back into the pool first - currently you have two connections concurrently, so it can't possibly be the same thing. You need to restructure a bit:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // execute a single SELECT here
} // <==== end the first connection

using (TransactionScope scope = new TransactionScope())
{
    for (int i=0; i<...; i++)
    {
        Update(); // UPDATE query
    }

    scope.Complete();
}

creating a transaction scope after closing the first connection (...) but would reduce performance, because connection would be closed and needed to be re-opened, correct

No; when you "close" it, you are actually just releasing it back to the pool; it doesn't close the underlying connection (unless you have pooling disabled). Closing (or at least, disposing) is normal and expected.

but only UPDATE queries will be enlisted in the transaction, right?

Correct, because that is then only place where a connection is opened inside the transaction-scope

But what happens if different connection gets used in Update()? Will all UPDATE queries still enlist in a transaction as expected and execute atomically?

Any connections that support enlisting (assuming it isn't disabled in the connection string) will be enlisted. However, depending on the server this may use LTM or may use DTC. If you want to be sure about your connections: take control of them:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // execute a single SELECT here
} // <==== end the first connection

using (TransactionScope scope = new TransactionScope())
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    for (int i=0; i<...; i++)
    {
        Update(connection); // UPDATE query
    }

    scope.Complete();
}

Note I'm passing the connection into Update in the above; here it is obvious that a single connection will be used (assuming Update works sanely).