具有多线程服务的数据库连接池 [英] Database connection pooling with multi-threaded service

问题描述

我有一个使用TPL库进行线程化的.NET 4 C#服务.我们最近将其切换为也使用连接池，因为一个连接已成为处理瓶颈.

I have a .NET 4 C# service that is using the TPL libraries for threading. We recently switched it to also use connection pooling, since one connection was becoming a bottle neck for processing.

以前，我们使用过lock子句来控制连接对象上的线程安全.随着工作的备份，队列将作为任务存在，并且许多线程(任务)将在lock子句中等待.现在，在大多数情况下，线程等待数据库IO并以更快的速度处理工作进程.

Previously, we were using a lock clause to control thread safety on the connection object. As work would back up, the queue would exist as tasks, and many threads (tasks) would be waiting on the lock clause. Now, in most scenarios, threads wait on database IO and work processes MUCH faster.

但是，由于我正在使用连接池，因此我们遇到了一个新问题.一旦达到最大连接数(默认为100)，如果请求进一步的连接，则会发生超时(请参见

However, now that I'm using connection pooling, we have a new issue. Once the max number of connections is reached (100 default), if further connections are requested, there is a timeout (see Pooling info). When this happens, an exception is thrown saying "Connection request timed out".

我所有的IDisposable都在using语句内，并且我正在正确地管理我的连接.之所以发生这种情况，是因为请求的工作量超出了池的处理能力(这是预期的).我了解为什么会引发此异常，并且知道处理该异常的方法.一个简单的重试感觉就像是黑客.我也意识到我可以通过连接字符串来增加超时时间，但是这并不是一个可靠的解决方案.在以前的设计中(无池)，由于应用程序内的锁定，工作项将得到处理.

All of my IDisposables are within using statements, and I am properly managing my connections. This scenario happens due to more work being requested than the pool can process (which is expected). I understand why this exception is thrown, and am aware of ways of handling it. A simple retry feels like a hack. I also realize that I can increase the timeout period via the connection string, however that doesn't feel like a solid solution. In the previous design (without pooling), work items would process because of the lock within the application.

处理这种情况以确保所有工作都得到处理的好方法是什么?

What is a good way of handling this scenario to ensure that all work gets processed?

推荐答案

另一种方法是使用

Another approach is to use a semaphore around the code that retrieves connections from the pool (and, hopefully, returns them). A sempahore is like a lock statement, except that it allows a configurable number of requestors at a time, not just one.

类似的事情应该做:

//Assuming mySemaphore is a semaphore instance, e.g. 
// public static Semaphore mySemaphore = new Semaphore(100,100);
try {
  mySemaphore.WaitOne(); // This will block until a slot is available.
  DosomeDatabaseLogic();
} finally {
  mySemaphore.Release();
}

这篇关于具有多线程服务的数据库连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！