Re: should there be a hard-limit on the number of transactions pending undo?

On Sat, Jul 20, 2019 at 4:17 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Fri, Jul 19, 2019 at 12:52 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > Right, that's definitely a big part of the concern here, but I don't
> > really believe that retaining locks is absolutely required, or even
> > necessarily desirable. For instance, suppose that I create a table,
> > bulk-load a whole lotta data into it, and then abort. Further support
> > that by the time we start trying to process the undo in the
> > background, we can't get the lock. Well, that probably means somebody
> > is performing DDL on the table.
>
> I believe that the primary reason why certain other database systems
> retain locks until rollback completes (or release their locks in
> reverse order, as UNDO processing progresses) is that application code
> will often repeat exactly the same actions on receiving a transient
> error, until the action finally completes successfully. Just like with
> serialization failures, or with manually implemented UPSERT loops that
> must sometimes retry. This is why UNDO is often (or always) processed
> synchronously, blocking progress of the client connection as its xact
> rolls back.
>
> Obviously these other systems could easily hand off the work of
> rolling back the transaction to an asynchronous worker process, and
> return success to the client that encounters an error (or asks to
> abort/roll back) almost immediately. I have to imagine that they
> haven't implemented this straightforward optimization because it makes
> sense that the cost of rolling back the transaction is primarily borne
> by the client that actually rolls back.
>

It is also possible that there are some other disadvantages or
technical challenges in those other systems due to which they decided
not to have such a mechanism. I think one such database prepares the
consistent copy of pages during read operation based on SCN or
something like that. It might not be as easy for such a system to
check if there is some pending undo which needs to be consulted. I am
not telling that there are no ways to overcome such things but that
might have incurred much more cost or has some other disadvantages.
I am not sure if it is straight-forward to imagine why some other
system does the things in some particular way unless there is some
explicit documentation about the same.

Having said that, I agree that there are a good number of advantages
of performing the actions in the client that actually rolls back and
we should try to do that where it is not a good idea to transfer to
background workers like for short transactions.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com