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

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

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

Thu, 28 April 2011 19:05

Eclipse User

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.

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

Mon, 02 May 2011 12:11

Eclipse User

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.

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

Mon, 02 May 2011 13:39

Eclipse User

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.

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

Mon, 02 May 2011 14:55

Eclipse User

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?

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

Mon, 02 May 2011 15:10

Eclipse User

>>
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.

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

Tue, 10 May 2011 21:19

Eclipse User

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

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

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 18:03:00 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter