There is no such thing as 'caching results' at the JBoss connection pool level.

The problem is in your undertanding of transactions. Use of a local-transaction-datasource implies that you want JBoss to manage transactions for you. When you close a connection, this does not mean that JBoss commits the transaction.

For example consider the following pseudo code:

Connection c = get C Connection
c.doJDBC
c.close()

//NO Txn has been committed yet
Connectiond = get D Connection
d.doJDBC
d.close()

//Still no commit

Connection d still will see the old records of c because the container has not committed your transaction. Depending upon where this is executed (EJB, Servlet) the transaction will be committed at different times and each has different semantics. I suggest some basic reading on JTA, LocalTransactions and JDBC.

If you are explicitly comitting, they you don't need to use <local-txn-datasource>, you would use <no-txn-datasource>.


A quick question before we go any further, in what context are you using the datasource? EJB? Servlet?

But POJO's called from where?

Inside an EJB? Inside a Servlet? I would imagine that this is a Servlet or you are in an EJB where you have specified no transaction. Otherwise JBoss would not let you do an explict commit using this type of datasource.

Could you please be more specific about the exact environment in which you are running?

Ok thanks, that makes a bit more sense.

You basically have 3 options:

a)Use the javax.transaction.UserTransaction interface to demarcate transaction boundaries in your Servlet. Doing this will enable you to remove the explicit commits from your code, as well as use the default transaction isolation level of the driver.

b)Manage the transactions explicitly yourself. This is effectively what you are doing now, just with the wrong datasource, <local-tx-datasource>. This is why it worked when you switched to the <no-tx-datasource>.

c)Wrap your POJO's in an EJB and allow the container to manage the transactions for you.

Generally I prefer the first approach because it keeps your POJO's free of transaction code that can be cumbersome to read, and error prone. Further, try to leverage as much of the application server facilities as you can, that's why it is there.