Queries to system objects in SNAPSHOT_MODE may cause transaction conflicts
Details
| Detail name | Value |
|---|---|
| Changelog Number | 16148 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 8.22.0 |
| Resolution Date | 2023-08-29 |
Description
Queries to system objects (e.g., system tables like "$ODBCJDBC".ALL_TABLES) take locks and thus can cause transaction conflicts even when SNAPSHOT_MODE='SYSTEM TABLES' is set or within queries that use{{/snapshot execution/}}. This happens if the Exasol compiler internally decides to materializes subqueries.
Preparation
create schema Test; CREATE OR REPLACE TABLE Test.T1(a int);
Example
-- It is necessary to deactivate autocommit for this example:
alter session set snapshot_mode = 'SYSTEM TABLES';
commit;
select * from "$ODBCJDBC".ALL_TABLES;
-- Expected: 0
select count(*) from "$EXA_OBJECT_LOCKS" where OBJECT_NAME in ('T1');
Workaround
There is no workaround.
Fix
Materialized subqueries that only use system objects from schema SYS no longer acquire locks if snapshot_mode = 'SYSTEM TABLES' is set or if the query uses /snapshot execution/.
