Deadlock in SQL loader while loading single file in multiple tables

Question

While loading a single file into multiple tables with SQL loader we get the following error:

SQL*Loader-961: Error calling once/load finishing for table TABLE15
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Info and things I've tried so far:

The SQL loader exe is started from a .NET windows service, but the service does nothing while it waits for the exe to finish.
Oracle 12c database.
The only constraint and index on the TABLE15 (and any other import table) is a primary key + index on the ID column. These are very basic 'temporary' tables, only used to bulk import the data into them. Business logic, logging of problematic data and updating the 'final' tables from the 'temporary' ones happens afterwards.
We only get this error in one of our testing environments (which has a less powerful database than production), but the same file imports just fine in production and another testing environment.
Even when making sure that the SQL loader exe is the only process that accesses the database, the import still fails, so it seems like SQL loader is generating its own deadlock.
The file contains a little under 35000 records (csv file is a little under 2kb) and over 95% of the records are for TABLE15. We have much bigger files that import just fine, but those import a single file into a single table.
If I split up the file into two files, one with data for TABLE15 and one with the other data, both files process just fine. The single file always fails and split up in two files it always succeeds.
If I alter the control file and move the TABLE15 part to the start of the file, the same error is thrown, but on TABLE10 instead (which is futher down the control file).
If I split up the file in two roughly equal files, only the file that starts with TABLE15 data (original file contains the different types sequentially) fails on a TABLE15 deadlock (both when it's the slightly larger and the slightly smaller file).
If I put the TABLE15 data at the end in a single import file, it fails on a TABLE15 deadlock.

The control file looks like this:

OPTIONS (SKIP=1)
LOAD DATA
  INFILE *  
  INTO TABLE TABLE01 WHEN TYPE = 'TYPE01'
  FIELDS TERMINATED BY ","
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
    ( ID DECIMAL EXTERNAL,
      TABLE01_COL1 CHAR, 
      TABLE01_COL2 CHAR "TRIM(:TABLE01_COL2)",
      TABLE01_COL3 CHAR,
      TYPE FILLER
    )  
  INTO TABLE TABLE02 WHEN TYPE = 'TYPE02'
  FIELDS TERMINATED BY ","
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
    ( ID DECIMAL EXTERNAL,
      TABLE02_COL1 CHAR, 
      TABLE02_COL2 CHAR "TRIM(:TABLE02_COL2)",
      TABLE02_COL3 CHAR,
      TYPE FILLER
    )
  [...]
  INTO TABLE TABLE17 WHEN TYPE = 'TYPE17'  FIELDS TERMINATED BY ","
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
    ( ID DECIMAL EXTERNAL,
      TABLE17_COL1 CHAR, 
      TABLE17_COL2 CHAR "TRIM(:TABLE17_COL2)",
      TABLE17_COL3 CHAR,
      TYPE FILLER
    )

Does anyone have any idea what the source of this problem could be (and how it could be solved)? As far as I can tell it seems to be some kind of combination of filesize, multi-table import and a less powerful database.

@psaraj12 no, only a primary key on the ID columns (I'll add this info to the question) — Sam
Are there any triggers on the tables? Are any foreign keys non-indexed while you update any primary key via trigger? — Lalit Kumar B
@LalitKumarB Nothing special on those tables other than an index. They have been kept simple intentionally to keep the bulk import simple. If we need any complicated logic, it is applied afterwards. — Sam

ibre5041 ibre5041 · Accepted Answer · 2015-02-12T10:38:15

You have to contact DBAs and request deadlock report tracefile. This file is generated whenever deadlock occurs. Without this file nobody can help you.

PS: I already saw situations when a single DB connection caused a deadlock in the database. Due to performance problems some kind of "internal timeout" interrupted SQL insert and this problem was reported as a deadlock. There was only one session in the deadlock report and the type of the lock was different from usual ones (SX,SSX,TX).

Deadlock in SQL loader while loading single file in multiple tables

1 Answers