Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
UPDATE: For some reason today I can not reproduce the issue in the exact same way as described in the question. I was able to reproduce (and fix) a different situation altogether, where the problem was caused by a ninja update to a row by the legacy code. I will attempt to delete the question to avoid misleading people. –  Andrey Shchekin Aug 6 '12 at 0:11
    
I'm closing as too localized instead of deleting. We can delete down the road if we need. –  JNK Aug 6 '12 at 0:21
add comment

closed as too localized by JNK Aug 6 '12 at 0:21

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

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?

share|improve this answer
    
You are right, though I can't provide complete version either, as it some legacy C# code that a) I can not share b) does hundreds of lines of of unrelated stuff. But the idea to reproduce it in similar situation is good, I will try it. My current theory is actually key hash collision, which may explain why it is hard to reproduce (depends on what is included in hash, need to research that). –  Andrey Shchekin Aug 3 '12 at 11:15
    
select %%lockres%%, count(*) from <table> group by %%lockres%% does it find any duplicates? –  Remus Rusanu Aug 3 '12 at 11:22
    
For some reason today I can not reproduce the issue in the exact same way as described in the question. I was able to reproduce (and fix) a different situation altogether, where the problem was caused by a ninja update to a row by the legacy code. I will delete the question to avoid misleading people. –  Andrey Shchekin Aug 6 '12 at 0:10
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.