dotnetcore / dotnetcore/FreeSql
SqlSerever InsertOrUpdate并发问题
- Dominant language
- C#
- Stars
- 4.4k
- Forks
- 910
- PR merge metrics
- No merged PRs in 30d
Description
#### 问题描述及重现代码:
sqlserver的InsertOrUpdate生成的sql在高并发情况下并非线程安全,sqlserver的merge不是一个原子操作,要加上WITH(UPDLOCK, SERIALIZABLE),[官方文档说明](https://learn.microsoft.com/zh-cn/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16#concurrency-considerations-for-merge):
```c#
var sql = freeSql.InsertOrUpdate()
.SetSource(new MyTestEntity
{
Id = -1,
Name = "123",
Desc = "1111"
}, r => r.Name)
.ToSql();
Console.WriteLine(sql);
```
```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]);
```
```sql
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
#### 附上并发测试代码:
```c#
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();
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("SELECT COUNT(*) FROM my_test WHERE [name] = @name", new { name });
Console.WriteLine($"🎯 最终行数: {count} (应为 1)");
}
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.