ORA-01453 SET TRANSACTION... problem

[LordFjord]

Hello,
I have trouble with the ORA-01453 error message (SET TRANSACTION must be first statement of transaction).
I am using dotConnect Oracle professional version 5.70.146.0. Oracle server is Oracle 10g Express 10.2.0.1.

I have built a wrapper around the dotconnect drivers, so i can use oracle, ms sql, mysql ...

The wrapper starts the connection, handles the transactions and executes command and queries.

In the businesslogic i have a timer that periodically takes objects from a queue and processes the designated queries for these.

During this time, the database connection is kept open, only the transaction is started and committed (or rolled back in case of errors).
The oracle commands are created (with the sql, connection and transaction) for each sql i need to process and disposed after execution.

I am running into the ORA-01453 error at the point where i start the new transaction for the 2nd object, the error occurs. The 1st object is processed correctly (this includes a number of sql queries and commands, all within the transaction).

The transaction is null before i call begintransaction.

I have experimented with closing and reopening the connection for each object processing, without success. I have tried to explicitely call commit (or rollback) on the connection before starting the new transaction. This worked if i trigger the object processing one by one, but runs into the same error if i let it proceed automatically.
My guess is that some race-condition could be involved.
The timer created a new thread each time it runs, so my databasewrapper is called from a different thread each time, while maintaining the same connection and setting up new transactions for each processed object.

I have no trouble at all with the other database server types (mssql, mysql).

The weird thing is, that some time ago this worked, though I cannot tell if its because i updated the dotconnect components to the latest version or i messed something up in my code

I am looking for a hint or idea to solve this problem.

stacktrace:

Code: Select all

Error during BeginTransaction: Devart.Data.Oracle.OracleException: ORA-01453: SET TRANSACTION muss erste Anweisung der Transaktion sein   at Devart.Data.Oracle.bo.b(Int32 A_0)
   at Devart.Data.Oracle.an.a(Int32 A_0, a3 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.OracleTransaction..ctor(OracleConnection A_0, IsolationLevel A_1)
   at Devart.Data.Oracle.OracleConnection.BeginTransaction(IsolationLevel il)

Thanks ahead.

[LordFjord]

I have fixed my problems after many retries, reinstalling devart, rolling back to older versions and so on...

Previously, my businesslogic called the begintransaction, committransaction and rollbacktransaction methods of my database wrapper manually.

Now, i am returning a transaction object to my businesslogic and use it with a using block around the operations, which call my database component.

Initially, i tried to avoid making my businesslogic independant from DbTransaction (or any directly database related objects), seems that didnt work out.

I am still not sure what the cause of my initial problem was, probably multithreading or other race-condition related things.

Anyway, I found a workaround that is acceptable, and it is recommended to use transactions in "using"-blocks anyway.

Maybe this helps others who run into similar issues...

Greetings,
LordFjord

[AndreyR]

Thank you for sharing your knowledge.
Glad to hear the issue is resloved.
The reason of the problem most likely had something to do with multithreading.