Main question is in the subject.
In more detail: Let's say we have a table T with a FK constraint to table U (for simplicity, to its PK, and both table use clustered indices on their PKs). Now, we INSERT or UPDATE a row in T, thereby writing the FK value. My understanding is that
a transaction-long X lock is issued for the inserted/changed row on T (and IX locks above);
a transaction-long S lock is issued on U for the FK value - so that no other transaction can acquire an X lock on that key and change it.
Is this correct? If not, how does FK constraint enforcement work, and which locks does it set?
It would explain deadlocks on a parallel ETL system of ours.
(I did not find any explanation about this, also not in publicly accessible places like https://www.microsoftpressstore.com/articles/article.aspx?p=2233327&seqNum=3; also, common understanding seems to be "shared locks are released as soon as SQL Server has read and processed the locked data.", which sort of contradicts my transaction-long S locks ...).
EDIT: Here is an extract from the deadlock graph:
Delete from ProduktGtin ....
...
INSERT INTO dbo.Produktzusatz ....
...
<pagelock fileid="1" pageid="33949422" ...
<owner id="processc45d4e8c8" mode="IX"/>
<waiter id="process780a4c8c8" mode="U" requestType="wait"/>
<pagelock fileid="1" pageid="6935000" ...
<owner id="process780a4c8c8" mode="IX"/>
<waiter id="processc45d4e8c8" mode="S" requestType="wait"/>
0 Answers