1

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?

Improve this question
5
  • 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, 2013 at 15:14
  • It is the login call, checks the user credentials (name and password) stored in database user tables.
    – Jordi
    Sep 9, 2013 at 15:36
  • I guess it's shoule be the application issue, which doesn't commit in time
    – francs
    Sep 10, 2013 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, 2013 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, 2015 at 13:44

1 Answer 1

Reset to default
1

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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