CODEWITHSUNDEEP
.netsql-serverconnection-poolingsqlconnection
Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

Will pooled SQL Connections be scavenged if there is still a reference to them?

@usr and I are having a disagreement in another question about whether or not .NET will scavenge open connections that have been idle, but still maintain a held reference.

I maintain, based on documentation, that if the physical connection is idle for a period of time, it will be reclaimed after a period of time even if a SQLConnection object reference remains held.

usr claims that this won't happen, and it only reclaims connections that no longer have references. His assertion as this would make connection pooling unreliable and would cause havoc with transactions.

I will admit, the documentation is vague about the issue of held references.

I'm looking for an authoritative answer on this, not just guessing. Either someone has experimented and proven either case, or someone with knowledge of the internal workings.

So which one is it?

EDIT:

I think the confusion here is with the terminology of "Closed". What appears to be the case is that the documentation refers to closing the physical connection as "Removing" the connection from the pool. While "Closing" refers to releasing the open physical connection back into the pool for re-use.

Open connections can still be aborted, but it's unclear if the pooler marks this as invalid if it's still held as open by the client app (ie client app has not yet called close on the aborted connection) Not sure it really matters though, other than as part of the pool count.

Upvotes: 2

Views: 493

Answers (3)

usr
usr

Reputation: 171178

I just ran the following program:

        var conn = new SqlConnection("...");
        conn.Open();
        conn.ExecuteNonQuery("select null");
        Thread.Sleep(TimeSpan.FromMinutes(11));
        conn.ExecuteNonQuery("select null");

It ran through successfully (using custom helper methods so you cannot directly run it).

This does not prove anything of course (only if it hadn't run correctly). Reflector shows that the pool cleans up the first time after 6.5min and every 30s thereafter:

private Timer CreatePruningTimer()
{
    return new Timer(new TimerCallback(this.PruneConnectionPoolGroups), null, 240000, 30000);
}

So the test should have exposed the problem.

Now, the sentence in the docs is not particularly clear. It does not mention GC or even object references (in your question you say it does, but it doesn't).

The connection pooler periodically scans connection pools looking for unused connections that were not closed by Close or Dispose, and reclaims those it finds. If your application does not explicitly close or dispose of its connections, it can take quite a while for the connection pooler to reclaim them, so it is best to ensure that you explicitly call Close and Dispose in your connections.

I don' trust it. Speaking in Bayesian terms, Microsoft docs are strong evidence, but if there is strong counter evidence one can come to the opposite conclusion.

I have the following evidence that open connections are not closed without reason:

  1. That would inject random failures that are not preventable (only by retry)
  2. It would abort transactions that are just fine
  3. It would make long-running transactions impossible
  4. I cannot see a reason why this behavior should be the case
  5. The sentence in the docs can be interpreted in the following way: The pool periodically returns connections which have not been closed but whose corresponding SqlConnection object has been GC'ed (detecting that by using a WeakReference or something). I guess the Microsoft doc writer meant that, or did not understand the issue (plausible!)
  6. The test did not demonstrate the problem
  7. How does SSMS manage to run hour-long queries? SSMS should be subject to the same limitation

Upvotes: 0

Henk Holterman
Henk Holterman

Reputation: 273179

whether or not .NET will scavenge open connections that have been idle, but still maintain a held reference

The 'held reference' is immaterial here, the point is 'open'. To properly recycle a Connection you need to Dispose() or Close() it.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

I can tell you with 100% certainty that the connection DOES NOT return to the pool until released. I had a session create a temporary table, and left it hanging while waiting for user input. The user comes back after 6 minutes, presses a button and it continues on the same SPID and life goes on against the temporary table.

Applying logic, it makes sense because can you imagine the havoc if you lose the SPID just because you've been idle for X minutes? Should you keep having to "ping" SQL Server with a command just to keep it alive? (it would be insane)

You appear to be confusing 2 concepts. When a Connection is closed, it returns to the pool. If it goes out of scope, it is implicitly closed -> returns to the pool. The reference to "idle" refers to when the connection in the pool becomes idle, not when it's actively referenced and not closed. A held connection isn't even in the pool to become eligible for any type of scavenging. Freeing resources from the pool makes sense, because a web-app can easily burst to 100s of connections, then for the next few months stay at around 20 or so active concurrent connections.

Upvotes: 2

Related Questions