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?
Best Answer
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:
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.
Correct, because that is then only place where a connection is opened inside the transaction-scope
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:
Note I'm passing the
connection
intoUpdate
in the above; here it is obvious that a single connection will be used (assumingUpdate
works sanely).