Can a transaction have many threads?

Question

Generally

We have some business logic that is causing a bottle neck within a transaction. The business logic queries the database for a set of data (read only), processes it, and returns an object. This must be done many times with varying parameters in a given request. Can we theoretically break off each business logic call into a separate thread?

Specifically

EJB object (part of an http request on a JBoss App Server)
-creates objects that implement Callable (call method calls business logic method)
-using an ExecutorService invoke each callable object

Business Logic
-Makes a query of postgresql database which uses a PreparedStatement
-Using POJOs we build objects from ResultSet objects that come from postgresql
-Do expensive calculations

After all of this we get postgres errors that unnamed portals don't exist even when we limit our threads to one:

ERROR:  cursor "<unnamed portal 777>" does not exist
STATEMENT:  FETCH ALL IN "<unnamed portal 777>"

I'm not quite sure what is happening to cause the error, but the business logic is being called correctly and it works fine without threading. This leads me to question whether threads can be started and added to a transaction (and if they can how do WE do it?).

Answer 1

In a JDBC context I'd say that if you're able to share the connection on which you've done the "BEGIN TRANSACTION," and to issue all your calls from there, then yes, it should at least theoretically work.

I'm not normally a fan of stored procedures, but have you considered those? If threading within a transaction is possible, I'd expect it to behave a little more sanely within the database engine than dispersed across the boundary.

Answer 2

You should not create threads within an EJB (or anywhere else within an App server for that matter). If you need to break it down, use JMS or even simpler, use WorkManager to provide concurrency within the EJB based operation.

Answer 3

Though this is probably not the right way to do things this is how to pull this off.

Make the objects that implement Callable not just call the business logic, but do an EJB lookup and call the method. This result looks like this:

EJB object (part of an http request on a JBoss App Server)
-creates objects that implement Callable (call method calls business logic method)
-using an ExecutorService invoke each callable object

Callable Object
-lookup another EJB instance of the class we are already in to get a transaction going
-call that instance's business logic

Business Logic
-Makes a query of postgresql database which uses a PreparedStatement
-Using POJOs we build objects from ResultSet objects that come from postgresql
-Do expensive calculations

I don't much like it which is why I didn't select it as the best answer, but in our specific case it works and I thought it would help others.