I have a web application interacting with PostgreSQL (v8.4 & Centos Linux) which suddenly started locking some of the database's tables. Still have no idea what happened, since the code is not new, has been running several time and has been tested beforehand.

I am trying to see what transaction might have cause it, or if a combination of it with an autovacuum process could have caused it. But in the meantime I would like to unlock the database tables. I tried restarting the postgreSQL service, terminating thus the processes in "idle in transaction" state which were locking the tables but it didn't work: next time my application performed the same call the tables went locked again.

Any ideas on what can I do to unlock the database gracefully?

  • What does that call in question do? Idle in transaction means that there is an open transaction where nothing is being done. For example, this is what you see when you start a transaction manually and then forget to commit or rollback. – dezso Sep 9 '13 at 15:14
  • It is the login call, checks the user credentials (name and password) stored in database user tables. – Jordi Sep 9 '13 at 15:36
  • I guess it's shoule be the application issue, which doesn't commit in time – francs Sep 10 '13 at 6:05
  • How I can check which transactions started and are pending? I listed the process(es) and also the locked tables but logs aren't providing much information regarding such issue. – Jordi Sep 10 '13 at 9:46
  • What framework and DBAPI is the web application using? Do you have the rights to make changes to the source code? – eradman Oct 13 '15 at 13:44

What you are seeing are transactions which are running, holding locks, and not doing anything. What you need to do is trace the transactions back to their source and find out what they are doing. They may be other admins who have logged in and forgot to log out.

In general it is usually a bad thing to hold transactions open and idle for a long time due to this sort of locking problem.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.