Add SQL interface and system table for transaction Snapshot Mode

Detail name	Value
Changelog Number	10248
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.1.0
Resolution Date	2021-08-03

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 = {'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
		Read-lock on all database tables
		Error "Transaction collision: automatic transaction rollback"

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;

insert into T1 values (2);

commit;

insert into T1 values (1);

 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

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;

insert into T1 values (4);

commit;

insert into T1 values (3);

commit;

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.