Things to remember when using Connection Pools

Everyone knows why we use connection pools but there are certain things to always keep in mind when using connection  pools. sometime back we were using snagDB as our connection pool but decided to move to C3PO. The transfer was very smooth but after sometime running in production we started to get a strange deadlock situation. There were connections in the pool but the whole application was stuck and interestingly all the connections were in the idle state. We kept on digging as to what was gogin on but couldn’t find the answers. The immediate solution that came to mind was to either increase the size of the pool or to manually drop the connections but this was of no use.

We were using the seprate table strategy to generate the IDs for the tables. It is a simple strategy in whcih a seperate table houses the name of the table, the curent generated key for it and other parameters like how to increment/calculate the next key etc. In strategy a seperate connection is taken from the pool to query the table for next key and then the current key is updated in the table for the current table after that the transaction is committed in so that we dont end up giving the same key to two different objects. Our connectino pool strategy was to increase the size of the pool by 10 when there wasn’t any new connectino available and then wait for the connection when the pool reached a max size.

This sscenario kicked in my mind, that this might be the root cause of all the problems. During the isert operations we were utilizing two connections one for the current process doing the insert and the other for generating the key. It exaplained all of the things happening. First the pool increased it size because every insert process was requesting a two connections then we had  a deadlock situtaion, because all the connections were utilized by the insert process and the ID generator kept on waiting for the connection to be available.

This can be fixed pretty easily by making a seperate connection pool for the ID generator so that it never starves of the connection. And the most important thing is to never use two connections for doing one task but in this case we had no choice.

Please share your thoughts and solutions, if anyone has encountered the same situation and steps taken to resolve it. By the way in my current company our CTO must have gone through this, because we dont use any conenction pooling in our product 😮 despite telling him the advantages he doesn’t seem to understand.

Things to remember when using Connection Pools

5 thoughts on “Things to remember when using Connection Pools

  1. Michael Slattery says:

    Interesting. This probably doesn’t occur to many as it’s somewhat rare to see a fully maxed out connection pool where all connections are involved in inserts.

    “… never use two connections for doing one task …”

    I believe you must do ID generation in a separate connection. Doing it in the same connection can cause several issues (as we’ve learned the hard way).

    I like your solution of a 2nd pool for ID generation. Seems sensivle.

    You could always fetch 2 connections per transaction. Or you could have a single dedicated connection just for ID fetching. However, that could cause some contention (due to need for synchronized access)

  2. Faisal Feroz says:

    Our application was a Content Management System, it was used for managing product specifications like server specs, router specs etc. in a standrad format. So you can imagine the load on the database and in these kind of applications inerts usually outweight the updates.

    Yes I agree with you, for doing ID generation a seperate connection is absoloutely necessary. But i have seen code where people are using several connections for inserting child rows into the database.

  3. I remember when we studied connection pools and how the container manages them and even wanted to programmatically control their creation. The best way is to let the container manage them, I guess.

  4. Faisal Feroz says:

    Allowing container to manage the connection pool won’t solve the problem if the problem lies in their usage. Its better to think about all the scenarios before writing code. This is what differentiates a Great Programmer from a Mediocre One.

  5. John says:

    We’ve made ID fetching use the same connection pool as regular transactions, but an ID-fetching transaction will always receive a connection even if the pool is empty. It’ll create a new one, but it won’t increase the number of connections available to non-ID-fetching transactions.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s