我有以下情况,我有一个带有主键的表,以及一个我不能有具有特定要求的行的约束。出于演示目的,这里我有不允许在 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
如果不提供 column 索引
N
,SQL Server 无法检查目标值是否有效存在,必须扫描表。扫描一直持续到找到匹配项,或者如果不存在匹配项(您的目标案例)则扫描整个表。这是非常低效的,并且每行都会发生。在默认锁定读提交隔离级别,扫描通常还意味着在每行测试匹配时获取和释放共享锁。
根据在不同连接上插入(并因此排他锁定)行的顺序,竞争活动将导致广泛的共享排他阻塞或死锁。
所以你需要一个非聚集索引
N
,例如:有索引
当索引存在时,您可能仍然会遇到死锁(如您所见)。这是因为当表较小时,SQL Server 可能会选择扫描非聚集索引,而不是寻找所需的 N 值,然后寻找 id > @id OR id < @id。
这可能会以与原始情况类似的方式导致阻塞或死锁。确切的交互稍微复杂一些,因为 SQL Server 通常会在使用标量函数检查约束之前选择在聚集索引和非聚集索引中插入新行。(在演示场景中,可以通过在检查约束后强制 SQL Server 插入非聚集索引来避免这种情况,但我不想参与其中。)
一种解决方法
我只想说,除了最专业的从业者之外,所有人都应该避免使用标量函数强制检查约束。有太多的怪癖和隐藏的陷阱。当约束没有按预期工作时,您可能最终会得到无效数据,甚至根本无法触发。
请记住,大多数人认为他们比实际情况要专家得多。也就是说,仅出于教育价值,可以通过强制对非聚集索引进行搜索访问来避免演示中的死锁情况。
我还添加了一个提示,因为必须使用最新提交的值而不是行版本来
READCOMMITTEDLOCK
验证约束。在使用已提交的快照隔离或快照隔离时,该演示无法确保唯一性,因为该函数可能会读取过期数据。这仍然不是健壮的代码,也不适合生产使用。它确实解决了问题中显示的问题。
根据实际需求,您可能可以使用触发器、非规范化约束或索引视图。
其他备注
HOLDLOCK
并不意味着持有锁。SQL Server 在给定查询规范和配置设置(包括隔离级别)的情况下,始终持有足够长的锁以保证正确性。HOLDLOCK
是 的同义词SERIALIZABLE
。对于专家用户来说,这是一个高级选项,可以为特定对象指定可序列化的隔离语义,同时在同一事务中的其他对象上使用不同的级别。TABLOCK
确保只使用对象级锁。TABLOCKX
确保仅采用独占对象级锁。两者都倾向于完全序列化对指定对象的访问,这对于避免争用非常有用——但这只是因为您现在根本没有并发性。标量函数检查约束未按预期运行的示例(一些链接很旧,但描述的行为仍然是最新的):
另请注意,检查约束中使用的标量函数不符合SQL Server 2019+ 的内联条件。