C#操作SQL Server 2012偶发无诱因重复键异常排查咨询

问题现象 #

• 业务代码在多台设备正常运行，仅QA部门某台特定工作站偶发抛出Duplicate Key Exception，无法在其他环境复现
• 异常触发时对应数据实际已经成功写入数据库，已通过日志排除应用层保存函数重复执行的可能，确认错误由索引触发
• 项目基于.NET 6开发，使用System.Data.SqlClient 4.8.2操作数据库，先后测试ExecuteNonQuery/ExecuteScalar/ExecuteReader三种执行方式，搭配OUTPUT返回主键、插入前预校验存在性、返回值标记插入状态等逻辑，均无法解决偶发报错问题

业务场景 #

业务数据由第三方设备生成，软件负责将设备数据同步到本地数据库，数据获取有两个路径：

• 软件主动向设备发起请求拉取数据
• 接收设备主动推送的通知事件获取数据

相关代码 #

表及索引创建脚本 #

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FOO].[Transactions]') AND type in (N'U'))
    BEGIN
        CREATE TABLE [FOO].[Transactions](              
            [TransactionID] [bigint] NOT NULL,
            [DeviceID] [tinyint] NOT NULL,
            [TransactionNumber] [bigint] NOT NULL,
            [Material] [text] NULL,
            [Volume] [decimal](15, 3) NOT NULL,
            [StartDateTime] [datetime2](7) NOT NULL,
            [EndDateTime] [datetime2](7) NOT NULL,
            [StartingVolume] [decimal](15, 3) NOT NULL,
            [EndingVolume] [decimal](15, 3) NOT NULL,
        PRIMARY KEY 
        (
            [TransactionID] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[FOO].[transactions]') AND name = N'IX_transaction_number')
    CREATE INDEX [IX_transaction_number] ON [FOO].[transactions]
    (
        [TransactionNumber] DESC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
                

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[FOO].[transactions]') AND name = N'IX_device_sale')
    CREATE UNIQUE NONCLUSTERED INDEX [IX_device_sale] ON [FOO].[transactions]
    (
        [DeviceID] ASC,
        [TransactionNumber] DESC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

基础插入SQL语句 #

INSERT INTO [FOO].[Transactions]
        ([TransactionID],[DeviceID],[TransactionNumber],[Material],[Volume],[StartDateTime],[EndDateTime],[StartingVolume],[EndingVolume])
VALUES
    (@TransactionID ,@DeviceID,@TransactionNumber,@Material,@Volume,@StartDateTime,@EndDateTime,@StartingVolume,@EndingVolume);

带存在性校验的插入SQL #

注意：该SQL执行返回0时，SqlCommand的StatementCompleted事件仍会触发，提示插入操作影响1行数据

IF EXISTS (SELECT TOP 1 * FROM [SCD].[Transactions] WHERE [SaleID] = @SaleID)
BEGIN
    SELECT 0;
END
ELSE
    BEGIN
        INSERT INTO [SCD].[Transactions]
                    ([TransactionID],[DeviceID],[TransactionNumber],[Material],[Volume],[StartDateTime],[EndDateTime],[StartingVolume],[EndingVolume])
                VALUES
                    (@TransactionID ,@DeviceID,@TransactionNumber,@Material,@Volume,@StartDateTime,@EndDateTime,@StartingVolume,@EndingVolume);
        SELECT 1;
    END

C# 数据插入底层实现 #

private int SaveTransaction(SqlConnection connection, TransactionInfo transaction)
{
    try
    {
        _logger.LogInformation("[REPOSITORY] Starting to save TransactionID:{0} at {1} ns",transaction.TransactionID,Stopwatch.GetTimestamp());
        using SqlCommand cmd = new SqlCommand(INSERT_TRANSACTION, connection);
        cmd.Parameters.Add("TransactionID", System.Data.SqlDbType.BigInt).Value = transaction.TransactionID;
        cmd.Parameters.Add("DeviceID", System.Data.SqlDbType.SmallInt).Value = transaction.PumpID;
        cmd.Parameters.Add("TransactionNumber", System.Data.SqlDbType.BigInt).Value = transaction.TransactionNumber;
        cmd.Parameters.Add("Material", System.Data.SqlDbType.Text).Value = transaction.Material;
        cmd.Parameters.Add("Volume", System.Data.SqlDbType.Decimal).Value = transaction.Volume;
        cmd.Parameters.Add("StartDateTime", System.Data.SqlDbType.DateTime2).Value = transaction.StartDateTime;
        cmd.Parameters.Add("EndDateTime", System.Data.SqlDbType.DateTime2).Value = transaction.EndDateTime;
        cmd.Parameters.Add("StartingVolume", System.Data.SqlDbType.Decimal).Value = transaction.StartingVolume;
        cmd.Parameters.Add("EndingVolume", System.Data.SqlDbType.Decimal).Value = transaction.EndingVolume;
        var res = cmd.ExecuteNonQuery();
        _logger.LogInformation("[REPOSITORY] Ending to save TransactionID:{0} at {1} ns Result:{2}", transaction.TransactionID, Stopwatch.GetTimestamp(),res);
        return res > 0 ? 1 : 0;
    }
    catch (Exception ex)
    {
        _logger.LogInformation("[REPOSITORY] Error saving TransactionID:{0} at {1} ns \n {2}", transaction.TransactionID, Stopwatch.GetTimestamp(), ex.Message);
        _logger.LogError(ex, ex.Message);
        return -1;
    }
}

C# 外层调用逻辑（负责连接创建与释放） #

public int SaveTransaction(TransactionInfo transaction)
{
        using SqlConnection connection = new SqlConnection(_connectionString);
    try
    {
        connection.Open();
        var res = SaveTransaction(connection, transaction);
        return res;
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, ex.Message);
        return -1;
    }
    finally
    {
        connection.Close();
    }
}

可能触发异常的SQL Server配置与底层原因 #

• 参数类型隐式转换问题：C#代码中DeviceID字段数据库定义为tinyint，代码里传入的参数类型是SmallInt，高并发场景下不同执行计划的参数嗅探+隐式转换可能导致唯一键校验逻辑出现时序问题，尤其SQL Server 2012 Express对内存、并行执行的限制会放大这类问题
• 读提交隔离级别下的锁竞争异常：默认读提交隔离级别下，存在性校验的读操作和插入操作之间没有加更新锁，两个并发请求同时通过存在性校验时，会先后尝试插入，后插入的请求本应触发重复键，但如果QA工作站的SQL Server实例开启了自动提交+隐式事务的特殊配置，可能出现插入动作实际完成、但异常被错误抛出的情况
• 索引损坏：特定工作站的SQL Server因为异常关机、磁盘坏道等问题导致IX_device_sale唯一索引出现逻辑损坏，索引条目和实际数据不一致，插入时重复校验逻辑误判
• MARS（多活动结果集）连接配置异常：如果QA环境的连接字符串开启了MultipleActiveResultSets=True，在同时处理拉取数据和推送数据两个路径的请求时，可能出现命令复用、请求时序错乱，导致同一个插入语句被执行两次，但因为连接层状态异常，第一次执行成功的结果没有正常返回，第二次执行触发重复键
• SQL Server Express 2012已知bug：该版本在内存压力较大时，唯一索引的闩锁释放逻辑存在已知缺陷，会在插入成功后错误抛出重复键错误，该问题在后续CU补丁中被修复，如果QA工作站的实例没有安装最新累积更新，就会触发这类偶发问题

排查方向 #

• 第一步先确认报错对应的约束名称：捕获异常时完整输出错误信息，明确是主键TransactionID重复还是唯一索引IX_device_sale的(DeviceID,TransactionNumber)组合重复，缩小排查范围
• 检查参数类型匹配问题：把C#代码里DeviceID的参数类型从SmallInt改成和数据库一致的TinyInt，消除隐式转换
• 检查QA工作站SQL Server实例配置：对比正常设备和QA设备的SQL Server版本号、补丁级别、隔离级别配置、是否开启自动收缩、自动关闭等Express默认容易触发的不合理配置，确认QA实例是否安装了最新的SQL Server 2012 SP4及后续CU补丁
• 检查索引健康状态：在QA工作站执行DBCC CHECKTABLE ('FOO.Transactions')和DBCC CHECKDB命令，排查是否存在索引损坏、数据不一致的问题，如果存在直接重建所有索引
• 检查连接字符串配置：确认QA环境使用的连接字符串是否和其他环境一致，重点核对是否开启了MARS、连接池、事务绑定相关的配置项，临时关闭MARS测试是否还会复现问题
• 加锁优化插入逻辑：把存在性校验+插入的逻辑改成单语句原子操作，使用MERGE或者加UPDLOCK,HOLDLOCK提示的插入逻辑，消除并发下的校验和插入时序差，示例：

  INSERT INTO [FOO].[Transactions]
          ([TransactionID],[DeviceID],[TransactionNumber],[Material],[Volume],[StartDateTime],[EndDateTime],[StartingVolume],[EndingVolume])
  SELECT @TransactionID ,@DeviceID,@TransactionNumber,@Material,@Volume,@StartDateTime,@EndDateTime,@StartingVolume,@EndingVolume
  WHERE NOT EXISTS (
      SELECT 1 FROM [FOO].[Transactions] WITH (UPDLOCK,HOLDLOCK) 
      WHERE TransactionID = @TransactionID
  )
  

• 排查设备端数据推送逻辑：在QA环境同时抓包第三方设备的请求日志和应用层日志，确认是否是QA环境的设备同时触发了拉取和推送两个路径，导致同一条数据被两个并发线程同时提交，因为应用层没有做进程内去重锁，两个请求同时执行插入，其中一个成功写入，另一个触发重复键——这类跨线程的并发调用不会被单函数入口的重复执行日志捕获
• 替换客户端驱动测试：临时把System.Data.SqlClient替换成Microsoft.Data.SqlClient最新版本，排除老版本驱动在特定Windows环境下的命令重试、连接状态异常问题

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