[SOL-27] Filter on system tables and transaction conflicts Created: 18.12.2013  Updated: 09.07.2020  Resolved: 09.07.2020

Status: Obsolete
Project: Solution Center
Component/s: EXASolution
Affects Version/s: EXASolution 4.2.2, EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0, Exasol 6.2.x
Fix Version/s: None

Type: Explanation
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: Exasol, SQL, Transaction

Issue Links:
Causing
Related
relates EXASOL-1252 Avoid read-locks on objects when usin... Resolved
is related to SOL-135 Transaction System Obsolete
Explanation:

Note: This solution is no longer maintained. For the latest information, please refer to our documentation:

https://community.exasol.com/t5/database-features/filter-on-system-tables-and-transaction-conflicts/ta-p/1232

Background

Transaction conflicts, WAIT FOR COMMIT or ROLLBACK, each of them is possible. When querying system tables, read locks are implicitly set as follows:

SELECT * FROM EXA_DBA_TABLES;      
-- readlock on all tables

SELECT * FROM EXA_DBA_TABLES 
WHERE TABLE_SCHEMA  = 'SCHEMA1';   -- readlock on tables in SCHEMA1

SELECT * FROM EXA_DBA_TABLES 
WHERE TABLE_SCHEMA  = 'SCHEMA1'
AND TABLE_NAME = 'TABLE1';        -- readlock on table SCHEMA1.TABLE1

This holds even for more complex filters if the following conditions are met:
1) The system table (in this case EXA_DBA_TABLES) is the only one used in the query.
2) The system table appears no more than once in the query (views built on top of this table, e.g. EXA_USER_TABLES, count as well)
3) Only filters which meet the following requirements are evaluated without read-locking all objects contained in the system table (Filter Type A):
a) Only one column is used in the filter
b) This column is filterable (e.g. table_schema, table_name)
c) The filter contains no lookups or references to other tables

Explanation

If filters that violate the constraints above (Type B) are used, we distinguish two cases:
1) Filters of type A and B are combined via AND on the highest filter level: In this case, only objects that passed type A filters are read-locked.
2) Filters of type A and B are combined via OR on the highest filter level: In this case, all objects contained in the system table are read-locked.

Remark: In database versions before EXASolution 4.2.5, it may happen that unexpected read-locks are set when querying particular system tables which in fact are views (e.g. EXA_ALL_TABLES)

Category 1: Database Administration - Transactions
Generated at Sat Aug 15 21:56:57 CEST 2020 using Jira 7.13.13#713013-sha1:4c0f5f5e3383570393fbbf3d2fd5de1eb4057c36.