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

On Tue, Jul 30, 2019 at 7:12 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> SQL Server 2019 has something called "instantaneous transaction
> rollback", which seems to make SQL Server optionally behave a lot more
> like Postgres [1], apparently with many of the same disadvantages as
> Postgres. I agree that there is probably a middle way that more or
> less has the advantages of both approaches. I don't really know what
> that should look like, though.
>
> [1] https://www.microsoft.com/en-us/research/uploads/prod/2019/06/p700-antonopoulos.pdf

Thanks for sharing that. I see they're giving that paper at VLDB next
month in LA... I hope the talk video will be published on the web.
While we've been working on a hybrid vaccum/undo design, they've built
a hybrid undo/vacuum system. I've only skimmed this, but one of their
concerns that caught my eye is log volume in the presence of long
running transactions ("3.6 Aggressive Log Truncation"). IIUC they
have only a single log for both redo and undo, so a long running
transaction requires them to keep all log data around as long as it
might be needed for that transaction, in traditional SQL Server.
That's basically the flip side of the problem we're trying to solve,
in-heap bloat. I think we might have a different solution to that
problem, with our finer grained undo logs. Our undo data is not mixed
in with redo data (though redo can recreated it, it's not needed after
that), and we have multiple undo logs with their own discard pointers,
so a long running transaction only prevents only one single undo log
from being truncated, while other undo logs holding other transactions
can be truncated as soon as those transactions are committed/rolled
back and are either all visible (admittedly tracked with a system-wide
xmin approach for now, but could probably be made more granular) or a
snapshot-too-old threshold is reached (not implemented yet).

--
Thomas Munro
https://enterprisedb.com