I have a web application which uses VUE, FastAPI and PostgreSQL. We constantly use this web app and recently it started to be unresponsive to logins and everything.
I checked the logs of FastAPI because VUE seemed like it was working properly, there seemed no problem but logs were stopped even tho I tried to follow up on the logs. Then when I restarted the container it looked like the container wasn't starting as usual. So I checked PostgreSQL and it also looked fine from the outside. I was able to connect psql and databases inside PostgreSQL.
Then I checked ps aux to see if there were any processes locking the production database because development application was working normally. In the output of ps aux I noticed this log:
postgres 539295 0.0 11.7 221856 116804 ? Ss 07:57 0:05 postgres: 14/main: postgres prod_app 172.18.0.4(47952) UPDATE waiting
I tried removing all processes which had "UPDATE waiting" in the status section, and I was expecting my app to keep working normally after deleting these process and never have the same problem ever again.
At first everything seemed fine my app started working normally but then these processes came back and crushed the app again.
This locking processes seem to not go away only by deleting the processes I think I have to find another long term solution to this problem. Is there something I'm missing or can I do anything to prevent these processes from occurring?
In the same server there were other services which were updating same database server's different tables. One of them had an update statement which wasn't closing. When I closed its transaction after updating and added a rollback statement if it was failed during transaction then it was fixed.