Authors: Lari Hotari, Ricardo Olivieri

Thanks for the contribution! This looks like valuable stuff indeed...

FWIW, we added a class called "IsolationLevelDataSourceRouter" (in the "jdbc.datasource.lookup" package) for a similar purpose in Spring 2.0.1, also with WebSphere as intended target platform (but not being WebSphere-specific). This was actually the reason for introducing the "getCurrentTransactionIsolationLevel()" method on TransactionSynchronizationManager... which you were able to reuse right away

IsolationLevelDataSourceRouter simply has a list of isolation levels and DataSource instances (the latter typically obtained from JNDI), routing any incoming calls on its DataSource reference appropriately. This should work fine for the typical setup solution (from what I've seen) where the same physical database is represented by various DataSource references, each configured with a specific isolation level.

The code that you submitted follows a different approach: It seems to use WebSphere-specific API for obtaining Connections with specific isolation levels from the same DataSource, with only one DataSource per physical database. This is certainly a nice alternative, since you only need to set up one JNDI DataSource in such a scenario...

Looking through the IBM JDBCConnectionSpec API docs, I also noticed that they support a "readOnly" flag there, through the superclass WSConnectionSpec. We could also support that one, taking the read-only flag from TransactionSynchronizationManager's "isCurrentTransactionReadOnly()" method. Furthermore, we could also support overriding username and password for simple getConnection() calls, similar to the existing UserCredentialsDataSourceAdapter class.

Regarding the retry in case of a stale connection: That seems to be a completely orthogonal issue, so should probably be factored out into its own DataSource proxy (retrying getConnection calls on the target DataSource there, no matter whether that target is a raw WSDataSource, a WebSphere-specific DataSource adapter, or any other DataSource). According to the IBM docs, this is unfortunately not really trivial on WebSphere, since any ongoing global transaction is supposed to be rolled back before reobtaining a Connection...

Actually, detecting and replacing stale Connections is really a core responsibility of the connection pool itself, so I'm somewhat puzzled to see that a WebSphere DataSource imposes that burden onto the user, even requiring to roll back an ongoing transaction! All other connection pools that I'm aware of (standalone as well as in J2EE servers) perform this as part of the pool management, never returning stale Connections to user code in the first place... Is there really no way to configure a WebSphere DataSource accordingly?

Juergen

Juergen, thanks for your valuable comments. I have updated the code and uploaded the changes. I added code to use the readOnly flag and a username/password as suggested. I agree this was also needed. I did some more research on the stale connection issue and it seems we cannot do that test as it was in the class. I found the following statement in the WAS documentation which confirms what you have said:

"Before the application can obtain a new connection for a retry of the operation, roll back the transaction in which the original connection was involved and begin a new transaction."

I found the above statement here: http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html

With the code that I first uploaded, the data source will catch a stale connection and then it will retry to get a new connection without notifying the application that it needs to roll back the current transaction and start again. This could cause major problems.

I also found this link that talks about pretesting connections in the WAS pool -> http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html. WAS has functionality (out-of-the-box) for testing the connections in the pool before giving any to the application. If a stale connection is found, I am guessing WAS throws an exception which the application needs to handle.

If we find a way to get a new connection whenever a WAS stale connection is found without requiring the application to roll back the transaction and start again, then we would have something that we can provide as a separate proxy like you suggested. I haven't found documentation about it yet. Hence, I removed the code from the class that tested for stale connections.

Removed stale connection test.
Added code to use the readOnly flag and username/password.

Thanks for pointing out that pre-testing configuration option in WAS... Since similar stuff exists in all other J2EE servers out there, I suppose that it also behaves the same: It's going to pre-test until a valid Connection has been found, always returning a valid Connection handle to the user. That WAS documentation section even hints at that, claiming that users do not have to worry about that anymore then, at the price of repeated pre-testing for every single "getConnection()" call.

Thanks for the refined adapter implementation! This is a very useful addition for WebSphere users, and I'll make sure to include it in Spring 2.0.3.

Juergen

I've introduced an IsolationLevelDataSourceAdapter class, preparing Connections with an isolation level (the current transaction's or a statically specified one) using standard JDBC, and - as a subclass of the latter -, introduced the WebSphereDataSourceAdapter class, obtaining Connections through WebSphere's JDBCConnectionSpec facility. This is pretty analogous to the capabilities of the original WebSphereDataSourceAdapter, the only difference being that a current transaction isolation level always overrides a statically specified level (this cannot be turned off, since it would be pretty counterintuitive to ignore a current transaction's isolation level and simply use some other one). Additionally, since this class is part of Spring 2.0.3, we can rely on the "getCurrentTransactionIsolationLevel()" method being present

This should be available in the next 2.0.3 snapshot (http://www.springframework.org/snapshots). Feel free to give the included WebSphereDataSourceAdapter an early try, and let me know whether it works for you!

Juergen

I just finished testing the WebSphereDataSourceAdapter class contained in the latest 2.0.3 snapshot. It works great! Thanks for including this class and for the enhancements.