VB.Net - 我应该为每个线程使用单独的数据库连接 [英] VB.Net - Should I use a separate database connection for each thread

问题描述

我有一个项目可以读取传入的电子邮件。对于每个进入的电子邮件，我创建一个新线程来处理对话。部分线程正在写入数据库表。在测试中，它似乎每隔一段时间都会失败，而EXECUTE行引用了多步问题，但我无法轻易地重现它，我认为这可能会发生，因为两个或多个线程同时运行并尝试写入数据库。我已经读过我可以为所有线程建立一个数据库连接，但我开始认为可能不是这样。 m_cn是主数据库连接。



1.这种情况发生多步错误是否有意义？



2.为每个线程打开一个单独的数据库连接是否更好以保持其自包含？



3.如果是这样，如果一次打开500个连接怎么办，MS SQL会处理这个吗？



以下是发生多步错误的代码：

I have project that reads incoming emails. For each email coming in I create a new thread to process the conversation. Part of the thread is writing to a database table. In testing, it seems to fail every so often and the EXECUTE line citing Multi-step issues, but I cannot reproduce it easily, and I am thinking that this might be happening because two or more threads are running at the same time and trying to write to the database. I have read I can have one database connection for all the threads but I am starting to think that maybe is not the case. The m_cn is the main database connection.

1. Does it make sense that I could get multi-step errors with this occurring?

2. Is it better to open a separate database connection for each thread to keep it self-contained?

3. If so, what if there are 500 connections open at once, will MS SQL handle this?

Here is the code where the multi-step error is happening:

cmd.ActiveConnection = m_cn
cmd.ActiveConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
cmd.Parameters.Append(cmd.CreateParameter("", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 64, sRCPTTo)) ' RFC says 64 character limit
cmd.Parameters.Append(cmd.CreateParameter("", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , bounce.BounceType))
cmd.CommandText = "insert into tblBounceLog (VERPS,BounceType) values (?,?)"
cmd.Execute()

谢谢

Thank you

推荐答案

如果你看一下SQLCOnnection下的MSDN：http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection .aspx [ ^ ]您将看到有关于线程安全的部分：



此类型的任何公共静态（在Visual Basic中共享）成员是线程安全的。任何实例成员都不保证是线程安全的。



这意味着：是的 - 你需要为每个线程提供不同的连接实例。它们是（可能是间歇性的，可调试的）问题的一个秘诀。

If you look at MSDN under SQLCOnnection: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx[^] you will see there is a section on thread safety:

"Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe."

Which means: Yes - you need a different instance of the connection for each thread. Sharing them is a recipe for (possibly intermittent and horrible to debug) problems.

这篇关于VB.Net - 我应该为每个线程使用单独的数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！