连接池–一个进程–多线程 [英] Connection pool – one process – many threads
问题描述
我对连接池的理解是；如果connectionstring完全相同，那么我们将重用该连接，而不是建立新的连接。
My understanding of the connection pool is; if the connectionstring is the exact same, then we reuse the connection instead of make a new connection.
我的问题是我正在创建许多用于并行处理的线程。在此虚拟程序中，我创建500个线程，并让ThreadPool函数处理线程。
My problem is that I’m creating many threads for parallel processing. In this "dummy" program, I create 500 threads and let the ThreadPool function handle the threads.
步骤是：
-
每个线程在SQL中创建一个更新表。 （记录更新的时间戳）
Each thread make an update table in SQL. (Stating the timestamp for the update)
然后，线程在1到10秒（随机）之间休眠。
Then the thread sleeps between 1 to 10 sec (random).
最后，线程在SQL中进行另一次更新（统计结束时间的时间戳）
At the end the thread makes another update in SQL (Stating the timestamp for the end time)
然后退出线程
class Program
{
static void Main(string[] args)
{
int numberOfThreads = 150;
ThreadPool.SetMinThreads(numberOfThreads, numberOfThreads);
ThreadPool.SetMaxThreads(numberOfThreads, numberOfThreads);
List<Int64> chunkList = new List<Int64>();
int maxNumberOfChunks = 500;
for (int i = 1; i < maxNumberOfChunks; i++)
{
chunkList.Add(i);
}
foreach (Int64 chunk_id in chunkList)
{
ThreadPool.QueueUserWorkItem(new WaitCallback(ThreadWorker), new arguments { chunk_id = chunk_id });
}
Console.ReadLine();
}
static void ThreadWorker(Object stateInfo)
{
arguments arguments = (arguments)stateInfo;
Console.WriteLine("Chunk # : {0} is set to START", arguments.chunk_id);
UpdateSQLdb(arguments.chunk_id, DateTime.Now, null, null, "START", null, null);
Random random = new Random();
int mseconds = random.Next(1, 10) * 1000;
System.Threading.Thread.Sleep(mseconds);
Console.WriteLine("Chunk # : {0} is sleeping for {1} sec.", arguments.chunk_id, mseconds);
Console.WriteLine("Chunk # : {0} ist set to END", arguments.chunk_id);
UpdateSQLdb(arguments.chunk_id, null, DateTime.Now, null, "END", null, null);
}
struct arguments
{
public Int64 chunk_id;
}
static void UpdateSQLdb(Int64 CHUNK_ID, DateTime? START_TS = null, DateTime? END_TS = null, Enum CHUNK_STATUS = null, string error_messages = null, byte? NEW_CALCULATION_ATTEMPTS = null, byte? NEW_POSTPROCESS_ATTEMPTS = null)
{
using (SqlConnection conn = new SqlConnection("Data Source=C55S01;Initial Catalog=MCS_BATCH;Integrated Security=SSPI;Asynchronous Processing=True")) //Timeout=60;Max Pool Size=200;Pooling=True;
{
int result = -1;
conn.Open(); //<-- Each time I open a connection. It creates a new instead of reusing one from the ConnectionPool
try
{
using (SqlCommand cmd = new SqlCommand("TEST.UpdateSQL", conn))
{
cmd.CommandTimeout = 300;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@CHUNK_ID", SqlDbType.BigInt, 15).Value = CHUNK_ID;
cmd.Parameters.Add("@START_TS", SqlDbType.DateTime2, 7).Value = START_TS;
cmd.Parameters.Add("@END_TS", SqlDbType.DateTime2, 7).Value = END_TS;
cmd.Parameters.Add("@ERR_MESSAGE", SqlDbType.VarChar).Value = error_messages;
cmd.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int, 4).Direction = System.Data.ParameterDirection.ReturnValue;
try
{
result = cmd.ExecuteNonQuery();
int return_value = (int)cmd.Parameters["@ReturnValue"].Value;
if (return_value != 0)
{
Console.WriteLine("1. Error in running TEST.UpdateSQL, return value is : {0}", cmd.Parameters["@ReturnValue"].Value);
}
}
catch (SqlException ex)
{
UpdateSQLdb(CHUNK_ID, null, DateTime.Now, null, ex.Message.ToString(), null, null);
Console.WriteLine("2. Error executing TEST.UpdateSQL : {0}", ex);
}
}
}
catch (Exception ex)
{
Console.WriteLine("3.Error in TEST.UpdateSQL : {0}", ex);
throw;
}
if (conn.State == ConnectionState.Open)
{
Console.WriteLine("Closing connection....");
conn.Close();
}
conn.Dispose();
}
}
}
}
我的问题是我未处理 System.InvalidOperationException
（超时过期。在从池中获取连接之前，超时时间已经过去。这可能是因为所有池中的连接都在使用中，并且已达到最大池大小。）
My problem is that I get a System.InvalidOperationException was unhandled (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.)
我已经监视了SQL Server上的连接数，并且很快达到了100个连接（这是池中默认的最大连接数）
I have monitored the number of connections on the SQL server, and It quickly get to 100 connections (which is the default max number of connections in a pool)
如果尝试将以下参数添加到连接字符串中：
Timeout = 60 Max Pool Size = 200; Pooling = True;
If have tried to add these parameters to the connection string : Timeout=60"Max Pool Size=200;Pooling=True;
但是这只会将问题推迟到下一个阶段，因为连接池将达到200，并且超时将在某个时候达到。
But this will just postpone the problem to a later stage, because the connection pool will hit 200 and the timeout will be reach at some point.
问题：为什么要一遍又一遍地创建连接，而不是重复使用
任何提示，技巧或建议都会受到高度赞赏。
Any hint, tips or advice are highly appreciated.
推荐答案
它确实在执行您要求的操作。它正在使用池中的连接，但是您已经做了很多工作。如果您有500个线程和200个连接，则每个线程都无法建立连接。您可能应该具有与线程一样多的连接。
It's doing exactly what you asked it to do. It's using connections from the pool but you've given it too much work. If you have 500 threads and 200 connections each thread can't have a connection. You should probably have as many connections as threads.
如果您还有更多工作要做（所有500个线程都处于繁忙状态），那么您要么将错误返回给消费者或以其他方式限制您对应用程序的输入。
If you have yet more work to do (all 500 threads are busy), then you have to either return an error to the consumer or otherwise throttle the input into your application.
这篇关于连接池–一个进程–多线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
