CODEWITHSUNDEEP
javaconnectionconnection-pooling
Balachandar Thangavel
Balachandar Thangavel

Reputation: 67

Releasing connections from connection pool

In our code (which runs as a schedule job via timer), we have threads running in parallel to perform a database operation. Problem here is each thread is initiating a connection via Hibernate factory. These connections are closed after every database action but stil gets stocked in the connection pool(as INACTIVE). All the connections gets released only after the job/main process is killed.Is there any way to release the connection even from connection pool after the database operation. When we use cron job instead of timer, the process gets killed automatically but we dont need cron here. Kindly help us to resolve this as we are already nearing production release. Note : We came to know about this when QA tested with heavy load on the job and for each load new connections are pulled.

Upvotes: 1

Views: 5720

Answers (2)

Balachandar Thangavel
Balachandar Thangavel

Reputation: 67

I have found the root cause for the issue and have also found the solution. The root cause was number of connections set as minimum and maximum and the time out parameter. The minimum was 5 and max was 20 and timeout was 800 seconds. But out job was scheduled to run every minute. Due to the configuration, the connections were not released properly within minute. Another issue was our code was not using the session factory as singleton, but was initializing for each thread. Since the resource was not shared, each session factory creates 5 connections by default and extended to 20 max. Since the timeout also was higher before the connections are released, next set of job starts and creates its own set of new connections. Finally the pool gets full and oracle becomes unavailable.

We fixed this by sharing the session object across and also setting the timeout to lesser value so that connections are getting released from pool.

Upvotes: 0

bhuvan salanke
bhuvan salanke

Reputation: 163

You need to restrict the number of threads getting created in the thread pool.

dotConnect for Oracle uses connection pooling. The OracleConnection connection string has the Pooling parameter. If Pooling=true (the default value), the connection is not deleted after closing it, it is placed to the pool instead. When a new connection with the same connection string is opened, it is taken from the pool (if there are free connections) instead of the creating a new one. This provides significant performance improvements. If you use 800 connections that are connected for 10-15 seconds each, and there are only few different connection strings, you may not have 800 actual connections. Closed connections will be placed to the pool, and they will be taken from the pool when a new connection with the same connection string will open. No additional connection will open in such case.

You can disable Pooling by adding 'Pooling=false' to the connection string. In such case, a connection will be deleted from memory and free the session. However this may lead to performance loss.

Most likely, pooling should not cause creating too much sessions. Try testing your application with pooling on. If the session number will be too large, you can disable pooling.

For more information, please refer to http://www.devart.com/dotconnect/oracle/docs/FAQ.html#q54

Upvotes: 3

Related Questions