Snapshot execution mode for metadata queries (preview feature)

Detail name	Value
Changelog Number	10122
Type	Improvement
Status	Resolved
Fix Versions	Exasol 7.0.0, Exasol 7.0.beta1
Resolution Date	2020-07-22

Background

Accessing the database metadata (table/views/column... definition) over driver specific methods is lock free. This is not the case when the same metadata is accessed over SQL interface (select from system tables).

 Problem

Accessing metadata via SQL interface aquires read locks on underlying database objects.
These read locks can cause unwanted transaction conflicts, causing “wait for commit” blocks or “transaction rollbacks” in unexpected locations.

Improvement

• Metadata (only) queries are executed without setting read locks (by using snapshot execution mode)
• non Metadata (only) queries are executed in normal transaction mode (unchanged)
• mixed Metadata and non Metadata queries are executed in normal transaction mode (unchanged) but the metadata parts are accesses lockfree (by using snapshot execution mode)

• e.g. SELECT * FROM sys.EXA_ALL_TABLES

• e.g. SELECT * FROM myschema.mytable

• e.g. SELECT * FROM myschema.mytable JOIN sys.EXA_ALL_TABLES ON ….

How to activate the preview feature

Snapshot execution mode for metadata queries can be activated

• for the whole database
• for the current connection or session

• using the extra database parameter: -enableSystemTablesSnapshotMode=1

• by setting an attribute in the driver:

• JDBC: connection string parameter snapshottransactions=1
• ODBC: connection string parameter SNAPSHOTTRANSACTIONS=Y
• ADO.NET: connection string parameter SNAPSHOTTRANSACTIONS=Y
• WebSockets: setting attribute snapshotTransactionsEnabled to True during login or by using the setAttributes() method

Changed behavior