I have following situation, I have a table with a primary key, and a constraint that I cannot have rows with specific requirements. For demonstration purposes, here I have constraint that doesn't allow to insert duplicate values in N column. In real case, it checks several columns using foreign keys to other tables and with additional filters, so I cannot put simple unique constraint. So here is example
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
When I run concurrently
insert into dbo.T1 (N)
values (@i)
I get this deadlock S -> X, X -> S on primary key. And that I kind of understand why. Deadlock xml: https://pastebin.com/hceR3sum
My first attempt to fix this was to grab S lock first
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablock, holdlock))
insert into dbo.T1 (N)
values (@i)
commit
But it failed with this deadlock S -> IX, IX -> S. Can somebody explain what is going on? Deadlock xml: https://pastebin.com/mLXJb59C.
And I fixed it with locking entire table with X lock. Is it ok? Is there a better approach?
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablockx, holdlock))
insert into dbo.T1 (N)
values (@i)
commit
If I put index on N column I get this deadlock https://pastebin.com/KJGmLDhH. The real requirement is pretty much the same, the simplest case there are 4 columns with accountIds and enabled flag, and when something happens to the record I have to check that account id is unique among all enabled records. or if it's disabled that I have nothing to check. Something like that.
I use C# to run queries simultaneously
class Program
{
private const string connectionString = "Server=.;Database=Performance;Trusted_Connection=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();
}
}
I use Microsoft SQL Server Express (64-bit) 13.0.1601.5
