This is follow-up to MDEV-14589, where I claimed that to fix the problem properly, we'd have to be able to execute out-of-order purge.

Thinking again, I do not think that it is necessary to delete the undo log record when we remove a purgeable record. I think that we could trigger purge for the delete-marked committed record (in the current DML thread) as soon as we encounter it. It might even suffice to simply delete the record in the current index, if it is found to be safe to purge.

If I remember correctly, the purge threads would first remove the delete-marked record from all secondary indexes before being proceeding to delete the record from the clustered index. Because an undo log record for delete-marking something should contain enough information to identify all affected indexes, it could be safe for a DML operation to remove the clustered index record before purge is run.

We might extend this to always implement a 'pre-purge' on any DML operation that intends to modify the page. This could include resetting DB_TRX_ID in clustered index records when they refer to committed transactions that are not visible in any active read view (or purge_sys.view). That could reduce the write impact of MDEV-12288.

This topic was brought up during the development of MDEV-20605. I currently think that it is entirely safe for purge to remove any explicit locks on purgeable records. With this suggested bug fix, such locks should almost* never exist in the first place.

*We still need to consider what to do with READ COMMITTED and READ UNCOMMITTED transactions. They would not release explicit locks until commit, but the purge-blocking read view would be updated at statement boundaries (or would not be created at all in case of READ UNCOMMITTED). In MDEV-14589 it was hinted that such transactions would never acquire locks on delete-marked records, so maybe there is no problem.