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
//NO Txn has been committed yet
Connectiond = get D Connection
//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.
Well, just to add to your example, i'm doing an explicit commit from my application.
Connection c = getConnection.
and then d = getConnection;
Since the updated data is reflected in database, i assume commit is working fine. As per database transaction rules, if i commit in database, it should be available to anyone querying after this.
Please let me know if i'm grossly mis-understanding something.
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?
we are calling this from POJO's.
However i did try to put
transaction-isolation=TRANSACTION_READ_COMMITTED and this has solved the problem. But i would surely like to know the best and ofcourse the correct approach. Meanwhile, also trying to brush my JTA knowledge.
Would try using <no-tx-datasource>
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?
We are calling this POJO's (which gets the connection from JBoss Connection Pool using datasource) from Servlet. There is no EJB involved.
Also, tried with <no-tx-transaction> and that seems to work.
So, what is the correct approach ?
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.
Thanks Weston, that gives a brief picture of different ways of handling transactions.
Can you guide me to any resource on web which gives more understanding of 1st option. (I searched google and read some articles but it seems to be ambigous).
Thanks for all the suggestions.