SQL timeout expired when it should not

Question

I use the SqlConnection class and run problems with expiring the command.

First, I use the SqlCommand property to set the command timeout as follows:

command.CommandTimeout = 300; 

In addition, I found that the Runtime Timeout parameter is set to 0 to ensure that there are no timeouts on the SQL management side.

Here is my code:

 using (SqlConnection conn = new SqlConnection(connection)) { conn.Open(); SqlCommand command = conn.CreateCommand(); var transaction = conn.BeginTransaction("CourseLookupTransaction"); command.Connection = conn; command.Transaction = transaction; command.CommandTimeout = 300; try { command.CommandText = "TRUNCATE TABLE courses"; command.ExecuteNonQuery(); List<Course> courses = CourseHelper.GetAllCourses(); foreach (Course course in courses) { CourseHelper.InsertCourseLookupRecord(course); } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); Log.Error(string.Format("Unable to reload course lookup table: {0}", ex.Message)); } } 

I installed the registration and can check exactly 30 seconds after disabling this function, I get the following error message in my stack trace:

 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 

In the interest of full disclosure: InsertCourseLookupRecord() , found inside the above statements using foreach, executes another query to the same table in the same database. Here is the query that it executes:

 INSERT INTO courses(courseid, contentid, name, code, description, url, metakeywords, metadescription) VALUES(@courseid, @contentid, @name, @code, @description, @url, @metakeywords, @metadescription)" 

This table contains over 1,400 entries.

I certify any person (s) who will help me resolve this as the greatest great wizard.

Answer 1

I believe that you are experiencing a deadlock because of which your request in the InsertCourseLookupRecord() function fails. You are not passing your connection with InsertCourseLookupRecord() , so I assume that you are running this in a separate connection. So what happens:

• You have started a transaction.
• You crop the table.
• InsertCourseLookupRecord starts another connection and tries to insert data into this table, but the table is locked because the transaction is not yet complete.
• The connection in the InsertCourseLookupRecord () function expires when the timeout value specified for this connection is 30 seconds.

You can change the function to accept the command object as a parameter and use it inside the function instead of creating a new connection. Then it will become part of the transaction and will be executed together.

To do this, change the function definition to:

 public static int InsertCourseLookupRecord(string course, SqlCommand cmd) 

Derive all connection code from the code, because you are going to use a CMD object. Then, when you are ready to fulfill your request:

 myCommand.Parameters.Clear(); //need only if you're using command parameters cmd.CommandText = "INSERT BLAH BLAH BLAH"; cmd.ExecuteNonQuery(); 

It will work under the same connection and transaction context.

You name it in your used block:

 CourseHelper.InsertCourseLookupRecord(course, command); 

You can also just take the code in InsertCourseLookupRecord and put it inside the for loop, and then reuse the command object in your use block without passing it to the function.

Answer 2

Since you are using two separate SqlConnection objects, you are blocking your self due to the SqlTransaction that you started in your external code. The request to InsertCourseLookupReacord and, possibly, to GetAllCourses blocked by calling TRUNCATE TABLE courses , which has not yet been set. They wait 300 seconds for the truncation to complete, and then time out.

You have several options.

• Pass SqlConnection and SqlTransaction to GetAllCourses and InsertCourseLookupRecord so that they can be part of the same transaction.
• Use the "ambient transaction" by getting rid of SqlTransaction and using System.Transaction.TransactionScope . This causes all connections open to the server to have a common transaction. This can cause maintenance problems depending on what the requests are doing, as it may be necessary to call the Distributed Transaction Coordinator , which may be disabled on some computers (due to the appearance, what you showed you will need DTC because at the same time you there are two open connections).

The best option is to try changing your code to make option 1, but if you cannot make option 2.

Answer 3

Adapted from the documentation :

CommandTimeout has no effect when a command is executed against a context connection (SqlConnection opened with "context connection = true" in the connection string)

Please view the connection string, this is the only opportunity I can think of.