Re: Referential Integrity and SHARE locks

On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > Thus we have three types of write lock:
> > 1. full row write lock
> > as well as two mutually exclusive groups of columns:
> > 2.a) PK cols
> > 2.b) all columns apart from the PK cols
>
> This appears to require that we add enough fields to row headers to
> represent *three* locking transactions instead of the current one.
> Given the amount of griping about row header overhead that normally
> flows across this list, I can't see such a proposal getting accepted.

Yeh, I said "ouch" myself.

> > This would be possible by using 2 additional tuple info bits to flag
> > that the lock held was either HEAP_LOCKED_PK_ONLY or
> > HEAP_LOCKED_NON_PK_COLS. When both lock types are held simultaneously we
> > will replace xmax with a multitransaction id, where we hold only two
> > transactionids at most - the first Xid is the holder of the PK cols
> > lock, the second Xid is the holder of the non-PK cols lock.
>
> You haven't thought that through: it fails to distinguish who holds
> which lock (mxact membership is not ordered)

OK, sorry, I thought there was some ordering possible.

> , and it doesn't scale to
> more than two holders, and I don't think it works for combinations of
> share and exclusive lock. Also, what happened to the third type of lock?

Well, we just need to record the maximum two lock holders (given the
semantics described). The third lock type is both locks at once.

> Implementation details aside, I'm a tad concerned about introducing
> deadlock failures that did not happen before, in scenarios where
> transactions touch the row multiple times and end up needing to
> acquire one of these locks while already holding another.

Well, right now we have deadlocks and lots of locking. Updating PKs
isn't a regular occurence, so I'd rather swap a common deadlock for an
uncommon one, any day.

Anyway, implementation aside, I wanted to agree the overall TODO, so we
can think through the best way over a long period, if you agree in
general.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com