Eclipse Community Forums: EclipseLink » EJB transaction does not rollback stored procedure changes

Help

Home

Home » Eclipse Projects » EclipseLink » EJB transaction does not rollback stored procedure changes

Show: Today's Messages :: Show Polls :: Message Navigator

EJB transaction does not rollback stored procedure changes [message #667469]

Thu, 28 April 2011 23:05

Kirill

Messages: 7
Registered: April 2011

Junior Member

We have a stored procedure (SP) that is called inside EJB. The SP inserts and selects records, and returns the result of the select to a server side. It does not manage transaction, so it is a responsibility of EJB. Nevertheless, the SP gets committed though EJB transaction is rolled back.
We tried both container and bean managed transaction (via UserTransaction) and result was the same. When in debug mode we can see that changes are pushed to database before end of EJB-method is reached.

If we do standard JDBC transaction within our EJB the SP is rolled back, but we don't want to sacrifice JPA over JDBC.

Report message to a moderator

Re: EJB transaction does not rollback stored procedure changes [message #667895 is a reply to message #667469]

Mon, 02 May 2011 16:11

Kirill

Messages: 7
Registered: April 2011

Junior Member

To get anybody's attention I thought that additional details about our environment might be heplfull. We're using EclipseLink 2.1.0 with GlassFish server 3.0.1. The database server is SQL Server 2008 R2.

Report message to a moderator

Re: EJB transaction does not rollback stored procedure changes [message #667904 is a reply to message #667895]

Mon, 02 May 2011 17:39

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

How are you executing the procedure?

By default in EclipseLink if you execute a Query through getResultList() or getSingleResult() (ReadQuery) it is assumed to be non-transactional, and may use an auto-commit connection if you have specified a read connection pool, or are not using JTA.

If you execute the Query using executeUpdate() (DataModifyQuery) that it will be transactional.

If this is the case, then you can initiate a transaction either by call beginEarlyTransaction() or the UnitOfWork before executing the query,

em.unwrap(UnitOfWork.class).beginEarlyTransaction();

or, setting the query hint,
"eclipselink.exclusive-connection"="true"
should work

A flush (that results in DML) or DML execution will also have the same effect.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: EJB transaction does not rollback stored procedure changes [message #667920 is a reply to message #667904]

Mon, 02 May 2011 18:55

Kirill

Messages: 7
Registered: April 2011

Junior Member

Hi James,

Thanks for you answer. I think there is some missanderstanding, let me try to clarify.

We have to use getResultList() since the SP returns results, but at the same time the same SP performs inserts (basically it creates objects in our DB and returns some of them). With executeUpdate() we won't be able to return the results.

The transaction is managed inside the EJB, so there are some changes performed by the bean and changes done by SP. All of them should be in one transaction, but currently the SP gets committed immediately, before end of bean's method is reached.

Can we somehow manage EclipseLink to handle SP without committing transaction?

Report message to a moderator

Re: EJB transaction does not rollback stored procedure changes [message #667925 is a reply to message #667469]

Mon, 02 May 2011 19:10

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

>>
em.unwrap(UnitOfWork.class).beginEarlyTransaction();

or, setting the query hint,
"eclipselink.exclusive-connection"="true"
should work

A flush (that results in DML) or DML execution will also have the same effect.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: EJB transaction does not rollback stored procedure changes [message #669633 is a reply to message #667925]

Wed, 11 May 2011 01:19

Kirill

Messages: 7
Registered: April 2011

Junior Member

Hi James,

Just want to thank you! Utilizing of UnitOfWork.class with beginEarlyTransaction() works. Of course it's EclipseLink-specific API but there is no other way as I understand.

Thank you,
Kirill

Report message to a moderator

Previous Topic:	Override the propertyChange method of AttributeChangeListener Class
Next Topic:	@AttributeOverrides gives me exception

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:28:09 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter