.net SqlCommandTimeOut and connection pool

Question

Suppose we execute many different sql commands and SqlCommand.CommandTimeout leave the default value of 30 seconds.

And suppose some of these sql commands are just a long query, and we can get a timeout exception.

Correct me if I am mistaken, this exception was simply the reason .Net no longer wants to wait, but if we use the connection pool, this connection can remain open, so that this sql statement can still work on the SQL Server Side? Or is there some kind of hidden connection between this system to stop it suddenly, regardless of whether we use a connection pool or not?

I just want to know what the mechanism is and whether the performance of the SQL server will affect. I mean, if the request is really long, for example, take 10 minutes, if it still works, it can simply slow down the server, since no one can get the result.

UPDATE

So, here I am asking about the connection pool, specifically that the code will close the connection with exception handling, or we can just assume that they are using code that is a template whose preferred name is @dash. The problem is that I call the Close () or Dispose () method in this SqlConnection object, it returns to the connection pool, it does not physically close it.

I ask when it will return to the pool if this long query will work on the SQL Server side. And if possible, how to avoid it.

UPDATE again

Thanks for @dash mentioning the database transaction, yes, the rollback will make it wait, and we will not close the connection and do not return it to the pool. So what if it is just a long query or update, but only one individual update without any database transaction? And especially, I want to know if there is a way we can tell SQL Server that I don't need the result, please stop it?

Answer 1

It all depends on how you really fulfill your requests;

Submit the following request:

 SqlConnection myConnection = new SqlConnection("connection_string"); SqlCommand myCommand = new SqlCommand(); myCommand.Connection = myConnection; myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandTimeout = some_long_time; myCommand.CommandText = "database_killing_procedure_lol"; myConnection.Open() //Connection now open myCommand.ExecuteNonQuery(); 

Two things will happen; one is that this method will queue until the command completes. ExecuteNonQuery (). Secondly, we will also link the connection from the connection pool throughout the method.

What happens if we cross? Well, an exception is thrown - an SqlException exception with the property Number = -2. However, remember that in the above code there is no exception management, so all that happens is objects that go out of scope, and we will need to wait for them to be deleted. In particular, our connection will not be reused until this happens.

This is one of the reasons why the following pattern is preferred:

 using(SqlConnection myConnection = new SqlConnection("connection_string")) { using(SqlCommand myCommand = new SqlCommand()) { SqlCommand myCommand = new SqlCommand(); myCommand.Connection = myConnection; myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandTimeout = some_long_time; myCommand.CommandText = "database_killing_procedure_lol"; myConnection.Open() //Connection now open myCommand.ExecuteNonQuery(); } } 

This means that as soon as the request is completed, either naturally (it will be completed) or through an exception (timeout or otherwise), the resources will be immediately returned.

In your particular problem, having a large number of queries that take a long time to complete is bad for many reasons. In a web application, you have potentially many users fighting for a limited amount of resources; memory, database connections, processor time, etc. Therefore, linking any of them to expensive operations will reduce the responsiveness and performance of your web application or limit the number of users that you can serve at the same time. If the database is expensive, you can also associate your database with a further restriction on usage.

It is always worth trying to give the runtime of database queries only for this reason. If you cannot, then you will have to be careful how many of these types of queries you can run at the same time.

EDIT:

So, you are really interested in what is happening on the SQL Server side ... the answer ... it depends! CommandTimeout is actually a client event - you say that if the request takes more than n seconds, then I no longer want to wait. SQL Server receives a message that it is, but it still needs to deal with what it is doing now, so it may actually take some time before SQL Server completes the query. He will try to prioritize, but more on that.

This is especially true for transactions; if you are executing a query enclosed in a transaction and you are rolling back this as part of exception management, then you need to wait until the rollback is complete.

It also very often happens that people panic and start issuing KILL commands against the identifier of the SQL process the query is working with. This is often an error if the team is executing a transaction, but is often suitable for lengthy selections.

SQL Server must manage it so that it remains consistent. The fact that the client is no longer listening means that you have wasted your work, but SQL Server still needs to be cleaned up after itself.

So, the ASP.Net side will be all right, as it’s all the same, but SQL Server still needs to finish the job or reach the point where it can refuse this work, or roll back any changes in any transactions that were are open.

This can obviously affect database server performance depending on the query!

Even a long run of SELECT or UPDATE or INSERT outside the transaction should complete. SQL Server will try to abandon it as soon as possible, but only if it is safe. Obviously, for UPDATES and INSERT in particular, it must reach the point at which the database remains unchanged. For SELECT, he will try to finish as soon as he can.

Answer 2

Thanks for @dash mentioning the database transaction, the rollback will make it wait and we will not close the connection and return it to the pool. So what if it is just a long select or update request, but only one individual update without any database transaction? And specifically, I want to know if there is a way that we can tell SQL Server that I don’t need the result now, please stop it?

I think this link will answer the need Link1 Link2

Answer 3

The SqlConnection.ClearPool () method can be wait if you are looking. The following article deals with this.

How to force close SqlConnection when using connection pool?