Setting context in a JPA connection — is this safe?

Question

I need to set some context before every database operation (I have tried using Oracle's package-level variables, but due to some issues with package recompilation, I will experiment with DBMS_SESSION and/or DBMS_APPLICATION_INFO), so that we can get specific user info anywhere we need it (procedures, triggers, etc.), instead of having dozens of database connections identified as "JBoss".

I have written a Java EE interceptor that intercepts calls to a @Stateless bean. It calls an Oracle function to set some session context (look at this question for some sample code How to tell if a transaction is active in a Java EE 6 interceptor).

My first worry was connection pooling. At first I thought that the default @PersistenceContext propagation provided by Java EE would be enough to guarantee that everything runs in the same connection/transaction/EntityManager, and I only had to unset everything at the end of my interceptor (in a finally block) before the connection is returned to the pool. It seemed a little fragile, but I thought it could work.

Then I found out that Hibernate has a property called hibernate.connection.release_mode (Hibernate docs about hibernate.connection.release_mode, Red Hat docs about org.hibernate.ConnectionReleaseMode) and that the default and recommended behavior when using JTA transactions is to release the connection after every statement (although the docs say something about re-acquiring the same underlying connection, which just confused me).

Now I'm not even sure I can reliably set something in an interceptor that will be visible only to this user/operation without the risk of somebody else grabbing the same connection in the middle of my business method and making a mess with my user context. As I understand it, Oracle database session variables are kept for a connection, not for a transaction or a @PersistenceContext (the database knows nothing about the persistence context after all, and a connection can be used for multiple transactions).

I'm about to give up because this is looking more and more fragile as I learn more about the implementation details of all the technologies involved. Can this user context idea be made to work or should I try a totally different approach? And how could I test/debug my implementation to be sure there aren't any concurrency issues lurking around? I haven't found any useful event listener to monitor the framework behavior, and building a program to stress test the server is too much work to invest in something I'm not sure is supposed to work anyway.

I'm using JBoss AS 7.1, EJB 3.1, an Oracle 10g database, and JPA 2.0 (backed by Hibernate, although we do not use any Hibernate-specific API).

Answer 1

I would personally avoid trying to set individual parameters on a JDBC connection from a pool. After all, the idea of a pool is that all connections are identical. So while your interceptor idea would work I am also worried about how fragile it would be. Any bugs in that implementation would be the nastiest sort of race conditions.

On the other hand, because you're using Oracle you might want to look at EclipseLink. It implements JPA2 and was heavily funded by Oracle so it supports all their bizarre features. You might want to look into using 'Isolated Client Sessions'. It looks to support Virtual Private Databases which require individual session set up. So that would be a solution if you need to change session context.

Answer 2

So I'll answer my own question with all the things I have found out lately.

In order to understand server behavior, I changed my JBoss configuration to use a pool of only 1 connection, so I could detect when somebody else is blocked waiting.

If the current operation is inside a transaction (e.g. @TransactionAttribute(REQUIRED)), the connection won't be used for anything else until the transaction finishes (other clients will have to wait). However, if you read the database without a transaction, other clients could grab the same connection while you are not using it, even before your business method finishes (I don't know how much of this behavior is standard and how much is an implementation detail).

Hibernate does release the connection after every statement by default and that's why the connection can be reused in a non-transactional method. On the other hand, JDBC and JEE have the features needed to re-acquire the same connection if you are still in the same transaction.

But why does Hibernate release a connection that it's going to re-acquire later? If Hibernate didn't release it, some JEE servers could think that Hibernate is leaking connections when it opens a connection in a nested EJB call and leaves it open for the caller to reuse. That is explained in this post:

http://www.mail-archive.com/hibernate-dev@lists.jboss.org/msg00612.html

To be safe, all data we need to save (and later present to the user) is explicitly passed as parameters, but for logging purposes we did use some data stored in the Oracle session, knowing that the connection cannot be reused by other clients as long as you use transactional business methods.

Update: It seems to be possible to do what I want using some app server specific settings related to connectors and/or custom data sources (not sure what is the best answer here: https://developer.jboss.org/thread/250132)