wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes updating db after lengthy paypal transaction - transactional integrity Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "updating db after lengthy paypal transaction - transactional integrity" Watch "updating db after lengthy paypal transaction - transactional integrity" New topic
Author

updating db after lengthy paypal transaction - transactional integrity

Brendan Healey
Ranch Hand

Joined: May 12, 2009
Posts: 218
I have a JSF/EJB/JPA application which uses container managed persistence. There is
one case where a call is made to paypal via HTTP which results in a balance being adjusted
for the user in a database. The thing is these paypal transactions can take ages, up to
30 seconds. To give me finer grained transaction control I'm using bean managed
transactions in this case. This is some pseudo-ish code for what I've got now:



So the idea is that I start a transaction, assume success and increase the user balance,
call the http service and commit if it succeeds or rollback otherwise.

I have an uneasy feeling that I may not be anywhere near the right ballpark with this design,
particularly having the lengthy http call (actually done using jax-rs) inside the pessimistic_write
transaction. I clearly can't write lock the user record for 30 seconds while the credit card is
being processed, but unless I make the paypal call within the transaction I could do the paypal
call, it succeeds, I then try to increase the user balance but the database is shutdown - no
transactional integrity.

This is new territory for me, can anyone point me in the right direction, is there an established
way of doing what I'm trying to do? I feel that I'm missing something obvious.

Many Thanks.

p.s. I'm using a glassfish 3.1 stack with Seam 3
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
With your database and pay-pal you basically have two data sources that you need to commit as a single atomic transaction. But you don't have two-phase commit with PayPal, so you need to improvise.

The best option is to create a pending transaction table, and commit the record of the payment to this table first. Then commit to paypal, then commit you your normal table and remove from the pending table.

This way you always have a record of the transaction even in the event of a failure. Also, you avoid blocking your database for a long duration.

Another option is to flush the change to your database, but not commit the transaction until after the paypal succeeds. This reduces the failure window to be very unlike, which is really the best that you can ever get, as there could always be some sort of hardware or disk failure that wipes away data (this world is never perfect, worst case some see a bogus transaction on there paypal and calls to have it fixed).


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Brendan Healey
Ranch Hand

Joined: May 12, 2009
Posts: 218
Thanks James, that's a great answer, I will re-work this as you suggest.

Regards,
Brendan.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: updating db after lengthy paypal transaction - transactional integrity
 
Similar Threads
SessionContext . setRollbackOnly() method doubt.
When commit transaction
Using Datasource with and without transaction
Booking Transaction
CMT EJB method that never returns