Snapshot Mode

This article describes the snapshot mode in Exasol.

To avoid transaction conflicts, you can run SQL statements in snapshot mode. This feature is especially useful when making frequent queries to the metadata system tables, as such queries can lead to delayed execution caused by transaction management. For example, BI tools and other front end systems need metadata from Exasol to display information about existing tables. If there is any information that cannot be retrieved through regular metadata calls on API level, you have to fall back to SQL queries on the system tables.

To avoid delayed execution in such a scenario, you can run queries to the system tables in snapshot mode. Snapshot mode ignores existing transaction locks on system tables and does not create new ones. All the queries to the system tables will run in read-only mode and show you the latest object version (the latest committed version).

Using snapshot mode

Snapshot mode is enabled for the system tables by default through the parameter SNAPSHOT_MODE. This parameter determines how all queries within the session are executed. You can change this parameter using either ALTER SYSTEM or ALTER SESSION.

To enable or disable the snapshot mode for the current session, use ALTER SESSION. If the snapshot mode is enabled, all queries to the system table run in snapshot mode during the current session.

Examples:
Copy 
--enable snapshot mode for system tables in a session
ALTER SESSION SET SNAPSHOT_MODE = 'SYSTEM TABLES';
Copy 
-- disable snapshot mode for system tables in a session
ALTER SESSION SET SNAPSHOT_MODE = 'OFF';

You can enable or disable the snapshot mode for all new sessions using ALTER SYSTEM. If the snapshot mode is enabled, all queries to the system table run in snapshot mode.

Examples:
Copy 
--disable snapshot mode for system tables
ALTER SYSTEM SET SNAPSHOT_MODE = 'OFF';
Copy 
--enable snapshot mode for system tables
ALTER SYSTEM SET SNAPSHOT_MODE = 'SYSTEM TABLES';

Using special parameter in driver connection string

You can specify a parameter in your driver connection string to enable the snapshot mode for your connection. You can then run all queries to the system tables in snapshot mode. The driver parameters are an option to initialize the session parameter to a different value than what's already specified by ALTER SYSTEM.

For more details on the parameter, see Supported driver properties (JDBC), Connection strings (ODBC), or Use the data provider (ADO.NET).

Using the /*snapshot execution*/ prefix

You can also run any SQL statement in snapshot mode using /*snapshot execution*/ prefix with an SQL statement. The prefix overrules the value set for the session parameter. When you run a statement with prefix /*snapshot execution*/, only that statement runs in snapshot mode. When using this prefix, the whole query runs in snapshot mode and is not limited to system tables.

Using prefix is not supported for EXECUTE SCRIPT statement.

The prefix is case sensitive and must always be at the beginning of a statement and not preceded by any other comment or white space. The first 22 characters of the statement must be /*snapshot execution*/. If the SQL preprocessor is activated, only the statement text after the preprocessing is checked.

Example:
Copy 
/*snapshot execution*/ 
SELECT * FROM customers;
-- the prefix before the statement allows you to run the command in snapshot mode

Behavior differences

The following table describes the behavior differences when snapshot is on, off, or run with prefix.

Behavior Snapshot mode OFF Snapshot mode SYSTEM TABLES Snapshot mode with prefix /*snapshot execution*/
Locks on system table reads Yes No No
Locks on user table reads Yes Yes No
Locks on system/user table writes Yes Yes Yes
Can be used for SELECT Yes Yes Yes
Can be used for DML (INSERT and similar statements) Yes Yes No
Repeated selects can give different results No Yes (if reading from system tables) Yes