ASP.NET, C#: timeout when trying to Transaction.Commit() to database; potential deadlock?
- by user1843921
I have a web page that has coding structured somewhat as follows:
SqlConnection conX =new SqlConnection(blablabla);
conX.Open();
SqlTransaction tran=conX.BeginTransaction();
try{
SqlCommand cmdInsert =new SqlCommand("INSERT INTO Table1(ColX,ColY) VALUES @x,@y",conX);
cmdInsert.Transaction=tran;
cmdInsert.ExecuteNonQuery();
SqlCommand cmdSelect=new SqlCOmmand("SELECT * FROM Table1",conX);
cmdSelect.Transaction=tran;
SqlDataReader dtr=cmdSelect.ExecuteReader();
//read stuff from dtr
dtr.Close();
cmdInsert=new SqlCommand("UPDATE Table2 set ColA=@a",conX);
cmdInsert.Transaction=tran;
cmdInsert.ExecuteNonQuery();
//display MiscMessage
tran.Commit();
//display SuccessMessage
}
catch(Exception x)
{
tran.Rollback();
//display x.Message
}
finally
{
conX.Close();
}
So, everything seems to work until MiscMessage. Then, after a while (maybe 15-ish seconds?) x.Message pops up, saying that:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
So something wrong with my trans.Commit()? The database is not updated so I assume the trans.Rollback works...
I have read that deadlocks can cause timeouts...is this problem cause by my SELECT statement selecting from Table1, which is being used by the first INSERT statement?
If so, what should I do? If that ain't the problem, what is?
