Snapshot execution mode for metadata queries (preview feature)
Details
| 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
When enabled, fully serializable transaction level will be not applied for simple selects on system tables and underlying objects.
