Running workflow resulted in DB deadlock
Customer writes workflow to clean up TCA Agents in end point servers in the TPM database. The workflow starts many DataBase access threads, and runs into time-out condition due to deadlock and SQLCODE -911 and rc-68 (lock timeout). The AM log shows the following messages:
10/31/10 3:30:06:245 EDT
com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException Max connections reached 869
10/31/10 3:30:06:256 EDT
com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection 299 .
It is known that if customer develops their own workflow to clean up TCA agents with multiple element IDs, and the workflow drives RetrieveAgents.sh in its process, the problem above can happens due to many threads can be spawned to process the clean up.
It is recommended that customer reviews the following actions:
1) Increase the DB2 lock timeout value (default 120 seconds)
2) Raise the workflow timeout value (default 300 sec)
3) Reduce the number of thread spawned; even by removing the use of RetrieveAgents.sh.
4) Use the following steps could be used in future to increase the connection resource size.
a) open the was admin console using https://AM_IP:9043/ibm/console
b) Navigate to JDBC->Data Sources-> Agent Registry-> connection pool
c) Adjust Connection timeout and/or maximum connections to higher values.
e) Apply and Save the changes to the master repository.
f) After performing the above steps you need to re start the AM.