我有以下情况，我有一个带有主键的表，以及一个我不能有具有特定要求的行的约束。出于演示目的，这里我有不允许在 N 列中插入重复值的约束。在实际情况下，它使用其他表的外键和其他过滤器检查几列，所以我不能放置简单的唯一约束。所以这里是例子
create table dbo.T1 (
Id int not null identity (1,1),
N int not null
)
alter table dbo.T1
add primary key (Id);
go
create function [dbo].[fn_CheckN](@id int, @n int)
returns int
as
begin
if exists (select * from dbo.T1 t where t.n = @n and t.Id != @id)
return 0
return 1
end
go
alter table [dbo].T1 with nocheck add constraint [CK_T1_Valid] check (([dbo].[fn_CheckN]([Id],[N]) = 1))
go
alter table [dbo].T1 check constraint [CK_T1_Valid]
go
当我同时运行时
insert into dbo.T1 (N)
values (@i)
我在主键上得到这个死锁 S -> X, X -> S。我有点明白为什么。死锁xml： https ://pastebin.com/hceR3sum
我第一次尝试解决这个问题是先抓住 S 锁
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablock, holdlock))
insert into dbo.T1 (N)
values (@i)
commit
但它因死锁 S -> IX, IX -> S 而失败。有人可以解释发生了什么吗？死锁 xml：https ://pastebin.com/mLXJb59C 。
我用 X 锁锁定了整个表来修复它。可以吗？有更好的方法吗？
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablockx, holdlock))
insert into dbo.T1 (N)
values (@i)
commit
如果我在 N 列上放置索引，我会遇到这个死锁https://pastebin.com/KJGmlDhH。真正的要求几乎相同，最简单的情况是有 4 列带有 accountIds 和 enabled 标志，当记录发生问题时，我必须检查 account id 在所有启用的记录中是否唯一。或者如果它被禁用，我没有什么要检查的。类似的东西。
我使用 C# 同时运行查询
class Program
{
private const string connectionString = "Server=.;Database=Performance;Trusted_Connection=True;MultipleActiveResultSets=True; Max Pool Size=3000";
static async Task Main(string[] args)
{
await ClearAsync();
await Task.WhenAll(Enumerable.Range(0, 100000).Select(async i => await InsertAsync(i)).ToArray());
Console.WriteLine("Done");
Console.ReadKey();
}
public static async Task InsertAsync(int i)
{
using var connection = new SqlConnection(connectionString);
using var cmd = new SqlCommand(@"
insert into dbo.T1 (N)
values (@i)
", connection);
await connection.OpenAsync();
cmd.Parameters.Add("@i", SqlDbType.Int).Value = i;
await cmd.ExecuteNonQueryAsync();
}
public static async Task ClearAsync()
{
using var connection = new SqlConnection(connectionString);
using var cmd = new SqlCommand("delete from dbo.T1", connection);
await connection.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
}
我使用 Microsoft SQL Server Express（64 位）13.0.1601.5
