SQL Connection Won't Run During Transaction

Question

I've got 2 scenarios both running the same code and one works 100% of the time while the other fails just as often.

I have 2 sql connections pointing to the same sql server locally while a transaction is going on. When I try to run a query against the second connection, it times out. While debugging, I can even go into SSMS and try to run a query and it will just sit there as well. The fact that it works in the other scenario is odd, but ultimately I'm trying to figure out why sql is locked up when I'm running the other sqlconnection.

Does anyone have any ideas on what could cause this or any debugging tools I could use? I'm not interested in using a transactionScope.

If I close the first connection beforehand, the second connection's query will run just fine.

Here is when the issue happens. At this point we have sqlconnection1 that has a transaction attached and running:

                    SqlConnection connection = new SqlConnection(ConnectionString);
                    connection.Open();

                    SqlCommand command = new SqlCommand("", connection);


                    command.CommandText = "a read command"
                    int  count = command.ExecuteScalar();

Answer 1

As discussed, first thing is that if you don't need two connections you can just use one so everything runs in the same transaction.

If you need this set of SQL run in parallel with other processes or threads running similar queries, you may want to revisit the isolation and locking of your statements.

You are probably running the first transaction as Serializable isolation and doing updates on it. Consider using ReadCommited (or Snapshot ReadCommited). But that would depend on your actual work.

From SSMS, try running first

SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED

That should avoid the locks created by fist transaction, but this is just so you can see why its happening, is not a good idea to use that isolation level!