How to change from Shareable to Unshareable Connections

Body

The shared connections were introduced in WebSphere Application Server Version 5.0 and at the time unshared was the default. In general, the behavior of shared connection is sharing the connection with different threads. It is dependent on the application design. The shareable and unshareable is defined inside the resource reference. There are two ways you can define shareable connections, you can use direct naming lookup during deployment of the application, which will default to shareable, or you can create a resource reference and define a shareable connection. For unshareable, you have to create a resource reference and define unshareable. When the application closes a shareable connection, the connection is not closed, and it’s not returned to the free pool immediately. The connection remains open and ready for the next request within the same LTC for a connection to the same resource. When there is no new request then the connection returns to the free pool. When you are using unshareable connections, that connection will go through a get-use-close scenario, and once close() is called the connection will move to the free pool and will wait for the next request. Developers will have to ensure that the application uses get-use-close scenario. Typically, applications will be doing the following set of steps which together amounts to the use time per connection. The use time means "The average in milliseconds of the times that the connection is used." This average consists of only non-zero millisecond use times.

   begin transaction // either application or the container begins

     1) getConnection() // delay can happen if db is not performing well

     2) execteQuery() // delay can happen if db is not performing well

     3) application performs its own task that might be costly

     4) close connection

     5) application performs its own task that might be costly end transaction // either application or the container ends

     6) here connection is released to the free pool

You can look in resource reference and see your connections setting. You can check the resource reference section in the deployment descriptor of the application modules either web.xml or the ejb-jar.xml. A resource-reference is a logical name used to access a resource such as a data source. Once the resource-reference is bound to the data source, it can be accessed using the resource-reference instead of directly using the data source name. The indirect-lookup will pick the resource reference from the application (res-auth setting to 'Container') instead of using the default values which is 'Application'. The Container alias will be used only on an indirect-lookup which is configured during the application deployment, if the default value of 'Application' is used then only the component alias is used hence it is necessary that the application does an indirect lookup of the data source.

     For Unshared connection:

      <resource-ref>
        <description>Database</description>
        <res-ref-name>java:comp/env/jdbc/MyDS</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Unshareable</res-sharing-scope>
      </resource-ref>

     For Share connection:

      <resource-ref id="ResourceRef_1308651467450">
        <description>
        </description>
        <res-ref-name>jdbc/ds1</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
      </resource-ref>

     Note: If your application uses direct naming lookup, then the default res-sharing-scope will be Shareable.

In early versions of WebSphere (7.0.0.3 or older), to change from shareable to unshareable, you have to use development tool and create resource reference, set indirect naming lookup, and select unshared connection. Then repackage the ear. Finally redeploy the ear file.

Let's say you are using RAD tool:

Here you can define shareable or unshareable in resource reference.

From the web.xml I can see unshareable is set:

After WebSphere 7.0.0.3 and higher, when using direct lookup without using a resource reference, the default behavior of shareable will apply. In such direct lookup usages, if you want to use unshared connections, then set the property defaultConnectionTypeOverride to unshare to override the default connection sharing behavior. The globalConnectionTypeOverride property is provided as a convenience to ensure that, if this property is set then it takes precedence even if you have set the connection sharing in a resource reference.

You can set the properties in the console

From the Administrative Console , navigate to the following path: JDBC

providers > DB2 Universal JDBC Driver Provider > Data sources > DB2

Universal JDBC Driver DataSource > Connection pools > Custom Properties

Here is some documentation around connection sharing:

• Default behavior of managed connections in WebSphere Application Server
   
• Unshareable and shareable connections
   
• PK75717: Add custom properties to control connection sharing - more details about these two properties
   
• Creating or changing a resource reference

Question:

Which one should you select, share or unshared, to get the optimum performance?

Choosing between Shareable vs Unshareable connections is not something that can be derived without knowing the application's connection usage pattern.

For example, if you have a transaction involving a lot of nested ejb/servlet calls and each of them would be requesting a connection, then shared connections might be a better option than each of them getting a separate connection. Likewise, if your application's transaction pattern is more of just open-use-close then unshared might be a good option. Hence it is very difficult for us to tell which of these two will give you good performance without knowing your application. The right approach would be to get more clarity about shared and unshared connections with respect to your application and choose them wisely. Alternatively, you can reach out to the IBM Software Service team through your IBM representation and they will assist you further on these areas.

Here is a dW Answers post regarding this topic: How do I change from shared to unshared connection?

Additionally, here is a YouTube video showing the steps to switch from shared to unshared: How do I change a connection from shareable to unshareable in WebSphere Application Server?