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

Alvaro Herrera wrote:
> AFAICS the problem here is that this test doesn't use MultiXactIds at
> all in 9.2, but it does in 9.3. I vaguely recall Noah tried to convince
> me to put in an optimization which would have avoided this issue; I will
> give that a thought. I don't think I will be able to get it done for
> 9.3.1 though.

I gave this a deeper look yesterday. Sadly, that optimization would not
work here; we give stronger guarantees now than 9.2 and that's the
reason for the slowness. Previously, we just overwrote the lock held by
the ancestor transaction if a subxact grabbed a stronger lock; we no
longer do that, and instead create a multi comprising both of those
locks. That way if the subxact rolls back, we don't lose the previous
lock we held.

It seems that we could avoid creating a new multixact in a few more
cases, by reusing an already existing multixact; or, if we're forced to
create a new one, omit the member from the old one that is superceded by
our own member with the stronger lock. However, there is no way this
will affect this test case *at all*.

I see another way forward: if an ancestor takes lock of a certain
strength, and then subxact takes a stronger lock, we could record this
as "ancestor taking the stronger lock", so this could be stored as a
plain Xid and not a Multi. That way, (1) we do not incur a new
multixact, and (2) the lock would not be lost if the subxact aborts.
This would come at the cost that if the subxact rolls back, the stronger
lock would not be released.

Another thing we could look at is whether we can optimize for the case
of sub-committed subtransactions, i.e. we know these won't roll back in
relation to the current xact. It seems that would apply here because
when you finish the BEGIN/EXCEPTION/END block, that automatic subxact is
marked sub-committed. It seems to me we could collapse the locks
acquired by those subxacts to appear as locks owned by its currently
open ancestor.

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