Re: Deadlock bug

On Fri, 2010-08-20 at 15:59 -0400, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)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 ...

ISTM that the cause of this issue is that we don't need a *share* lock
at all, we need something slightly less than that.

We place the share lock because we want to ensure that the PK value is
not removed by either UPDATE or DELETE. There is no need to forbid
UPDATEs that do not change the PK value on the referenced table.

So I propose that we have a new kind of lock: nodelete lock. This is a
regular row lock type and acts almost exactly same as a sharelock. Any
attempt to change PK or DELETE the value must wait for the current lock
holders transactions to complete. Other UPDATEs are possible - the
locked state would be passed down the lock chain to latest version.

We would change the RI code to use nodelete locks rather than share
locks, which would then avoid the issue.

It would not be possible to mix both nodeletelocks and sharelocks since
the multixact infrastructure only allows one lockref. That's not likely
to be a problem since sharelocks are mostly only used by RI anyway.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services