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.