Type: New Feature
Fix Version/s: Exasol 7.1.0
Changed Behavior: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.
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.
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.
The default Snapshot Mode has been changed from 'OFF' (disabled) to 'SYSTEM TABLES'.
The command line parameter -enableSystemTablesSnapshotMode is replaced with new syntax to control the Snapshot Mode.
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).
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|
|Read-lock on all database tables|
|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|
|Executed in Snapshot mode|
|No transaction conflict|