Re: BUG #8470: 9.3 locking/subtransaction performance regression

After looking at this some more, I am more convinced that we need to
have HeapTupleSatisfiesUpdate return BeingUpdated when a row is locked
by the same xact. This is more in line with what we do for MultiXacts,
and allows us to treat some cases in a saner fashion: in particular, if
a transaction grabs a lock on a tuple and a subxact grabs a stronger
lock, we now create a multi recording both locks instead of silently
ignoring the second request.

The fallout from this change wasn't as bad as I had feared; I had to add
some coding in heap_update and heap_delete to prevent them from trying
to wait on their own Xid, but it looks pretty innocuous otherwise.

In ran your test case inflating the loop counters a bit, so that it
shows more exaggerated timings. The results of running this twice on
9.2 are:
Timing: 6140,506 ms
Timing: 6054,553 ms

I only ran it once in an unpatched 9.3:
Timing: 221204,352

This is patched 9.3:
Timing: 13757,925 ms

Note patched 9.3 is 2x slower than 9.2, which is understandable because
it does more work ... yet I think it returns performance to a much more
acceptable level, comparable to 9.2.

I have more confidence in this patch than in the previous version,
despite it being more intrusive. The fact that it allows us to get rid
of some very strange coding in pgrowlocks is comforting: we now know
that anytime a tuple is locked we will get BeingUpdated from
HeapTupleSatisfiesUpdate, regardless of *who* locked it, and all the
callers are able to identify this case and act in consequence. This
seems saner in concept that sometimes returning MayBeUpdated, because
that turns each such return into a condition that must be double-checked
by callers. This means cleaner code in pgrowlocks around a HTSU call,
for instance. Also, we have discussed and seriously considered this
idea in several occasions previously in connection with other things.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services