I'm using CommitRetaining at the moment, but was adviced not to a while ago:
Try not to use the CommitRetaining or RollbackRetaining in transactions. It leads to long running idle transactions which is generally considered to be a program bug. Instead use TSQLQuery option sqlAutoCommit or sqlKeepOpenOnCommit with Commit.
But then all queries have to be sqlKeepOpenOnCommit or else those that are not are closed when calling commit. And sqlKeepOpenOnCommit sets the PacketRecords property to -1 meaning all records will be read and loaded at once instead of ten or so at a time.
So I thought a second, shortlived, transaction would be the solution. But how to do it?