Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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