We have an existing application containing Servlets, JSPs and Stateless Session Beans with container managed persistance. This application is running on WebSphere.
We have a problem regarding number of locks on the Sybase DB to which this app connects from the session beans using the default isolation level of WebSphere i.e. 'Repetable Read'.
The max number of locks set on Sybase is 75000 and we can check that the threads of execution coming from our appliction to DB increase the number of locks to a great extent. once it reaches the threshold, it rollbacks releasing the locks.
We checked the existing code of the session beans which fetches data from the DB. But it always gets maximum of 5 rows at a time.
In the code which executes select query, we can see opening of a. Connection object b. Statement Object and c. ResultSet Object and after executing of select query, they have closed only Statement object and ResultSet object but NOT closing Connection object.
Please let me know whether this the reason for increasing the number of locks on Sybase or any other solution to solve the problem.
Closing the ResultSet and the Statement objects will NOT close the Connection object. The Connection object will remain open, until you close it explicitly. You will have to add code to close the Connection object once you are done with querying the database
Even if your connections are pooled, you will have to invoke the connection.close(). This will return back the connection to the pool. If you do not close the connection, the connection will not be returned to the pool and whenever some process requests for a connection, a new connection will be returned and soon the pool will run out of connections, unless you close the connections. Its the responsibility of the application to close the connections once its finished with using the same.