Exasol allows you run SQL statements in snapshot mode to avoid transaction conflicts. The feature is useful for frequent queries to the system tables for metadata. Such queries may 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 Exasol's system tables.
To avoid delayed execution in such a scenario, you can run your 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 (latest committed version).
Using the Snapshot Mode
The snapshot mode is enabled for the system tables by default by parameter SNAPSHOT_MODE, therefore, all your queries to the system tables will run in snapshot mode. The parameter determines how all queries within the session are executed. You can change the behavior either by using ALTER SYSTEM or ALTER SESSION.
You can enable or disable the snapshot mode for the current session using ALTER SESSION. If the snapshot mode is enabled, all queries to the system table run in snapshot mode during the current session.
--enable snapshot mode for system tables in a session
ALTER SESSION SET SNAPSHOT_MODE = 'SYSTEM TABLES';
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.
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), Connecting through Connection Strings (ODBC), or Using the Data Provider (ADO.NET).
Using /*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. The behavior of this method is different from all the above methods because with this prefix, the whole query runs in snapshot mode and it's not limited to system tables anymore.
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 be 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, then only the statement text after the preprocessing is checked.
SELECT * FROM customers;
-- the prefix before the statement allows you to run the command in snapshot mode
The following table describes the behavior differences when snapshot is on, off, or run with prefix.
|Effects||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|