Referential Integrity and SHARE locks

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Referential Integrity and SHARE locks
Date: 2007-02-02 09:51:36
Message-ID: 1170409896.3680.185.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Richard Huxton 2007-02-02 10:09:24 Re: Function proposal to find the type of a datum
Previous Message Michael Meskes 2007-02-02 09:28:11 Re: [GENERAL] 8.2.1 Compiling Error