Background: We are running a web application on apache with database server as PostgreSQL. The application is a expense claim application with workflow. The software versions are specified below:
Red Hat Linux release 7.3
Apache 1.3.20
PostgreSQL 7.1.3
Problem: When the application is accessed by many users like say 40, the PostgreSQL database freezes.
Description of Process happening behind: The application mainly puts load on one table (worklist) where the steps were created for each expense claim initiated. While creating each step for claim there is exclusive row lock and at the end inserts a new step. Both these statements are in one transaction. When the apache hangs in between the deadlock remains. In this way there are many deadlocks created which makes the database to finally freeze. To resolve this we were restarting the PostgreSQl db. Sometime the apache also hangs. Then we were restarting the apache. There is no log created in postgreSQL. Whereas our application records an error log: ‘Failed to gain exclusive table row lock’
We were guessing that the database hanging is due to deadlock issue. But not sure of it.
I have attached the postgreSQL.conf file for your reference to check the settings.
Please let me know what might be the reason and how to check and resolve it.
Thanks and Best Regards,
Anu