SqlSerever InsertOrUpdate并发问题

[280780363]

问题描述及重现代码：

sqlserver的InsertOrUpdate生成的sql在高并发情况下并非线程安全,sqlserver的merge不是一个原子操作,要加上WITH(UPDLOCK, SERIALIZABLE),官方文档说明:

        var sql = freeSql.InsertOrUpdate<MyTestEntity>()
            .SetSource(new MyTestEntity
            {
                Id = -1,
                Name = "123",
                Desc = "1111"
            }, r => r.Name)
            .ToSql();
        Console.WriteLine(sql);

MERGE INTO [my_test] t1 
USING (SELECT @id as [id], @name as [name], @desc as [desc]) t2 
ON (t1.[name] = t2.[name]) 
WHEN MATCHED THEN 
    UPDATE SET t1.[desc] = t2.[desc] 
WHEN NOT MATCHED THEN 
    INSERT ([id], [name], [desc]) 
    VALUES (t2.[id], t2.[name], t2.[desc]);

CREATE TABLE my_test (
	id int NOT NULL,
	name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[desc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT my_test_pk PRIMARY KEY (id),
	CONSTRAINT my_test_unique UNIQUE (name)
);

数据库版本

sqlserver 2016

安装的Nuget包

.net framework/. net core？ 及具体版本

.net8

附上并发测试代码:

        var sql = @"
WAITFOR DELAY '00:00:02';
MERGE INTO [my_test] t1 
USING (SELECT @id as [id], @name as [name], @desc as [desc]) t2 
ON (t1.[name] = t2.[name]) 
WHEN MATCHED THEN 
    UPDATE SET t1.[desc] = t2.[desc] 
WHEN NOT MATCHED THEN 
    INSERT ([id], [name], [desc]) 
    VALUES (t2.[id], t2.[name], t2.[desc]);
";

        var connStr = "Server=xxx;Database=xxx;User Id=sa;Password=xxx;TrustServerCertificate=True;Encrypt=True;";

// 清空表
        using var cleanConn = new SqlConnection(connStr);
        cleanConn.Execute("DELETE FROM [my_test]");
        var name = Guid.NewGuid().ToString(); // 确保是全新 key！

        const int threadCount = 20;
        var exceptions = new ConcurrentBag<Exception>();

        var barrier = new Barrier(threadCount);

        Parallel.For(0, threadCount, i =>
        {
            try
            {
                using var conn = new SqlConnection(connStr);
                // 所有线程同步启动，增加竞争窗口
                barrier.SignalAndWait(TimeSpan.FromSeconds(5));

                conn.Execute(sql, new { id = i, name, desc = $"desc_{i}" });
                Console.WriteLine($"✅ Thread {i} succeeded");
            }
            catch (Exception ex)
            {
                exceptions.Add(ex);
                Console.WriteLine($"❌ Thread {i} failed: {ex.Message}");
            }
        });

        if (exceptions.Any())
        {
            Console.WriteLine($"💥 共 {exceptions.Count} 个线程失败！");
            foreach (var ex in exceptions) Console.WriteLine(ex.Message);
        }
        else
        {
            // 验证最终只有一行
            var count = cleanConn.QuerySingle<int>("SELECT COUNT(*) FROM my_test WHERE [name] = @name", new { name });
            Console.WriteLine($"🎯 最终行数: {count} (应为 1)");
        }