IMPORT statement with ERRORS INTO clause can cause transaction rollbacks

Detail name	Value
Changelog Number	10936
Type	Bug
Status	Open
Affected Versions	Exasol 7.1.0, Exasol 8.0.0

Description

IMPORT Statements may cause transaction rollbacks if the following conditions are fulfilled:

• IMPORT Statement contains an ERRORS INTO clause
• One of the rows being imported must receive an error that tries to write the specified error table
• Another transaction holds a write-lock on the error table (IMPORT, UPDATE, INSERT, DELETE, etc.)

Workaround

You can lock the error table in advance by executing the following statement before the IMPORT.
<ERROR_TABLE> refers to the table mentioned in the ERRORS INTO clause

@set autocommit off;
DELETE FROM <ERROR_TABLE> WHERE FALSE;
IMPORT INTO <TARGET_TABLE> FROM .... ERRORS INTO <ERROR_TABLE>;
COMMIT; 

Fix

These scenarios will no longer receive transaction rollbacks. 

A write-lock will instead be acquired on the target table and the errors table (if there is one specified) as soon as the IMPORT statement starts. If there are concurrent sessions writing errors into the same table, they may have to wait for commit.