Hi Roger,

I don't know anything about your project or environment, but I've been
doing some testing with PostgreSQL today and I'm finding it seems to
work much better with OpenJPA than MySQL.

I've recently come out of the corporate world, and Oracle was our
"standard" database platform. I've just started working with open
source db platforms, and I'm finding MySQL has some limitations that
might prove problematic when using OpenJPA and programming robust
database-backed web applications. I know it's a supported platform,
but I'd be willing to bet there are a lot more caveats (and remaining
bugs) when using MySQL as opposed to Oracle or PostgreSQL.

I'm sure some of the experts on this list can speak to this point, but
MySQL  apparently does not support deferred foreign key constraints.
In my particular use case, I was trying to merge a detached object
that contained a reference to another detached object. The cascade
update seemed to succeed only if both detached objects contained
changes.

Adding the @Version annotation and columns to my tables fixed the
problem, but then I ran into an intermittent 'Optimistic Lock'
exception when attempting to remove the same type of detached object.
It appeared to be a race condition, as sometimes the delete would
succeed, and other times it would fail. I'm speculating that this
occured because I had cascade delete enabled on my foreign key
constraint and CascadeType.ALL enabled for my referenced object.

Since I was using MySQL, the cascade delete on the foreign key
constraint couldn't be deferred until transaction commit. It would
sometimes execute prior to OpenJPA execution of the 2nd delete
statement. I believe that's where the (legitimate) intermittent
optimistic lock exception was coming from for this removal use case.

I've just finished setting up my database, schema, and tables on
PostgreSQL 8.2.4. I'm finding that with deferred foreign key
constraints enabled, my cascading issues seem to have disappeared. As
an added bonus I was able to revert to the initial code I created for
my DAOs because JPA was working without any additional (and seemingly
unnecessary) manipulation of my referenced objects.

If alternate database platforms are an option, I'd recommend looking
at PostgreSQL. Support for deferred constraints, plus better handling
of BLOBs and full support for multi-version concurrency control
(MVCC).

Cheers,
-jmh

On 4/29/07, roger.keays <[EMAIL PROTECTED]> wrote:


Jason Hanna wrote:
>
> org.apache.openjpa.persistence.RollbackException: Optimistic locking
> errors were detected when flushing to the data store.  The following
> objects may have been concurrently modified in another transaction:
> [com.coincident.green.beans.User-com.coincident.green.beans.User-1]
>

I've just fixed a similar issue in my app [1]. Maybe it is the same problem
with foreign key constraints [2]

[1] http://www.nabble.com/forum/ViewPost.jtp?post=10247539&framed=y
[2] http://www.nabble.com/forum/ViewPost.jtp?post=7395124&framed=y
--
View this message in context: 
http://www.nabble.com/Merge-Detached-Entity-Problem-tf3658825.html#a10247785
Sent from the open-jpa-dev mailing list archive at Nabble.com.


Reply via email to