C＃DataBase.ExecuteScalar中的连接泄漏

The following method in a static class gives me a time out exception because the connection pool is maxed out.

静态类中的以下方法给出了超时异常,因为连接池被最大化。

While in debug mode I looked in sql Management studio and saw there were 150 sleeping processes.

在调试模式下,我查看了sql Management studio,看到有150个睡眠进程。

I expected the connections to be closed automatically...I also tried putting as a static member and still got the same Error.

我希望连接自动关闭...我也试过把它作为一个静态成员,但仍然有相同的错误。

Any Ideas? heres the code:

有任何想法吗?继承人代码:

public static Decimal ExecuteScalarDec(string procName, params object[] parameters)
{
    try
    {
        return (Decimal)DatabaseFactory.CreateDatabase().ExecuteScalar(procName, parameters);
    }
    catch (Exception ex)
    {
        throw new Exception(procName.ToString() + " " + parameters.ToString(), ex);
    }
}

---

"By design, most of the Database class methods handle the opening and closing of connections to the database on each call. Therefore, the application code does not need to include code for managing connections.". ExecuteReader is an exception (because it returns a resource). ExecuteScalar is in limbo: it returns a 'scalar'. However, I guess the scalar can be quite heavy, eg. a Stream constructed from a large datatype return, and that would require to keep the conenction open. – Remus Rusanu

“按照设计,大多数Database类方法都会在每次调用时处理数据库连接的打开和关闭。因此,应用程序代码不需要包含用于管理连接的代码。” ExecuteReader是一个例外(因为它返回一个资源)。 ExecuteScalar处于不确定状态:它返回一个'标量'。但是,我猜标量可能很重,例如。从大数据类型返回构造的流,这将需要保持开放的连接。 - Remus Rusanu

I couldn't comment on your answer because it says "commenting requires 50 reputation" After I registered my user...

我无法评论你的答案,因为它说“评论需要50个声誉”我注册了我的用户后......

I'm returning a column Id in executeScalar() and the value is returned - I know this because the next call to execute scalar is only called after I recieve a value... It doesn't seam to make sense that the stream will remain open forever And I saw in sql Management that all the processes are sleeping.

我在executeScalar()中返回一个列Id并且返回了值 - 我知道这是因为下一次执行标量的调用仅在我收到一个值后被调用...它没有接合来理解流将会永远保持开放我在sql Management中看到所有进程都在休眠。

1 个解决方案

#1

public static Decimal ExecuteScalarDec(string procName, params object[] parameters)
{
    try
    {
        using (Database database = DatabaseFactory.CreateDatabase())
        {
            return (Decimal)database.ExecuteScalar(procName, parameters);
        }
    }
    catch (Exception ex)
    {
        throw new Exception(procName.ToString() + " " + parameters.ToString(), ex);
    }
}

Update

OK, since this is EnterpriseLibrary code. The Database class implements ExecuetScalar like this (other signatures will collapse to this eventually):

好的,因为这是EnterpriseLibrary代码。 Database类像这样实现ExecuetScalar(其他签名最终将崩溃到此):

 public virtual object ExecuteScalar(DbCommand command)
        {
            if (command == null) throw new ArgumentNullException("command");

            using (ConnectionWrapper wrapper = GetOpenConnection())
            {
                PrepareCommand(command, wrapper.Connection);
                return DoExecuteScalar(command);
            }
        }

and the ConnectionWrapper disposes the connection (end of source file in link) so, the theory goes, your call should be OK and dispose the connection.

并且ConnectionWrapper处理连接(链接中源文件的结尾),理论上,你的调用应该没问题并处理连接。

The GetOpenConnection() method returns a wrapper that does dispose the connection... except if one exists in the current TransactionScopeConnections:

GetOpenConnection()方法返回一个处理连接的包装器...除非当前的TransactionScopeConnections中存在一个连接:

 protected ConnectionWrapper GetOpenConnection(bool disposeInnerConnection)
    {
        DbConnection connection = TransactionScopeConnections.GetConnection(this);
        if (connection != null)
        {
            return new ConnectionWrapper(connection, false);
        }

        return new ConnectionWrapper(GetNewOpenConnection(), disposeInnerConnection);
    }

And here is how TransactionScopeConnections returns the connection:

以下是TransactionScopeConnections返回连接的方式:

  public static DbConnection GetConnection(Database db)
    {
        Transaction currentTransaction = Transaction.Current;

        if (currentTransaction == null)
            return null;

        Dictionary<string, DbConnection> connectionList;
        DbConnection connection;

        lock (transactionConnections)
        {
            if (!transactionConnections.TryGetValue(currentTransaction, out connectionList))
            {
                // We don't have a list for this transaction, so create a new one
                connectionList = new Dictionary<string, DbConnection>();
                transactionConnections.Add(currentTransaction, connectionList);

                // We need to know when this previously unknown transaction is completed too
                currentTransaction.TransactionCompleted += OnTransactionCompleted;
            }
        }

        lock (connectionList)
        {
            // Next we'll see if there is already a connection. If not, we'll create a new connection and add it
            // to the transaction's list of connections.
            // This collection should only be modified by the thread where the transaction scope was created
            // while the transaction scope is active.
            // However there's no documentation to confirm this, so we err on the safe side and lock.
            if (!connectionList.TryGetValue(db.ConnectionString, out connection))
            {
                // we're betting the cost of acquiring a new finer-grained lock is less than 
                // that of opening a new connection, and besides this allows threads to work in parallel
                connection = db.GetNewOpenConnection();
                connectionList.Add(db.ConnectionString, connection);
            }
        }

        return connection;
    }

Now unless I'm mistaken, the TransactionsScopeConnections will always create a fresh conenction for a brand new Database object (as in your case) and keep them in it internal dictionary. The Database object does not implement Disposable, so I'm lost in determining who exactly is supposed to clean up the connections from this TransactionScopeConnecitons internal list.

现在除非我弄错了,TransactionsScopeConnections将始终为全新的数据库对象创建一个新的联系(如你的情况),并将它们保存在内部字典中。 Database对象没有实现Disposable,所以我在确定谁应该从这个TransactionScopeConnecitons内部列表清理连接时迷失了。

Matt, is it possible to follow the steps in this article about CLR leaks and see if there are large numbers of Database objects in your process? Load SOS and do a !dumpheap -type Microsoft.Practices.EnterpriseLibrary.Data.Database. If you find many objects, can you trace the pin stack on some of them with !gcroot <AddressOfObject>

Matt,是否可以按照本文中有关CLR泄漏的步骤进行操作,并查看流程中是否有大量的Database对象?加载SOS并执行!dumpheap -type Microsoft.Practices.EnterpriseLibrary.Data.Database。如果找到很多对象,可以使用!gcroot 跟踪其中一些对象的引脚堆栈