Best way to manage transactions

Question

I've the JBoss and Hibernate based system. And I need to process two long operations. Operations can be probably longer than transaction's timeout. It operations are persists many-many entities, in two different transactions. And if something goes wrong, during this operations, I should rollback all changes of the transactions.

What's the best way to resolve it?

I think, the best way is merge all operations to one transaction, but it requires to set LOng transaction timeout, and it unacceptable for our system.

Is the managing of many transactions better in this situation. And how can I do it?

Answer 1

Using one transaction is good, robust and so on. Try again to convince your administrator ;-)

---

Otherwise, I can think of:

• using shorter transactions to insert fewer rows, using a marker to mean "incomplete". This could be in an existing column or a new one (maybe it could be in a different table?).
• if any transaction rollbacks, try it again ; if you prefer to abort the whole operation, you can choose delete the rows that have the "incomplete" marker.
• when all transactions are commited, you can open a last transaction, just for marking all rows as "complete" (ex: update ... where ...). This should be much faster than the full insertion was, so the transaction should be short enough.

Answer 2

Can you use a JTA server (transaction coordinator) to do the transaction in multiple steps with XA transactions? This posting has a bit of fan-out to a couple of open-source ones that might work for you.

XA transactions allow you to do a bunch of smaller operations and commit or roll back them in one hit. Try googling for 'hibernate xa transactions'. Most modern RDBMS platforms (MySQL 5.x, PostgreSQL, SQL Server, Oracle, DB2, Sybase etc.) support XA transactions.

Answer 3

I had to do something similar in JPA (via hibernate). I did the operation in N transactions rather than 1 big transaction. This operation ran rarely - it was an automated job - so there was no user waiting at a web page for a result.

How long do you expect the transaction to take? How long do you expect the user to wait?? How often do you expect this operation to occur. Maybe you're requirements are not clear.