Question/Issue with ORM transaction implementation

I ran into an issue this morning where I was getting the lovely "Can
not execute batch update" ORM error when I was saving an entity with
quite a few relationships. The save method was called from within a
handler that was annotated as transactional (using the
TransactionAspect).

The problem is that partial data was still committed to the database
(mySQL) even though ColdBox should of been taking care of the
transaction.

I took a peak at the TransactionAspect code and when I removed the
if(tx.wasCommitted()) clause around the transaction was rolled back as
expected so there seems to be a bug here. What's the purpose of
having the wasCommitted() conditional around the rollback? I've never
seen another example that used this (most just check for tx!=null )
and as I understand it you can't rollback a transaction that has
already been committed anyways so this would never work.

thanks,

.brett

Following up on this, the wasCommited() condition is used throughout
the BaseORMService so this appears to be a widespread issue. I just
tested the transaction in save() and observed the same issue.

I've also managed to figure out my issue, so hopefully this will help
reproduce it.

Basically I have three entities A & B & C. A has a many-to-many
relationship with B. B has a many-to-one relationship with C (C is a
lookup table). I was populating A & B from the RC which lead to a
constraint violation (B's relationship property to C was being set to
an empty string rather than NULL ).

When calling A.save() A will persist and the cascade to persist B will
throw the constraint error and you'll be left with A in your DB
without B. wasCommited() returns false, and the rollback never
happens.

Took me what seemed like forever to find the offending property, the
error thrown by the Hibernate transaction gave me no information about
what constraint was throwing the error (not in logs or console either)
but when I switched to a plain cftransaction CF told me immediately
what constraint it was.

.brett

the problem is that when you call the base orm service save() method, that uses a transaction also which is separate from teh transaction aspect. So that one closes the transaction. If you are using the tranasactional aspect, I would call save() with save(transactional=false) because the default is true.

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

I removed the transaction aspect and called the save method and the
issue is still there.

I don't think wasCommited() will ever return true unless the commit
was successful and at that point you can't rollback anyways, you get
another error (transaction has not started or something similar).

Luis,

I put together a small sample application so you can see the issue.
Setup a datasource called "test" and extract the zip into your
webroot. It's not an CB app but a simple ORM app that simulates the
issue on how CB does transaction rollback.

http://goo.gl/bZPK8

I've only tested it on mySQL.

.brett

hmm, what should I get, I get success!

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

I think the problem you are getting is that you are expecting for hibernate to save C from the A relationships in a cascade manner, but I do not think this will work, I think you need C to be persisted also before anything.
Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

For some reason, C is never persisted for me from the B releationship

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

That is not possible (what DB are you using). You should get a dump
of an error with output stating that wasCommitted() returned false.

No that isn't my problem. I don't have the cascade option on B's C
relationship because I want to force an constraint error on the
database to demonstrate the issue with the way ColdBox has rollback
implemented in it's transactions.

My model in the example is simply to demonstrate that.

It's not supposed to be saved. I purposely don't save C so that we
can't get a db constraint error so that we'll the issue with rollback
not executing.

The commit will fail. We will have a partical commit to the db.

table A will have a record
table B will have a record
but A_B will NOT have the relationship recorded

The error dump will show that there was a constraint violation
updating table B with the relationship to C.

man, I don’t get an error at all, weird!

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

That is weird. Maybe you don't have a FK constraint on your database
(can you check? table B column C should have a FK to C.id )? Are you
using mySQL?

Can someone else on the list run the sample code and see what they
get? Should be an error dump with a partially committed transaction
(the rollback() never executes)

table A will have a row
table B will have a row (Column C should be NULL)
table A_B will NOT have a row

ACF 9.01 & mySQL.

thanks,

.brett

Is this with using ORMService.save()?

I to have brought this up many times in here if that is the case, and the
one thing that has continually annoyed me is the fact this code has its own
transaction wrapped around the actual save.

This causes issues like a connection closed error, especially when you have
a transaction around a block of code that fails. The final save will then
fail because of this.

I have also found that the following block of code will also throw an error,
with this problem when you do something that mixes all this.

Obj = new ORMService.new('entity');
Obj.setName('test')
ORMService.save(obj);

Now in an ORMEvent on preInsert() I have about 4 HQL queries that run
outside of the ORMService.save() which again throws, session closed error.

And the only way to fix this under all circumstances is to remove the
transaction code from around the EntitySave. I am sorry but in my opinion if
you are going to managing the saving of the data like this, then you should
be managing inside the application and not the Save like this.

I have come to the conclusion that ORMService can be used some very basic
stuff, but when you start building a very large scaled application this
Service is not up to the task, and that is because it assumes that you will
not take responsibility for the session management of the ORM Session.

So now I have my own service, that does the same thing saves the object but
doesn't have the problems that the core service has.

Regards,
Andrew Scott
http://www.andyscott.id.au/

From: coldbox@googlegroups.com [mailto:coldbox@googlegroups.com] On
Behalf Of Brett
Sent: Saturday, 9 April 2011 3:39 AM
To: ColdBox Platform
Subject: [coldbox:9316] Re: Question/Issue with ORM transaction
implementation

It's not supposed to be saved. I purposely don't save C so that we can't

get a

db constraint error so that we'll the issue with rollback not executing.

The commit will fail. We will have a partical commit to the db.

table A will have a record
table B will have a record
but A_B will NOT have the relationship recorded

The error dump will show that there was a constraint violation updating

table

B with the relationship to C.

> For some reason, C is never persisted for me from the B releationship
>
> Luis F. Majano
> President
> Ortus Solutions, Corp
>
> ColdBox Platform:http://www.coldbox.org Linked
> In:http://www.linkedin.com/pub/3/731/483
> Blog:http://www.luismajano.com
> IECFUG Manager:http://www.iecfug.com
>
>
>
>
>
>
>
> > I think the problem you are getting is that you are expecting for
> > hibernate to save C from the A relationships in a cascade manner,
> > but I do not think this will work, I think you need C to be persisted

also

The issue I reported wasn't specific to ORMService.save() but in all
the orm transaction code in 3.0. Luis just checked in the fix for it
(thanks Luis!)

But I agree with your points. I'm hoping to use the transactionAspect
a lot which means having to remember to add transactional=false to
each save call. This should probably be defaulted to false, I'll
probably end up overriding it and changing the default.

Also - Luis, you added the transactional argument to the save() method
but what about the other functions that have transactions in them
(saveAll, delete, deleteAll, deleteById). Wont those have the same
problem save() had with the transaction aspect?

.brett

Great point: https://coldbox.assembla.com/spaces/coldbox/tickets/1202-new-orm-services-property–usetransactions%28true%29-which-tells-the-service-to-use-or-not-hibernate----#last_comment

Almost done.

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Also, all methods get a transaction argument, just in case.

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

But I believe the transaction attribute for these should default to false, 99% of people are going to be the ones who leave it as default management. Which means that save(entity=object, transaction=false), would be the more common code.

I too keep overriding your changes to default to false the save(), even when I manage the ORMSession myself.

Regards,

Andrew Scott

http://www.andyscott.id.au/

With the property it becomes your choice though?

setUseTransactions(false)

or init(useTransactions=false)

Luis F. Majano
President
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

I think Luis is creating a property of the base orm service to turn
this off/on instance wide rather than relying on an argument (that is
what I read from the ticket description).

If that is true it makes me wonder how this will work with autowire
since the DSL doesn't seem to support properties in its DSL. Maybe
there is a way... I dunno.

I still agree the more sensible approach is to have transactions off,
but I can deal with it.

.brett