即使使用创建的SqlConnection，连接泄漏（C＃/ ADO.NET） [英] Connection leak (C#/ADO.NET) even though SqlConnection created with using

问题描述

我有一个程序可以在线程池上运行的Task中加载大量数据（每次迭代约800K-1M行）（请参见下面的令人讨厌的代码示例）；并发运行的任务不超过4个。这是程序中与此数据库建立连接的唯一位置。在我的笔记本电脑（和其他同事相同的笔记本电脑）上运行程序时，该程序可以正常运行。但是，我们可以通过远程桌面访问另一个工作站，该工作站比我们的笔记本电脑强大得多。该程序在其列表的大约1/3至1/2处失败。所有任务均返回异常。

I have a program that loads a large quantity of data (~800K-1M rows per iteration) in a Task running on the threadpool (see offending code sample below); no more than 4 tasks running concurrently. This is the only place in the program that a connection is made to this database. When running the program on my laptop (and other coworkers identical laptops), the program functions perfectly. However, we have access to another workstation via remote desktop that is substantially more powerful than our laptops. The program fails about 1/3 to 1/2 of the way through its list. All of the tasks return an exception.

第一个例外是：超时到期。在从池中获取连接之前，超时时间已经过去。这可能是因为所有池化连接都在使用中，并且最大池达到了大小。我尝试使用Google搜索，搜索，在StackOverflow上搜索，然后将头撞在桌子上，试图弄清楚情况如何。一次运行的任务不超过4个，那么一次最多只能有4个连接。

The first exception was: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." I've tried googling, binging, searching on StackOverflow, and banging my head against the table trying to figure out how this can be the case. With no more than 4 tasks running at once, there shouldn't be more than 4 connections at any one time.

对此，我尝试了两件事：（1）我在conn.Open（）行周围添加了try / catch，如果出现InvalidOperationException，它将清除池中的内容-看起来很奏效[并没有让它一直运行，但是已经大大超过了以前的水平]，但是却以性能为代价。 （2）我将ConnectionTimeout更改为30秒而不是15秒，这是行不通的（但是让它继续进行一点）。我也曾尝试过一次做ConnectRetryInterval = 4（错误地选择了它而不是ConnectRetryCount）-这导致了另一个错误最大请求数是4,800，这很奇怪，因为我们仍然不应该在4,800附近请求或连接。

In response to this, I tried two things: (1) I added a try/catch around the conn.Open() line that would clear the pool if InvalidOperationException appears--that appeared to work [didn't let it run all the way through, but got substantially past where it did before], but at the cost of performance. (2) I changed ConnectionTimeout to be 30 seconds instead of 15, which did not work (but let it proceed a little more). I also tried at one point to do ConnectRetryInterval=4 (mistakenly choosing this instead of ConnectRetryCount)--this let to a different error "The maximum number of requests is 4,800", which is strange because we still shouldn't be anywhere near 4,800 requests or connections.

简而言之，我很茫然，因为我不知道是什么原因导致了仅在高速计算机上的连接泄漏。我也无法在该计算机上直接调试Visual Studio，非常感谢任何人可能在哪里尝试尝试解决此问题。

In short, I'm at a loss because I can't figure out what is causing this connection leak only on a higher speed computer. I am also unable to get Visual Studio on that computer to debug directly--any thoughts anyone might have on where to look to try and resolve this would be much appreciated.

（跟踪 c＃ TaskFactory ContinueWhenAll在所有任务完成之前意外运行）

private void LoadData()
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = "redacted";
        builder.UserID = "redacted";
        builder.Password = "redacted";
        builder.InitialCatalog = "redacted";
        builder.ConnectTimeout = 30;

        using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
        {
            //try
            //{
            //    conn.Open();
            //} catch (InvalidOperationException)
            //{
            //    SqlConnection.ClearPool(conn);
            //    conn.Open();
            //}
            conn.Open();


            string monthnum = _monthsdict.First((x) => x.Month == _month).MonthNum;
            string yearnum = _monthsdict.First((x) => x.Month == _month).YearNum;

            string nextmonthnum = _monthsdict[Array.IndexOf(_monthsdict, _monthsdict.First((x) => x.Month == _month))+1].MonthNum;
            string nextyearnum = _monthsdict[Array.IndexOf(_monthsdict, _monthsdict.First((x) => x.Month == _month)) + 1].YearNum;

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;

            cmd.CommandText = @"redacted";
            cmd.Parameters.AddWithValue("@redacted", redacted);
            cmd.Parameters.AddWithValue("@redacted", redacted);
            cmd.Parameters.AddWithValue("@redacted", redacted);
            cmd.CommandTimeout = 180;

            SqlDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                Data data = new Data();

                int col1 = reader.GetOrdinal("col1");
                int col2 = reader.GetOrdinal("col2");
                int col3 = reader.GetOrdinal("col3");
                int col4 = reader.GetOrdinal("col4");

                data.redacted = redacted;
                data.redacted = redacted;
                data.redacted = redacted;
                data.redacted = redacted;
                data.redacted = redacted;

                data.Calculate();
                _data.Add(data); //not a mistake, referring to another class variable
            }
            reader.Close();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }

推荐答案

此原来是没有足够仔细地阅读文档的经典案例。我试图使用ThreadPool.SetMaxThreads将最大线程数限制为4，但是最大线程数不能少于处理器的数量。在发生故障的工作站上，它具有8个处理器。因此，没有上限，它可以运行任务计划程序认为合适的任务，最终达到连接池限制。

This turned out to be a classic case of not reading the documentation closely enough. I was trying to cap max Threads at 4, using ThreadPool.SetMaxThreads, but max Threads cannot be less than the number of processors. On the workstation it failed on, it has 8 processors. So, there was never a cap, it was running as many tasks as the Task Scheduler felt appropriate, and it was eventually hitting the Connection Pool limit.

https://docs.microsoft.com/zh-cn/dotnet/api/system。 threading.threadpool.setmaxthreads

这篇关于即使使用创建的SqlConnection，连接泄漏（C＃/ ADO.NET）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！