Josh Berkus <josh@agliodbs.com> writes:
> Hmmm. It seems to me that we'd need a sharelock on the referenced row
> both times.
No, we don't. The first update knows that it's updating a pre-existing
referencing row and not changing the FK value. If someone were to try
to delete the referenced row, they would see the original version of the
referencing row as good and hence fail their FK deletion check.
The case where we need a sharelock is for insertion of a new referencing
row. It's to prevent the race condition where somebody deletes the
referenced row and thinks it's OK because he doesn't see the new
referencing row yet.
In principle we don't need to sharelock the referencing row in either
update in this example, since the original row version is still there.
The problem is to know that, given the limited amount of information
available when performing the second update.
regards, tom lane