Uploaded image for project: 'EXASOL Roadmap'
  1. EXASOL Roadmap
  2. EXASOL-2901

Add SQL interface and system table for transaction Snapshot Mode

    Details

    • Type: New Feature
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Fix Version/s: Exasol 7.1.0
    • Component/s: None
    • Labels:
      None
    • Changed Behavior:
      Hide
      New ALTER SYSTEM/SESSION syntax added to set Snapshot Mode. Default has been changed from 'OFF' (disabled) to 'SYSTEM TABLES'. Command line parameter -enableSystemTablesSnapshotMode has been removed. The profiling and audit tables have been changed to improve logging for the feature.
      Show
      New ALTER SYSTEM/SESSION syntax added to set Snapshot Mode. Default has been changed from 'OFF' (disabled) to 'SYSTEM TABLES'. Command line parameter -enableSystemTablesSnapshotMode has been removed. The profiling and audit tables have been changed to improve logging for the feature.

      Description

      New Feature

      New syntax has been added to allow the default Snapshot Mode to be overridden using ALTER SYSTEM for all future sessions and ALTER SESSION for individual sessions.

      ALTER {SYSTEM|SESSION} SET SNAPSHOT_MODE TO {'OFF'|'SYSTEM TABLES'};

      When Snapshot Mode is set to 'OFF', queries selecting from system (metadata) tables or user tables will take read locks on those tables.

      When Snapshot Mode is set to 'SYSTEM TABLES', queries selecting from system tables will not take read locks on them. Queries selecting from user tables will continue to get read locks on them.

      The behaviour of the prefix /snapshot execution/ is unchanged, but no longer required.

      Changed default

      The default Snapshot Mode has been changed from 'OFF' (disabled) to 'SYSTEM TABLES'.

      Removed command line parameter

      The command line parameter -enableSystemTablesSnapshotMode is replaced with new syntax to control the Snapshot Mode.

      Profiling and audit table changes

      A new column SNAPSHOT_MODE was added to EXA_SQL_LAST_DAY and EXA_DBA_AUDIT_SQL. For profiling, table rows where a table was accessed in Snapshot Mode, PART_INFO will contain 'IN SNAPSHOT MODE' (the label 'SYSTEM TABLE SNAPSHOT' is renamed to 'SYSTEM TABLE' in the PART_NAME column).

      Examples

      Initial setup:

      create schema S;
      create table T1 (C int);
      

      Snapshot Mode OFF: the select from EXA_DBA_TABLES causes a read-locks and forces a transaction rollback causing a conflict.

       

      Session 1 Session 2 Note
      open schema S;
         
      alter session set snapshot_mode = 'OFF';
         
      set autocommit off;
         
       
      open schema S;
       
       
      alter session set snapshot_mode = 'OFF';
       
       
      set autocommit off;
       
      select * from EXA_DBA_TABLES;
        Read-lock on all database tables
       
      insert into T1 values (2);
       
       
      commit;
       
      insert into T1 values (1);
        Error "Transaction collision: automatic transaction rollback"

       Snapshot Mode SYSTEM TABLES: EXA_DBA_TABLES is a system table so selecting from it does not cause a read-lock and avoids the conflict. The queries are run as if the system table was not read.

      Session 3 Session 4 Notes
      open schema S;
         
      alter session set snapshot_mode = 'SYSTEM TABLES';
         
      set autocommit off;
         
       
      open schema S;
       
       
      alter session set snapshot_mode = 'SYSTEM TABLES';
       
       
      set autocommit off;
       
      select * from EXA_DBA_TABLES;
        Executed in Snapshot mode
       
      insert into T1 values (4);
       
       
      commit;
       
      insert into T1 values (3);
        No transaction conflict
      commit;
         

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                CaptainEXA Captain EXASOL
                Reporter:
                CaptainEXA Captain EXASOL
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: