多线程数据库更新
我有一个 Windows 服务 (C#)，在其中创建多个线程，这些线程将尝试更新数据库 (SQL Server 2005) 中的状态。
我最初有一个 SELECT ，后来必须执行一个 UPDATE 。当我不使用锁来同步我的线程时，我收到错误
已经有一个打开的 DataReader 与此连接关联 必须先关闭它
但是当我使用锁（在静态对象上）时更新非常慢。
谁能帮我解决这个问题。
I have a Windows service (C#) where I create multiple threads which will try to update status in my database (SQL Server 2005).
I have one SELECT initially and an UPDATE later that has to be executed. When I don't use lock to synchronize my threads, I am getting error
There is already an open DataReader associated with this Connection
which must be closed first
But when I use lock (on static object) the update is very slow.
Can anyone help me out in solving this issue .
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您在应用程序中使用的每个命令对象都应该有自己的连接对象，您可以在执行命令之前立即打开该连接对象，并在执行命令之后立即关闭该连接对象。 （最好包含在
using语句中）信任连接池技术来处理实际的底层连接。这将阻止多个线程尝试同时在同一连接上执行多个命令（这就是您所看到的错误所讨论的内容）。
线程之间唯一应该共享的是连接字符串。
Each command object that you're using in your application should have it's own connection object, that you open immediately before executing the command, and close immediately afterwards. (Preferably, wrapped in a
usingstatement)Trust the connection pooling tech to deal with the actual underlying connections. This will stop multiple threads from trying to execute multiple commands on the same connection simultaneously (which is what the error you're seeing is talking about).
The only thing that should be shared between the threads is the connection string.
在尝试将其连接用于其他用途之前，请确保 DataReader 已关闭。我建议您重构代码，这样就不会在线程之间共享连接。如果您可以提供有关您想要实现的目标的更多信息，我也许能够提供有用的代码示例。
Ensure that your DataReader is closed before trying to use it's connection for anything else. What I would suggest is that you refactor your code so that you do not share connections between threads. If you can provide more information on exactly what you are trying to achieve i may be able to provide a useful code sample.
当您在同一个连接上同时打开多个 DataReader 时，即您调用 SqlCommand.ExecuteReader 但在再次调用该方法（无论是在同一个命令上还是在同一连接上的另一个命令上）之前不关闭此方法返回的 SqlDataReader 时，就会发生这种情况。
也可以尝试：
连接链中的
MultipleActiveResultSets=True。This happen when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection).
Also try:
MultipleActiveResultSets=Truein chains of connection.