Since the data source is managed by the application server (and WebSphere is a full-featured implementation of Java EE), this is actually the JCA specification that applies here. Section 7.9 of JCA 1.5 imposes the following restrictions on the ability of an application to change the transaction isolation level:
If a connection is marked as shared, it should be transparent to the application, regardless of whether one joint connection is used or not. An application should not make assumptions about using one common connection and, therefore, should use the connection together.
However, a J2EE application component that intends to use the connection in a fuzzy way should leave a deployment hint, which will prevent this container from being shared. Examples of fuzzy use of a connection include changing security attributes, isolation levels, character settings, and localization configurations.
To summarize: in general, your application should not try to change the isolation level if the resource link configures the connection as shared. If you look at the requirements for setting data access isolation levels in the WAS Information Center, you will also find the following statement:
Attempting to directly set the isolation level using the setTransactionIsolation () method for a shared connection running in a global transaction is not allowed. To use a different isolation level for connections, you must specify a different resource reference.
Section 7.9.1 of the JCA 1.5 specification, on the other hand, describes scenarios in which the application server may still allow the application to change the isolation level, even if the connection is available. This mainly refers to scenarios in which the connection is configured as shared, but where it is not used effectively (since there is no need to share the connection between several components).
The topic "Extensions for the Data Access API" in the InfoCenter suggests that WebSphere supports this:
applications [...] cannot change sharing properties after a connection request, if other descriptors exist for this connection. (If no other handles are associated with the connection, then the properties of the connection can be changed.)
Therefore, you should be able to use setTransactionIsolation () to change the isolation level in certain scenarios, but it depends on how your application uses the connection.
Finally, you did not describe in detail how you controlled this at the database level, but you need to consider that at some point the application server must reset the isolation level of the physical connection. Therefore, if setTransactionIsolation () succeeds, this change can only take effect for a short time on the physical connection.
Please note that there are several ways to avoid all these complications (which may or may not be applicable in your case):
- Instead of using setTransactionIsolation (), adjust the appropriate isolation level in the resource link and, if necessary, use several resource references.
- Use the WebSphere-specific WSDataSource API to specify the expected level of isolation before acquiring a connection.
- Change your SQL to change the isolation level for each query (for example, use
WITH UR ).