Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-27

Filter on system tables and transaction conflicts

    Details

    • Type: Explanation
    • Status: Obsolete
    • 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
    • Component/s: EXASolution
    • Explanation:
      Hide

      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)

      Show
      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

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: