SQLConnection Pooling - 处理 InvalidOperationException

发布于 2024-07-13 18:49:33 字数 476 浏览 7 评论 0原文

我正在设计一个高度并发的 CCR 应用程序，在该应用程序中，我必须不阻止或发送线程休眠。

我遇到了 SQLConnection Pool 问题 - 特别是在尝试调用 SqlConnection.Open 时遇到 InvalidOperationExceptions

我可能会重试很多次，但这并不能真正解决问题。

对我来说，理想的解决方案是定期重新检查连接可用性的方法，不需要占用线程

有什么想法吗？

[更新] 以下是发布在另一个论坛

该解决方案需要手动管理连接池。我宁愿有一个更动态的解决方案，即在需要时启动

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晚风撩人 2024-07-20 18:49:33

Harry，我在使用 CCR 时也遇到过这个问题。我的经验是，将调度程序线程与任何 I/O 上的阻塞完全解耦后，我可以比 SqlConnection 池处理的速度快得多地使用和处理工作项。一旦达到最大池限制，我就遇到了您所看到的那种错误。

最简单的解决方案是预先分配许多非池化异步 SqlConnection 对象并将它们发布到某个中央 Port对象。目的。然后，每当您需要执行命令时，请在迭代器中执行以下操作：

public IEnumerator<ITask> Execute(SqlCommand someCmd)
{
    // Assume that 'connPort' has been posted with some open
    // connection objects.
    try
    {
        // Wait for a connection to become available and assign
        // it to the command.
        yield return connPort.Receive(item => someCmd.Connection = item);

        // Wait for the async command to complete.
        var iarPort = new Port<IAsyncResult>();
        var iar = someCmd.BeginExecuteNonQuery(iarPort.Post, null);
        yield return iarPort.Receive();

        // Process the response.
        var rc = someCmd.EndExecuteNonQuery(iar);
        // ...
    }
    finally
    {
        // Put the connection back in the 'connPort' pool
        // when we're done.
        if (someCmd.Connection != null)
            connPort.Post(someCmd.Connection);
    }
}

使用 Ccr 的好处是，将以下功能添加到这段基本代码中非常简单。

超时 - 只需进行初始接收（针对可用连接），即带有超时端口的“选择”。
动态调整池大小。要增加池的大小，只需将新的打开的 SqlConnection 发布到“connPort”即可。要减小池的大小，请在 connPort 上生成一个接收，然后关闭接收到的连接并将其丢弃。

Harry, I've run into this as well, also whilst using the CCR. My experience was that having completely decoupled my dispatcher threads from blocking on any I/O, I could consume and process work items much faster than the SqlConnection pool could cope with. Once the maximum-pool-limit was hit, I ran into the sort of errors you are seeing.

The simplest solution is to pre-allocate a number of non-pooled asynchronous SqlConnection objects and post them to some central Port<SqlConnection> object. Then whenever you need to execute a command, do so within an iterator with something like this:

public IEnumerator<ITask> Execute(SqlCommand someCmd)
{
    // Assume that 'connPort' has been posted with some open
    // connection objects.
    try
    {
        // Wait for a connection to become available and assign
        // it to the command.
        yield return connPort.Receive(item => someCmd.Connection = item);

        // Wait for the async command to complete.
        var iarPort = new Port<IAsyncResult>();
        var iar = someCmd.BeginExecuteNonQuery(iarPort.Post, null);
        yield return iarPort.Receive();

        // Process the response.
        var rc = someCmd.EndExecuteNonQuery(iar);
        // ...
    }
    finally
    {
        // Put the connection back in the 'connPort' pool
        // when we're done.
        if (someCmd.Connection != null)
            connPort.Post(someCmd.Connection);
    }
}

The nice thing about using the Ccr is that it is trivial to add the following the features to this basic piece of code.

Timeout - just make the initial receive (for an available connection), a 'Choice' with a timeout port.
Adjust the pool size dynamically. To increase the size of the pool, just post a new open SqlConnection to 'connPort'. To decrease the size of the pool, yield a receive on the connPort, and then close the received connection and throw it away.

回复收藏 0 原文