多线程执行SQL查询报错：已有打开DataReader关联连接的解决方法

嘿，我来帮你搞定这个报错！你遇到的There is already an open DataReader associated with this Connection which must be closed first错误，核心原因是你在两个线程里共享了同一个MySqlConnection和MySqlCommand实例——数据库连接对象本身不是线程安全的，当一个线程还在使用连接时，另一个线程尝试操作它，就会触发这个冲突。

你提到的“数据库连接应尽可能晚打开、早关闭”是非常正确的做法，结合这个原则，我们可以通过以下方式解决问题：

解决方案：每个线程使用独立的连接和命令对象 #

不要把con、cmd这类对象作为类的成员变量共享，而是让每个线程方法内部自己创建、使用、销毁这些对象，确保每个线程的数据库操作完全独立，互不干扰。

修改后的完整代码 #

Imports MySql.Data.MySqlClient
Imports System.Threading
Public Class Form1
    Private server As String = Nothing
    Private pass As String = Nothing
    Private user As String = Nothing
    Private port As String = Nothing
    Private db As String = Nothing
    Dim Thread1 As Thread
    Dim Thread2 As Thread

    Public Sub New()
        Me.server = "localhost"
        Me.user = "root"
        Me.pass = ""
        Me.port = "3306"
        Me.db = "diagnosedb"
        
        Thread1 = New Thread(AddressOf Querie1)
        Thread2 = New Thread(AddressOf Querie2)
        Thread1.Start()
        Thread2.Start()
    End Sub

    Private Sub Querie1()
        ' 每个线程内部独立创建连接和命令
        Using con As New MySqlConnection($"Server = {Me.server}; Port = {Me.port}; Database = {Me.db}; Uid = {Me.user}; Pwd = {Me.pass};")
            Using cmd As New MySqlCommand("UPDATE teileliste SET verschleis = 500 WHERE ID = 1;", con)
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    ' 注意：线程中操作UI需要Invoke，避免跨线程异常
                    Me.Invoke(Sub() MsgBox(ex.Message))
                Finally
                    con.Close()
                End Try
            End Using
        End Using
    End Sub

    Private Sub Querie2()
        ' 每个线程内部独立创建连接和命令
        Using con As New MySqlConnection($"Server = {Me.server}; Port = {Me.port}; Database = {Me.db}; Uid = {Me.user}; Pwd = {Me.pass};")
            Using cmd As New MySqlCommand("UPDATE teileliste SET verschleis = 0 WHERE ID = 20;", con)
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    ' 注意：线程中操作UI需要Invoke，避免跨线程异常
                    Me.Invoke(Sub() MsgBox(ex.Message))
                Finally
                    con.Close()
                End Try
            End Using
        End Using
    End Sub
End Class

关键修改点说明 #

• 使用Using语句：它会自动帮你释放资源，比手动Close更可靠，确保连接和命令对象在使用完后被正确销毁。
• 线程独立实例：每个线程自己创建MySqlConnection和MySqlCommand，彻底避免了共享资源的冲突。
• 跨线程UI操作修复：原来的MsgBox直接在线程中调用会引发跨线程异常，这里用Me.Invoke把UI操作切换到主线程执行。

内容的提问来源于stack exchange，提问作者Toberto