使用Mutex多线程时Form1进度条无法实时更新的问题

我在向MS Access数据库写入约8500条数据时，将DataTable拆分为每批1000条，用Mutex多线程处理来提升速度。但当前存在两个问题：

1. 进度条仅在countdownEvent.Wait()执行完成后才更新
2. 线程运行期间窗体及所有控件无响应，无法执行最小化等操作

已尝试用BackgroundWorker做进度上报，但未达到预期效果，相关代码如下：

按钮点击事件代码 #

Private Sub PrepareData_Click(sender As Object, e As EventArgs) Handles PrepareData.Click
        PrepareData.BackColor = Color.SlateGray
        Data_Preperation_Tasks()
End Sub

BackgroundWorker初始化与进度更新代码 #

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Initialize the BackgroundWorker
        bgWorker.WorkerReportsProgress = True
        bgWorker.WorkerSupportsCancellation = True
        bgWorker.RunWorkerAsync()
End Sub

Private Sub bgWorker_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles bgWorker.ProgressChanged
        If Me.InvokeRequired Then
            Me.Invoke(Sub()
                          Me.ProgressBar1.Value = e.ProgressPercentage
                      End Sub)
            Return
        Else
            ProgressBar1.Value = e.ProgressPercentage
            ProgressBar1.Refresh()
        End If
End Sub

数据准备任务代码 #

Private Sub Data_Preperation_Tasks()
    'other code here that checks things

          Call Write_ProductDetails_ToDatabase_MutexMethod()
End Sub

Module1中的批量写入实现代码 #

Module Module1

 Private Sub UpdateProgressBar(ByVal value As Integer)
        If Form1.IsHandleCreated Then
            Form1.Invoke(Sub()
                             Form1.ProgressBar1.Value = value
                             Form1.ProgressBar1.Refresh()
                         End Sub)
        Else
            Form1.ProgressBar1.Value = value
            Form1.ProgressBar1.Refresh()
        End If
    End Sub

    Sub Write_ProductDetails_ToDatabase_MutexMethod()

        Form1.Action3.Text = "Writing Product Details To Database"
        Form1.Action3.BackColor = Color.Orange
        Form1.Action3.Visible = True

        Dim sAppPath As String
        sAppPath = System.Windows.Forms.Application.StartupPath

        Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath & "\BatchNumberUpload.MDB"
        Dim mutexName As String = "database_mutex"
        Dim mutex As Mutex = New Mutex(False, mutexName)

        Dim batchSize As Integer = 1000
        Dim threadCount As Integer = (InventoryTable.Rows.Count + batchSize - 1) \ batchSize ' Round up to the nearest integer
        Dim countdownEvent As CountdownEvent = New CountdownEvent(threadCount)

        Dim rowCount As Integer = 0 ' Keep track of the number of rows that have been written so far
        Dim totalRowCount As Integer = InventoryTable.Rows.Count

        Form1.ProgressBar1.Minimum = 0
        Form1.ProgressBar1.Maximum = totalRowCount
        Form1.ProgressBar1.Visible = True
       
        'Start the BackgroundWorker
        Form1.bgWorker.RunWorkerAsync()

        For i As Integer = 0 To InventoryTable.Rows.Count - 1 Step batchSize
            Dim batchRows = InventoryTable.Rows.Cast(Of DataRow)().Skip(i).Take(batchSize)

            ' Create a new thread for each batch of rows
            Dim thread = New Thread(Sub()
                                        ' Synchronize access to the database connection using a Mutex
                                        mutex.WaitOne()
                                        Try
                                            Using connection = New OleDbConnection(connectionString)
                                                Using command = New OleDbCommand()
                                                    command.Connection = connection
                                                    connection.Open()

                                                    For Each row In batchRows
                                                        Dim constring As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath & "\BatchNumberUpload.MDB"

                                                        Using con As New OleDbConnection(constring)

                                                            Using cmd As New OleDbCommand("INSERT INTO " & "`" & "BatchNumbers" & "`" & " Values(@BatchNumber,@ProductCode,@FirstLine,@SecondLine,@RRP,@SalePrice,@SaleEndDate,@Barcode)", con)


                                                                cmd.Parameters.AddWithValue("@BatchNumber", row.Item("BatchNumber"))
                                                                cmd.Parameters.AddWithValue("@ProductCode", row.Item("ProductCode"))
                                                                cmd.Parameters.AddWithValue("@FirstLine", row.Item("FirstLine"))
                                                                cmd.Parameters.AddWithValue("@SecondLine", row.Item("SecondLine"))
                                                                cmd.Parameters.AddWithValue("@RRP", row.Item("RRP"))
                                                                cmd.Parameters.AddWithValue("@SalePrice", row.Item("SalePrice"))
                                                                cmd.Parameters.AddWithValue("@SaleEndDate", row.Item("SaleEndDate"))
                                                                cmd.Parameters.AddWithValue("@Barcode", row.Item("Barcode"))
                                                                con.Open()

                                                                cmd.ExecuteNonQuery()

                                                                con.Close()

                                                            End Using

                                                        End Using
                                                        rowCount += 1 ' Increment the number of rows that have been written so far
                                                        If rowCount Mod 10 = 0 Then
                                                            Dim progressPercentage As Integer = CInt(rowCount * 100 / totalRowCount)
                                                            Form1.bgWorker.ReportProgress(progressPercentage) ' Report progress to the background worker
                                                        End If

                                                    Next
                                                End Using
                                            End Using
                                        Finally
                                            mutex.ReleaseMutex()
                                            countdownEvent.Signal() ' Signal that the thread has completed
                                        End Try
                                    End Sub)
            thread.Start()
        Next

        ' Wait for all threads to complete before proceeding to the next subroutine
        countdownEvent.Wait()

        UpdateProgressBar(totalRowCount)

        Form1.Action3.Text = "Completed Writing To Database"
        Form1.Action3.BackColor = Color.LimeGreen
        Form1.Action3Tick.Visible = True

        Form1.TimeRemaining.Text = "Completed"

    End Sub
End Module

问题根源与修复方案 #

1. 窗体无响应的核心原因 #

按钮点击事件直接在UI线程执行Data_Preperation_Tasks()，而countdownEvent.Wait()会阻塞UI线程，导致窗体无法处理消息（如最小化、重绘）。

2. 进度条不实时更新的原因 #

• 共享变量rowCount被多线程同时修改，计数不准确
• BackgroundWorker初始化时机错误，Form加载时就启动，后续重复调用RunWorkerAsync()引发冲突
• Mutex的使用完全抵消多线程优势，所有线程串行执行，且数据库操作冗余（每行都新建连接）

具体修复步骤 #

步骤1：重构BackgroundWorker调用逻辑

移除Form_Load中的bgWorker.RunWorkerAsync()，改为在按钮点击时启动任务，避免阻塞UI线程：

Private Sub PrepareData_Click(sender As Object, e As EventArgs) Handles PrepareData.Click
        PrepareData.BackColor = Color.SlateGray
        PrepareData.Enabled = False ' 禁用按钮防止重复触发
        bgWorker.RunWorkerAsync() ' 在后台线程执行写入任务
End Sub

Private Sub bgWorker_DoWork(sender As Object, e As DoWorkEventArgs) Handles bgWorker.DoWork
        Data_Preperation_Tasks() ' 后台线程执行数据写入
End Sub

Private Sub bgWorker_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
        PrepareData.Enabled = True ' 任务完成后恢复按钮状态
        PrepareData.BackColor = SystemColors.Control
End Sub

步骤2：修复共享变量线程安全问题

用Interlocked.Increment原子操作修改rowCount，避免多线程竞争：

' 替换原rowCount +=1
Interlocked.Increment(rowCount)

步骤3：优化数据库操作，提升写入速度

Access不支持多线程并发写入，去掉Mutex，改用单线程批量插入（比多线程串行效率更高）：

' 替换原批量写入逻辑
Using connection = New OleDbConnection(connectionString)
    connection.Open()
    Using transaction = connection.BeginTransaction() ' 用事务提升批量写入速度
        Using cmd = New OleDbCommand("INSERT INTO BatchNumbers Values(@BatchNumber,@ProductCode,@FirstLine,@SecondLine,@RRP,@SalePrice,@SaleEndDate,@Barcode)", connection, transaction)
            ' 预先定义参数，避免重复创建
            cmd.Parameters.Add("@BatchNumber", OleDbType.VarChar)
            cmd.Parameters.Add("@ProductCode", OleDbType.VarChar)
            cmd.Parameters.Add("@FirstLine", OleDbType.VarChar)
            cmd.Parameters.Add("@SecondLine", OleDbType.VarChar)
            cmd.Parameters.Add("@RRP", OleDbType.Currency)
            cmd.Parameters.Add("@SalePrice", OleDbType.Currency)
            cmd.Parameters.Add("@SaleEndDate", OleDbType.Date)
            cmd.Parameters.Add("@Barcode", OleDbType.VarChar)

            For Each row In batchRows
                ' 批量赋值参数
                cmd.Parameters("@BatchNumber").Value = row.Item("BatchNumber")
                cmd.Parameters("@ProductCode").Value = row.Item("ProductCode")
                cmd.Parameters("@FirstLine").Value = row.Item("FirstLine")
                cmd.Parameters("@SecondLine").Value = row.Item("SecondLine")
                cmd.Parameters("@RRP").Value = row.Item("RRP")
                cmd.Parameters("@SalePrice").Value = row.Item("SalePrice")
                cmd.Parameters("@SaleEndDate").Value = row.Item("SaleEndDate")
                cmd.Parameters("@Barcode").Value = row.Item("Barcode")
                
                cmd.ExecuteNonQuery()

                ' 原子更新计数并上报进度
                Dim currentRow = Interlocked.Increment(rowCount)
                If currentRow Mod 10 = 0 Then
                    Dim progressPercentage = CInt(currentRow * 100 / totalRowCount)
                    bgWorker.ReportProgress(progressPercentage)
                End If
            Next
            transaction.Commit()
        End Using
    End Using
End Using

步骤4：移除阻塞UI的countdownEvent.Wait()

因为整个任务在BackgroundWorker的后台线程执行，无需在UI线程等待，任务完成后的UI更新放到bgWorker_RunWorkerCompleted事件中：

Private Sub bgWorker_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
        PrepareData.Enabled = True
        PrepareData.BackColor = SystemColors.Control
        ' 更新完成状态
        Action3.Text = "Completed Writing To Database"
        Action3.BackColor = Color.LimeGreen
        Action3Tick.Visible = True
        TimeRemaining.Text = "Completed"
        ProgressBar1.Value = ProgressBar1.Maximum
End Sub

步骤5：简化进度更新逻辑

BackgroundWorker的ProgressChanged事件本身就在UI线程触发，无需再判断InvokeRequired：

Private Sub bgWorker_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles bgWorker.ProgressChanged
        ProgressBar1.Value = e.ProgressPercentage
        ProgressBar1.Refresh()
End Sub

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