Re: autoCommit handling (2.1.1)

[pveselov <pa...@gmail.com>]

Hi.

So, I spent some time fixing a bug in Postgres connection pool data source
(the data source for providing connections suitable for a connection pool),
auto-commit wasn't properly set there, thinking that that's the problem.

But, OpenJPA has it's own mind on managing auto-commit. And it seems that it
better be 'true' for Postgres. The decision to turn auto-commit on or off is
made when the DB dictionary is applied to the connection. The auto-commit is
not changed at all if the datasource is considered "managed", but that's
typically intended for cases when the connections that are retrieved from
the data source are enlisted into the global transaction by the data source
implementation (so no transaction management by OpenJPA is expected).

So, if the connection factory is "local", the auto-commit flag is set based
on the DB dictionary property of 
"supportsMultipleNontransactionalResultSets". I don't think that short of
changing source code, or implementing your own dictionary, that property can
be influenced.

And, on top of all this, Postgres doesn't play well with OpenJPA having
auto-commit mode off. That's because at least in some cases, OpenJPA will
throw queries at it that may fail. But OpenJPA has an expectation that
further queries will succeed, but, in fact, Postgres will require 'rollback'
to be issued on the transaction before any next query will succeed.

So, it seems like it's generally a very bad idea to have any expectation of
auto-commit flag value, or try meddling with it. My problem was that
Postgres doesn't allow to use LOM API if auto-commit is 'true', so I ended
up making sure that all LOM operations are done on a separate connection,
that is retrieved directly from the data source (and then has auto-commit
set false on it), rather than using connection extracted from an entity
manager.

Thank you.


hvieren wrote
> to learn more , i have following question:
> 
> i always see autoCommit on connection as  true , why ?

[skipped]

hvieren wrote
> one would think that autoCommit would be false on connection got when
> transaction is active.
> pveselov wrote
>> 2013/7/2 Pawel Veselov [via OpenJPA] <

>> ml-node+s208410n7584317h75@.nabble

>> >:
>>> Hi.
>>>
>>> I'm trying to understand how exactly OpenJPA does handle auto commit
>>> flag
>>> on connections.
>>> I've made sure that my connection pool always creates connections and
>>> has
>>> autoCommit set to false on them (it's Postgres, so by default, those
>>> come
>>> out with autoCommit=true). These are non-XA connections and/or pools.
>>> However, the pool doesn't force connections to be autoCommit=false (even
>>> though it's been asked to), when re-using previously established
>>> connection.
>>>
>>> I also use JPA transaction manager.
>>>
>>> I needed to use Postgres large object manager, and that's when I found
>>> that
>>> the connections had autoCommit=true (LOM will not even produce an API if
>>> that's so).
> 





--
View this message in context: http://openjpa.208410.n2.nabble.com/autoCommit-handling-2-1-1-tp7584317p7584825.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.