TransactionScope timing out connection on dev virtualbox VM

Question

I have some code thus:

private static void Delete(int PaxID)
    {
        using (SqlConnection conn = DataHelper.GetDBConnection())
        {
            using (SqlCommand cmd = DataHelper.GetSPCommand("spDeletePax",conn))
            {
                cmd.Parameters.AddWithValue("@PaxID", PaxID);
                cmd.ExecuteNonQuery();
            }
        }
    }

    public static void DeletePaxes(List<int> ids, string bookingRef, string user)
    {
        using (TransactionScope ts = new DataHelper.CreateTransactionScope())
        {
            foreach (var i in ids)
            {
                Delete(i);
            }
            ts.Complete();
        }
    }

    public static SqlConnection GetDBConnection()
    {
        SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
        conn.Open();
        return conn;
    }

    public static TransactionScope CreateTransactionScope()
    {
        var transactionOptions = new TransactionOptions();
        transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
        transactionOptions.Timeout = TransactionManager.MaximumTimeout;
        return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
    }

which until recently was working fine I have changed no code, but simply changed my source control from VSS to SVN, and now opened up the project in VS2012 (instead of 2008).

If i call the DeletePaxes(..) the first delete works but the second times out when connecting to the DB

am i just doing this wrong or does 2012/.NET4/4.5 deal with transactions differently? I have done some googling and turned up nothing (hence posting here)

can anyone enlighten me as to what might be going on? am I just doing this wrongly?

DTC issetup so dont think its that - and like i say was working fine until I changed the source control.. also if i change the transaction to just the default - not using the static method, it also fails.. removing the transaction works fine I am using the transaction because I need ALL or NONE of the deletes to work..

thanks

How about using a transaction per call? Since the first works, and the second doesn't, seems like the transaction might be going stale between the operations. — Grant H., Nov 21 '12 at 13:47
? then I may as well not use one, if you mean per call of delete ? — nat, Nov 21 '12 at 13:49
well, you may not want to initiate a new connection with each. Edit - yep, as Oded says. — Grant H., Nov 21 '12 at 13:50
Can you show the exact exception please? Are you saying that you are getting a Connection timeout (not a Command Timeout, or a DTC transaction timeout)? Also, does your sp_deletePax do any transaction work? — StuartLC, Nov 21 '12 at 13:50
it just sits there, am just running again to see what it throws, but no the deletePax does NOT do any transactional stuff, and like i say 3 days ago before changing source control it was working fine - also working fine in dev/test/live, just not when I debug it. if i press pause it is sat on the 2nd conn.open() — nat, Nov 21 '12 at 13:56
I have a suspicion that another connection is holding locks on Pax#2, thus blocking your progress. Have a look see at how to resolve locking ssues : http://stackoverflow.com/questions/906315/how-to-see-sql-2008-locks-and-blocked-tables — StuartLC, Nov 21 '12 at 14:01
it is, completely open now as thought it might be something to do with that. also was working fine — nat, Nov 21 '12 at 14:02

score 0 · Answer 1 · answered Nov 21 '12 at 14:35

OK dont beat me but turns out it was a problem with the VM setup. the network adapter was set to NAT, not Bridged Adapter - and all is well now. guess the transaction was not able to find its way back to the VM and thus is just sat there

may just be me that is foolish enough to forget that but thanks for all the replies anyway - and I will do some reading up on Table-valued Params

TransactionScope timing out connection on dev virtualbox VM

1 Answers1