Re: Confusing conflicts between OpenJPA and Postgresql

Vito wrote:
> I'm recently doing some research with Apache ODE engine. I use Postgresql as its external database,
> openjpa as its ORM solution and bitronix as its transaction manager. The ODE workflow engine starts
> without any problem. But when I deploy process definition files into the engine, the exception came
> out. I paste the error content at the end of this email.
> I looked through the source code of org.apache.openjpa.lib.jdbc.DecoratingDataSource, and found that
> openjpa is almost definitely to meant to invoke the function of reseting transaction isolation level,
> which would be catched by Postgresql and return with an exception.
> Does that mean openjpa works poorly with Postgresql? Or who can tell me how to solve this problem.

> The error content is below:

> org.apache.openjpa.persistence.PersistenceException: Cannot change transaction isolation level in the
> middle of a transaction

> Caused by: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle
> of a transaction

That makes sense, doesn't it?
I don't think there is any database that would allow to change the isolation level
in the middle of a transaction.

I tested with Oracle:

SELECT ....

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction

I think you will have to make sure that the SET TRANSACTION ISOLATION LEVEL
statement is the first statement issued in the transaction.

Yours,
Laurenz Albe