[SOL-214] WAIT FOR COMMIT on SELECT statement Created: 15.01.2015  Updated: 06.04.2021  Resolved: 07.08.2020

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

Type: Explanation
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: Transaction

Issue Links:
Causing
Related
is related to SOL-135 Transaction System Obsolete
Solution:

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

https://community.exasol.com/t5/database-features/wait-for-commit-on-select-statement/ta-p/1717

Background

Cause and Effect:

Since EXASolution transaction isolation level is SERIALIZABLE and newly created transactions are automatically scheduled after finished transactions, it is possible that WAIT FOR COMMITS occur for pure read transactions (consisting of SELECT statements, only). For further details on EXASolution TMS please refer to SOL-135.

Explanation

How to reproduce:

Three different connections (having AUTOCOMMIT off) are needed to reproduce this situation:

Example 1:

If a long running transaction (Tr1) reads object A and writes object B (e.g. long running IMPORT statements) and a second transaction (Tr2) writes object A and commits in parallel, Tr2 is scheduled after
Tr1. AfterTr2 is commited all new transactions are scheduled after it. If such a transaction wants to read object B it has to wait for the commit of Tr1.

Transaction 1 Transaction 2 Transaction 3 Comment
select * from tab1;      
insert into tab2 values 1;      
– transaction remains opened      
  insert into WFC.tab1 values 1;   Transaction 1 < Transaction 2
  commit;    
    commit; Starts a new transaction (Transaction 2 < Transaction 3)
    select * from tab2; This statement ends up in WAIT FOR COMMIT, waiting for Transaction 1

Note:
The read lock of Transaction 1 above may also be caused when reading meta data using system tables (see SOL-27).

Example 2:

The same situation may occur if you query system tables while SqlLogServer is performing one of its tasks (e.g. "DB size task" determining the database size). The following example describes this situation:

Transaction 1 LogServer Transaction 3 Comment
select * from EXA_DB_SIZE_LAST_DAY;      
insert into tab1 values 1;      
– transaction remains opened      
  – DB size task (writes EXA_DB_SIZE_LAST_DAY)   Transaction 1 < LogServer transaction, the task is executed every 30 minutes (0:00, 0:30, 1:00, 1:30, ...)
    commit; Starts a new transaction (LogServer transaction 2 < Transaction 3)
    select * from EXA_DB_SIZE_LAST_DAY; This statement end up in WAIT FOR COMMIT

Solution

Currently, the only solution to this is to break up Transaction 1 into multiple transactions by performing a COMMIT or ROLLBACK after the initial read access.
However, things may get more complicated when the read/write operation is concentrated within a single statement (ie. MERGE or INSERT from SELECT). In the latter case it has proven helpful to 'outsource' the reading part by using IMPORT as a subselect to fetch required data through a separate transaction...

Category 1: Database Administration - Transactions

 Comments   
Comment by Harshini Rangaswamy (Inactive) [ 30.03.2020 ]

Hi Mathias Brink, you could share the doc page where you found the link to this solution?

Generated at Sat Oct 23 22:52:59 CEST 2021 using Jira 7.13.18#713018-sha1:e1230154f8ff8cc9272975bf568fc732e806fd68.