midenok Dec 5th, 2019 at 13:57
@marko, @Eugene Kosov I'd like to discuss the design of transaction system versioning without transaction_registry. transaction_registry has many drawbacks (performance, hard to backup, not node-portable). OTOH the feature of "Transaction-Precise History in InnoDB" as explained in https://mariadb.com/kb/en/library/temporal-data-tables/  can go without it.
The general idea behind my proposal: to update row_start, row_end timestamps of all rows of active transaction at commit. The drawback behind that is that commit time will increase: transaction may have thousands (or millions?) of rows, so we need to update them all. Do you think this could be viable?

https://mariadb.com/kb/en/library/temporal-data-tables/

marko  1 year ago
It could be viable, and we could find the affected PKs by traversing the undo log of the transaction, even if it is big. We’d have to write more undo log for updating the fields. I do not see a problem with rollback, if we do this.

midenok  1 year ago
In fact there is no need to to find anything, we have transaction TRX_ID. We can find records by it. Rollback just restores old records from undo log.

marko  1 year ago
well, my point is that we must go and update each record in versioned tables that we modified in the transaction. And that information is in the undo log pages, not in main memory. In MyRocks it would be buffered in main memory until commit.

Updating PK is expensive (it’ll delete-mark the records in each index and insert new ones), so it would be nice if we only had to update the ‘end ID’ at commit, and write the ‘start ID’ already earlier.

midenok  1 year ago
The records we modified are already in clustered index, ordered by 'start ID' (at least). Isn't it?

marko  1 year ago
yes, that is what I would expect. I just wanted to confirm.

so, as long as ‘end ID’ is not indexed, it is very cheap (update-in-place in the clustered index records)
(assuming that it is being updated from a NOT NULL value to a NOT NULL value)

midenok  1 year ago
Yes. Good point.

'start ID' is DB_TRX_ID field of record.

marko  1 year ago
but that is not portable?

midenok  1 year ago
It is portable. We don't need any IDs, because what user wants is only timestamps. row_start, row_end will be timestamp as usual. User requirement is described by the link above: A point in time when a row was inserted or deleted does not necessarily mean that a change became visible at the same moment. With transactional tables, a row might have been inserted in a long transaction, and became visible hours after it was inserted. I don't believe anything more like timer jitter or transaction detalization is of a big value.

Though we might optionally store 'start ID' , 'end ID' as well to allow extra features. But TR is not needed, because timestamps are stored too.

marko  1 year ago
I would like to get a full description of this, including a description of what the transaction registry currently stores and how that information is being used.
If we use timestamps only, how would it differ from the current implementation? That the ‘end timestamp’ would be assigned (and updated) at transaction commit time, instead of being assigned at the time the statement is executed?

midenok  1 year ago
Yes.