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.