0

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"/>
H.M. Müller
  • 1
  • 2
  • You overlook a specific thing - that SQL Server CAN promote ROW locks to PAGE locks and TABLE locks iof there are too many. WHich means in theory you may select one row and end up with a table lock. – TomTom May 31 '20 at 16:33
  • This is a nice comment on another question I want to ask (but cannot right now, because I'm limited to 1 question/40 minutes). However, what does your comment have to do with my question? - I am not talking about page or table locks. – H.M. Müller May 31 '20 at 16:43
  • Well, now here is an intelligence test: Why do you think I put it up as a COMMENT and not as an answer? Because it provides information (i.e. your locks may look WAY larger than you see in this case) but NOT AN ANSWER. – TomTom May 31 '20 at 16:47
  • Then thank you very much for your COMMENT; but we know (from the dm_whatever view for currently held locks) that lock escalation does not play a role here. You can try this yourself with changing a single record on the source side of an FK constraint - you'll see that an S lock is taken on the target side. I trust you can use SSMS to check this out yourself; but you can also believe me :-). So I'd say I did not "overlook" what you said. Moreover, you are partly wrong - but this is outside this question. – H.M. Müller May 31 '20 at 16:49
  • Comment from myself: This comment says that I am right - https://www.quora.com/Is-it-true-that-a-database-without-foreign-key-constraints-is-faster/answer/Bill-Karwin – H.M. Müller May 31 '20 at 17:37
  • If it's a deadlock SQL Server should select a victim and terminate its command automatically. Is that what is happening? – Greg Askew May 31 '20 at 18:50
  • Of course. The deadlock dependencies vary; but the typical most simple scenario is that two pages are "fought about"; I have added an excerpt from the deadlock XML above - the non-S locks result from earlier statements in the transaction and are ok, i.e. understood. The only questions I have whether the S lock comes from a FK constraint check; and how long it will be held; and also why it is a page lock and not a row lock. – H.M. Müller May 31 '20 at 19:10

0 Answers0