C# Parallel.ForEach批量发邮件更新DB死锁脏读问题咨询

你遇到的死锁、脏读、更新丢失、并行抛错，本质是四个代码设计缺陷叠加导致的：

• 跨线程共享非线程安全的数据库对象：SqlConnection、SqlTransaction本身不支持多线程并发访问，你原有代码中多个并行任务复用同一个连接、同一个事务，会直接导致连接状态混乱、事务提交异常、脏读，这也是你加应用层锁之后抛出并行通用错误的核心原因。
• 全局临时表逻辑制造无意义锁冲突：Update方法中所有并行任务都对全局共享的[example].[MailTemp]表执行删表、批量写入操作，多个事务同时争抢同一张表的排他锁，死锁是必然结果。
• 数据库访问顺序不固定：不同任务更新表的顺序如果出现交叉，很容易形成循环锁等待，触发死锁。
• 锁粒度过大：全表删除、无索引更新会触发表锁而非行锁，进一步放大冲突概率。
  你之前把并行度降到2只是降低了冲突概率，没有解决根本问题，并发量上来后问题依然会复现。

按以下步骤调整即可保留并行发邮件的性能（性能损耗<5%），同时彻底解决死锁、脏读、更新丢失问题：

1. 遵守数据库连接使用规范 #

• 每个并行任务必须创建独立的SqlConnection、独立的SqlTransaction，绝对不能跨线程复用连接、事务对象。数据库连接池本身会自动做底层连接复用，手动共享连接不会提升性能，只会带来线程安全问题。
• 事务范围尽可能缩小：不要把SMTP发邮件的逻辑包在数据库事务里，只在邮件发送成功后开启事务执行更新，更新完立刻提交，缩短锁持有时间。
• 所有SQL语句改用参数化写法，避免SQL注入的同时，还能让数据库缓存执行计划提升性能。

2. 删除全局共享临时表逻辑 #

原有代码中用全局MailTemp表做单条记录更新中转的逻辑是死锁最大诱因，直接删除：

• 单条邮件记录更新直接按主键写UPDATE语句即可，每个任务更新不同Id的邮件记录，只会触发行锁，不会和其他任务冲突。
• 如果确实需要批量操作提升性能，给每个任务创建SQL Server会话级临时表（表名以#开头，数据库会自动隔离不同会话的同名临时表，不会产生冲突），不要用普通全局用户表做临时中转。

3. 固定所有任务的数据库操作顺序 #

所有并行任务必须严格按照相同顺序访问表、更新记录，从根源避免循环等待：

• 第一步：按主键更新员工通知表，更新对应员工的NotificationSent标记，仅锁当前员工对应的单行记录
• 第二步：按主键更新MailMessage邮件记录表，仅锁当前邮件对应的单行记录
• 两个操作放在同一个独立事务中，执行完立刻提交

4. 加轻量死锁重试机制 #

哪怕锁逻辑设计再完善，极端场景下还是可能出现偶发死锁，给数据库更新操作加最多3次重试即可，重试间隔逐次递增（第一次等100ms，第二次等200ms，第三次等500ms），对整体性能几乎无影响。

5. 不要用C#层lock控制数据库访问 #

C#的lock只能控制当前进程内的线程执行顺序：

• 多实例部署场景下完全无效
• 锁粒度过大会直接把并行操作退化成串行，损失性能
  数据库本身的行锁机制比应用层锁效率高得多，不需要在应用层额外加锁控制数据库访问。

var options = new ParallelOptions();
// 并行度可根据SMTP服务器、数据库承载能力调整，3-5都可，不需要降到2
options.MaxDegreeOfParallelism = 4;

Parallel.ForEach(listEmployees, options, item =>
{
    MailMessage mail = new MailMessage()
    {
        ExecutionTaskId = 2,
        From = "example@example.com",
        To = item.email,
        BodyDesc = template.Body,
        SubjectDesc = template.Subject,
        Status = 0,
        CreatedBy = item.persNbr,
        CreatedDate = DateTime.UtcNow,
    };
    SendMail(mail, item);
});

private static void SendMail(MailMessage item, EmployeeInfo emp)
{
    System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
    // 省略邮件内容组装逻辑
    msg.To.Add(emp.email);
    msg.Body = item.BodyDesc;
    msg.BodyEncoding = System.Text.Encoding.UTF8;
    msg.From = new System.Net.Mail.MailAddress("example@example.com");

    using (var smtpClient = new System.Net.Mail.SmtpClient(SettingsRepository.GetSetting("WEB_SMTP")))
    {
        smtpClient.Send(msg);
        item.Status = 1;
        item.SentDate = DateTime.Now;

        // 带死锁重试的更新逻辑
        int retryCount = 0;
        while (retryCount < 3)
        {
            try
            {
                // 每个任务新建独立连接，由连接池自动做底层复用
                using (var conn = new SqlConnection(SettingsRepository.GetSetting("DB_CONNECTION")))
                {
                    conn.Open();
                    using (var trans = conn.BeginTransaction())
                    {
                        // 固定操作顺序：先更新员工通知标记
                        if (item.ObjectTable.Contains("var_control"))
                        {
                            UpdatePayslipNotification(emp.Id, conn, trans);
                        }
                        else
                        {
                            UpdateP60Notification(emp.Id, conn, trans);
                        }
                        // 再更新邮件记录
                        UpdateMailMessage(item, conn, trans);
                        trans.Commit();
                    }
                }
                break;
            }
            catch (SqlException ex) when (ex.Number == 1205) // 1205是SQL Server死锁固定错误号
            {
                retryCount++;
                if (retryCount >= 3) throw;
                Thread.Sleep(retryCount * 100);
            }
        }
    }
}

// 所有更新方法传入当前任务独立的连接和事务，禁止使用全局共享的连接/事务
private static void UpdatePayslipNotification(int empId, SqlConnection conn, SqlTransaction trans)
{
    const string sql = @"Update [example].[table_example]
                        SET [NotificationSent] = 1
                        WHERE [Id] = @Id";
    using (var cmd = new SqlCommand(sql, conn, trans))
    {
        cmd.Parameters.AddWithValue("@Id", empId);
        cmd.ExecuteNonQuery();
    }
}

private static void UpdateP60Notification(int empId, SqlConnection conn, SqlTransaction trans)
{
    const string sql = @"Update [example].[p60_table]
                        SET [NotificationSent] = 1
                        WHERE [Id] = @Id";
    using (var cmd = new SqlCommand(sql, conn, trans))
    {
        cmd.Parameters.AddWithValue("@Id", empId);
        cmd.ExecuteNonQuery();
    }
}

private static void UpdateMailMessage(MailMessage mail, SqlConnection conn, SqlTransaction trans)
{
    // 直接按主键更新单条记录，删除原有全局MailTemp表的删表、 bulk copy、关联更新逻辑
    const string sql = @"Update [example].[MailMessage]
                        SET [To] = @To,
                            [Status] = @Status,
                            [SentDate] = @SentDate,
                            [SubjectDesc] = @Subject,
                            [BodyDesc] = @Body,
                            [From] = @From
                        WHERE [Id] = @MailId";
    using (var cmd = new SqlCommand(sql, conn, trans))
    {
        cmd.Parameters.AddWithValue("@To", mail.To);
        cmd.Parameters.AddWithValue("@Status", mail.Status);
        cmd.Parameters.AddWithValue("@SentDate", mail.SentDate);
        cmd.Parameters.AddWithValue("@Subject", mail.SubjectDesc);
        cmd.Parameters.AddWithValue("@Body", mail.BodyDesc);
        cmd.Parameters.AddWithValue("@From", mail.From);
        cmd.Parameters.AddWithValue("@MailId", mail.Id);
        cmd.ExecuteNonQuery();
    }
}

• 确认两张业务表的Id字段都是主键，建有聚集索引，保证更新时只会触发行锁，不会升级为表锁。
• 不要在数据库读操作中使用NOLOCK提示，避免脏读。

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