Hi, On 08/25/2010 10:35 PM, Simon Riggs wrote:
If the row is "key share" locked (as opposed to "tuple share" locks we already have), then an UPDATE would only work if it was a non-HOT UPDATE.
I think you meant it the other way around: an UPDATE on a "key share" locked tuple only works if it *was* a HOT UPDATE (which doesn't change any indexed attribute, so RI is guaranteed to be okay).
There have been at least three variants proposed for such a "key share" lock, for what kind of updates they should block:
a) block UPDATEs that change any indexed attributes *and* block UPDATEs on tuples that don't fit into the same page again (i.e. 100% like the HOT conditions).
b) block UPDATEs that change any indexed attributes (i.e. any kind of index on them), but allow non-HOT UPDATEs which change only non-indexed attributes
c) block UPDATEs that change any key columns (i.e. only attributes on which there is a UNIQUE constraint), but allow non-HOT UPDATEs which change only non-key attributes.
AFAICT b) and c) are sufficient for solving the OPs deadlock bug (i.e. UPDATEs to only non-index or even only non-key attributes). However, a) does not. Instead it would still deadlock for seemingly random occasions.
The lock to solve the OPs problem cannot match the HOT conditions 100%. I currently don't see any reason for making it as HOT-like as possible, so I'm advocating variant c).
If the reason is simply ease of implementation, I'd be fine with implementing b) first, but the definition of the LOCK type should then leave the way open to the less restrictive variant c).
Regards Markus -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers