I'm reading the SQL Standard and I can't find anywhere that says that we need to place SHARE locks on rows in the referenced table. RI_FKey_check() clearly does that.
What I do see is this: "4. For each row of the referenced table, its matching rows, unique matching rows, and non-unique matching rows are determined immediately prior to the execution of any <SQL procedure statement>. No new matching rows are added during the execution of that <SQL procedure statement>. The association between a referenced row and a non-unique matching row is dropped during the execution of that SQL-statement if the referenced row is either marked for deletion or updated to a distinct value on any referenced column that corresponds to a non-null referencing column. This occurs immediately after such a mark for deletion or update of the referenced row. Unique matching rows and non-unique matching rows for a referenced row are evaluated immediately after dropping the association between that referenced row and a non-unique matching row." under General Rules for <referential constraint definition> That explicitly says "are determined immediately prior to the execution". To me, that implies that a Read Committed snapshot is sufficient to read referenced rows and that no lock is required. Why do we need a SHARE lock at all, on the **referenc(ed)** table? It sounds like if we don't put a SHARE lock on the referenced table then we can end the transaction in an inconsistent state if the referenced table has concurrent UPDATEs or DELETEs. BUT those operations do impose locking rules back onto the referencing tables that would not be granted until after any changes to the referencing table complete, whereupon they would restrict or cascade. So an inconsistent state doesn't seem possible to me. What am I missing? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
