Page 1 of 1

OracleLoader 8.4: COMMIT is not allowed in a subordinate session

Posted: Tue 27 Jan 2015 16:13
by Thorium
Devart.Data.Oracle version 8.1.36 class OracleLoader did work correcty.

But when upgrading to version 8.4.313 it started to throw exception:
Devart.Data.Oracle.OracleException: ORA-02089: COMMIT is not allowed in a subordinate session
ORA-06512: at "SYS.DBMS_UTILITY", line 574
ORA-06512: at line 17
at Devart.Data.Oracle.a9.c(Int32 A_0)
at Devart.Data.Oracle.cu.a(Int32 A_0, cj A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at Devart.Data.Oracle.v.a(Boolean A_0)
at Devart.Data.Oracle.v.b()
at Devart.Data.Oracle.ax.e()
at Devart.Data.Oracle.OracleLoader.Open()
This happens when calling .Open()-method inside DTC-transaction (using .NET Transaction-class).

Re: OracleLoader 8.4: COMMIT is not allowed in a subordinate session

Posted: Wed 28 Jan 2015 16:53
by Pinturiccio
We could not reproduce the issue. Please provide us the following for reproducing the issue:
1. Oracle server and Oracle Client versions;
2. DDL script of the table;
3. Is there a Foreign Key in this table or to this table?

Also, please create and send us a small test project, if possible.

Re: OracleLoader 8.4: COMMIT is not allowed in a subordinate session

Posted: Mon 23 Feb 2015 14:31
by Thorium
It seems that the new 8.4 version of the Devart OracleLoader class uses "DISABLE CONSTRAINT" which is not ok in multi-threaded environment where the table is in use by other threads. The generated failing SQL is:

Code: Select all

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name, c.constraint_type
   FROM user_constraints c, user_constraints r
   WHERE c.constraint_type = 'R'
   AND r.owner = c.r_owner
   AND c.r_constraint_name = r.constraint_name
   AND r.table_name = 'MY_TABLE'
   AND c.status = 'ENABLED'
   UNION ALL
   SELECT c.owner, c.table_name, c.constraint_name, c.constraint_type
   FROM user_constraints c
   WHERE c.constraint_type = 'P'
   AND c.table_name = 'MY_TABLE'
   ORDER BY 4 DESC)
  LOOP
    DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLE "' || c.owner || '"."' || c.table_name || '" DISABLE CONSTRAINT "' || c.constraint_name || '"');
  END LOOP;
END;
Another exception what is raised is like:

Code: Select all

Devart.Data.Oracle.OracleException (0x80004005): ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 2
   at Devart.Data.Oracle.a1.c(Int32 A_0)

Seems that the 8.1 version did not do this.

Do you have any suggestions for a workaround?

Re: OracleLoader 8.4: COMMIT is not allowed in a subordinate session

Posted: Thu 26 Feb 2015 13:00
by MariiaI
It seems that the new 8.4 version of the Devart OracleLoader class uses "DISABLE CONSTRAINT" which is not ok in multi-threaded environment where the table is in use by other threads
dotConnect for Oracle is not thread safe, so you should use a separate connection for each thread or synchronize the threads yourself.
ORA-02089: COMMIT is not allowed in a subordinate session
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
We couldn't reproduce these errors with the latest build of dotConnect for Oracle.
Please provide us the following information:
1. Oracle server and Oracle Client versions.
2. DDL script of the table.
3. Is there a Foreign Key in this table or to this table?

Also, please create and send us a small test project, if possible. This will significantly speed up the process of determining the cause of the error and finding the solution for it.