In our architecture the stateless session bean calls the service (POJO) which in turn calls the DAO which does the DB operation.Taking the classic debit/credit example how should the transaction management be controlled if either of the debit or credit operation fails? We are using stored procedure instead of direct SQL's.To clarify further we want to rollbackt the entire process of debit if credit fails and vice versa.
It depends on the actual need of requirement.Use the container managed transaction. Since you are using stored procedure, make sure that their is no rolback or commit inside stored procedure. It also depeneds on the code of stored procedure that in what way stored procedure is handling exception and failure. If your stored procedure returns some custom error code then you need to handle that and in such cases User transaction would do the task better. you are the one who can better decide what suits to your environment
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein