Due to the slowness of the application, we made some of our long running queries asynchronous. Problem is these are part of a single transaction but in case if these queries/routines fail I need to roll back everything. How to achieve this? This application is legacy application using JDBC oracle and java 8. Also like to know if there is any support for this in Springboot, jpa application.

Thanks in advance.

CodePudding user response：

• Don't try to interact with the same DB connection from multiple threads at once. JDBC's connection system isn't specced to let you do this.
• A transaction belongs to a single connection. You can't smear it out over multiples.
• The obvious way to ensure that 'it is all rolled back' is to have a single long-lived transaction (but see later).

Combine these 3 facets and you end up with: Do all work in the async block. At least, all work that either needs to all happen, or none of it happens (i.e. the one transaction).

Any other basic approach wouldn't work or wouldn't be useful; there's no point freezing the main thread to wait for the async task (just do the async task on the spot; moving code to another thread doesn't magically make it go any faster. On the contrary, in fact).

However, transactions that aren't just long lived, but make a ton of changes to a DB is its own problem, but we now we're getting into the performance characteristics of your specific batch of queries and your particular DB engine, version, indices, and data. Kinda hard to answer with specifics, what with all those unknowns.

There are ways to design your DB to deal with this (mostly involving a table representing a calculation, and having a row indicate whether the calculation is complete or not. As long as you aren't done, dont set it to 'completed', and all your queries should ignore non-complete results. Upon bootup, delete (and with it, let that cascade) any non-complete results: Those must be half-baked work done right before your server crashed, and now you've restarted it). It's probably not the right answer here, just making sure you're aware that such options also exist.

As a general rule of thumb, countering a problem of "Our code has been observed to run too slowly" with "lets make it all async" doesn't work. async makes code harder to read, way harder to debug, and doesn't make stuff go faster. All you can really do with async is soothe the user by playing them some elevator music or slightly more pragmatic: A progress bar or whatnot, whilst they wait. And that's actually generally easier by spawning off the bits that tell the user what's happening into a separate thread, instead of asyncing the work itself. That, and make your algorithm better and/or fix your DB index definitions. You can search the web for that too; run EXPLAIN variants of your queries to make the DB tell you whether it is using any table sweeps (that's where it goes through the entire dataset before it can answer a query. You want to avoid those).

If you need help with either of those parts (show the user what is going on, instead of freezing the webpage or freezing the GUI / how to optimize a DB query), search the web for this information, there are tons of tutorials. Make sure to include the frontend tech; java can be used for swing apps, javafx, android, and there are at last count like a 100 web frameworks.