[MDEV-16226] TRX_ID-based System Versioning refactoring Created: 2018-05-21  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: Versioned Tables
Fix Version/s: 11.5

Type: Task Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 5
Labels: trx-versioning

Attachments: Text File trx_id_versioning_talk.txt    
Issue Links:
Blocks
is blocked by MDEV-16144 Default TIMESTAMP clause for SELECT f... Closed
Relates
relates to MDEV-17089 Updating a System Versioned Table alw... Closed
relates to MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertio... Closed
relates to MDEV-23446 UPDATE does not insert history row if... Closed
relates to MDEV-24608 Failing assertion: error == DB_SUCCES... Open
relates to MDEV-24621 In bulk insert, pre-sort and build in... Closed
relates to MDEV-15922 system versioning with trx_id breaks ... Stalled
relates to MDEV-17404 inconsistent transaction isolation le... Open
relates to MDEV-19131 Assertion `table->versioned(VERS_TRX_... Confirmed
relates to MDEV-20458 missing row in mysql.transaction_regi... Confirmed
relates to MDEV-20842 Crash using versioning plugin functio... Closed
relates to MDEV-21016 ASAN heap-use-after-free in trx_updat... Open
relates to MDEV-21576 Assertion `table->vers_start != table... Open
relates to MDEV-22475 versioning.update failed in buildbot ... Closed
relates to MDEV-22960 versioning.replace fails in buildbot ... Open
relates to MDEV-23145 Server crashes in handler::update_glo... Open
relates to MDEV-23285 mysql.transaction_registry contains d... Confirmed
relates to MDEV-26807 Duplicate entry with cascading delete... Open
relates to MDEV-27040 Was: Assertion `thd->transaction->stm... Open
relates to MDEV-29726 Assertion failure upon DELETE HISTORY... Open
relates to MDEV-30035 Possibly unexpected ER_LOCK_WAIT_TIME... Open
relates to MDEV-30701 row_start not set properly when updat... Open
Sub-Tasks:
Key
Summary
Type
Status
Assignee
MDEV-26472 Option to choose System Versioning wr... Technical task Open Aleksey Midenkov  

 Description   

As was discussed in https://mariadb.slack.com/archives/CHTLSLQEP/p1575543442067300 (attached here) transaction_registry table has many drawbacks: performance, hard to backup, not node-portable. The feature of transaction-precise versioning as described in https://mariadb.com/kb/en/library/temporal-data-tables/ actually does not require transaction_registry translation: we store timestamps as usual, but update row_end of changed rows at commit time.

Clustered index records contain DB_TRX_ID, DB_ROLL_PTR fields with non-null values in case they was changed by some open transaction. Clustered index is ordered by PK, explicit or implicit. In case there is no explicit PK in table, first non-null UK is then used or if no such UK exists then auto-generated DB_ROW_ID is added to clustered index. So there are 3 variants of ordering: explicit PK, non-null UK, DB_ROW_ID. We cannot search quickly by DB_TRX_ID.

Every open transaction has 2 undo logs attached per each changed table. Undo log cannot be attached to more than 1 transaction. One of undo logs is used for non-temporary tables, another one is for temporary ones. Non-temporary undo log must be scanned and corresponding clustered index rows selected by PK (one of 3 variants above).

Redo log must updated as well, otherwise crash recovery wil end up with wrong values of row_end.

Upgrade process converts TRX_ID-based tables to timestamp-based. As such kind of tables cannot be backed up, transaction_registry table is safe to drop as long as all TRX_ID-based tables converted.


Generated at Thu Feb 08 08:27:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.