SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

Question

I have the following table structure (simplified, pseudocode):

TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)
TABLE dbo.Transfers (
    Id int NOT NULL IDENTITY PRIMARY KEY,
    CreatedByUserId NOT NULL FOREIGN KEY REFERENCES dbo.Users (Id)
    ... -- no other user-related fields
)

I have the following situation in application code:

-- Given a User with Id 5 that already exists
BeginTransaction
1. INSERT INTO dbo.Users (...) VALUES (...) -- Creates user with Id 6
    NotInTransaction.Start
    2. INSERT dbo.Transfers (CreatedByUserId, ...)  VALUES (5, ...)
    NotInTransaction.End
...
CommitTransaction

In this specific case (when existing user and newly created user have nearby Ids), I get a timeout from step 2.

Now the interesting part is that I paused the application on step 2, and tried the following things outside the (paused) transaction:

• SELECT * FROM dbo.Users WHERE UserId = @UserId
  works (not locked) for all values of @UserId except 6, which makes total sense
• INSERT dbo.Transfers (CreatedByUserId, ...) VALUES (@UserId, ...)
  works for 1,2,3,4, locked for 6 and 5

I did a quick look on sys.dm_tran_locks (which I am not a master of) with some hobt joins, and when INSERT is locked, it shows

ObjectName | resource_type | resource_description | resource_associated_entity_id | request_mode | request_status
Users      | KEY           | (b9b173bbe8d5)       | 72057594047299584             | X            | GRANT
Users      | KEY           | (b9b173bbe8d5)       | 72057594047299584             | S            | WAIT

Now, the question is: why can I select user 5 from outside the transaction, but not insert into a table that references it through FK?

Answer 1

Your simplified repro simplified the problem beyond the point it repro...

create TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)

create TABLE dbo.Transfers (
    Id int NOT NULL IDENTITY PRIMARY KEY,
    CreatedByUserId int NOT NULL FOREIGN KEY REFERENCES dbo.Users (Id))

insert into Users default values;
go 5

insert into transfers (CreatedByUserId) values (1), (2), (3), (4);
go

begin tran
insert into Users default values;
insert into transfers (CreatedByUserId) values (6);

-- from separate connection
insert into transfers (CreatedByUserId) values (5);

Since the problem no longer reproes it means your simplified out a critical piece of information. Your symptoms look suspiciously similar to serializable isolation level issues. Perhaps you're using the harmful default TransactionScope constructor?