Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16236

Make system versioning use standard compliant transaction timestamps

    XMLWordPrintable

Details

    • Task
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Versioned Tables
    • None

    Description

      The SQL standard requires the ROW START and ROW END column to be populated by the "transaction timestamp" which has the same value for each data change done in the same transaction.

      Quoting SQL-2:2016, $4.41.3 (Properties of SQL-transactions):

      An SQL-transaction has a transaction timestamp, a value of an implementation-defined timestamp type that is used to set the values of system-time period start and system-time period end columns of rows, if any, modified by the execution of an SQL-data change statement in this SQL-transaction. The transaction timestamp is set by an SQL-implementation before any SQL-data change statement is executed in that transaction and, once set, remains unchanged during that SQL-transaction.

      MariaDB seems to use the current time for each statement even when executed in the same transaction.

      One consequence of this requirement is that one transaction can at most create one new row version for each row (otherwise the timestamps would overlap so that a AS OF query might see more than one version).

      This is also clearly covered in the standard—e.g. for UPDATE in SQL-2:2016, 15.13 (Effect of replacing rows in base tables) 9 a ii B versus C.

      The following script shows the wrong behaviour:

      CREATE TABLE demo_system_versioning (
        id INTEGER NOT NULL,
        data VARCHAR(255),
        start_ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START NOT NULL,
        end_ts   TIMESTAMP(6) GENERATED ALWAYS AS ROW END NOT NULL,
        PERIOD FOR SYSTEM_TIME (start_ts, end_ts),
        PRIMARY KEY (id)
      ) WITH SYSTEM VERSIONING;
       
      START TRANSACTION;
      INSERT INTO demo_system_versioning (id, data) VALUES (1, 'X');
      SELECT SLEEP(0.1);
      INSERT INTO demo_system_versioning (id, data) VALUES (2, 'Y');
      COMMIT;
       
      -- should return a single row as both rows where inserted in the same transaction
      SELECT COUNT(*), start_ts
        FROM demo_system_versioning
       GROUP BY start_ts;
       
      TRUNCATE TABLE demo_system_versioning;
       
      START TRANSACTION;
      INSERT INTO demo_system_versioning (id, data) VALUES (3, 'X');
      SELECT SLEEP(0.1);
      UPDATE demo_system_versioning SET data = 'Z' WHERE id = 3;
      COMMIT;
       
      -- should return a single row version as multiple changes to one row in a single transaction are collapsed
       
      SELECT COUNT(*), start_ts
        FROM demo_system_versioning FOR SYSTEM_TIME ALL
       GROUP BY start_ts;
       
      DROP TABLE demo_system_versioning;
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            Markus Winand Markus Winand
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.