0

We are having a recurring situation where we have 2 one query hanging, and other queries waiting for that one, and our DB grinds to a standstill. I am trying to debug this and am seeking help. The DB is RDS running Postgres 10.6.

I use DBeaver to monitor and admin this DB, and here are the screenshots from the Administer -> Lock Manager in that application. In this case, to resolve this issue, I needed to kill the one session the others are waiting on, but I would like to learn how to debug this deeper to figure out what it is waiting on, and why.

The query waiting There are multiple copies of the above query because it is being generated by a very frequent cron job, so that is atleast expected.

The query others are waiting on According to RDS -> Performance insights, this query, and a lot others are spending a lot of time at this point in the lock state relation.

I expect I havent given enough information here. Let me know what I can add.

1

To debug this problem, first find out what the session that blocks the others is doing:

SELECT state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = 29303;  -- or whoever blocks the others

If the session is in state active, it is a long running query. Use EXPLAIN (ANALYZE, BUFFERS) with the query to find out where it spends its time and how to improve that.

If the state is idle in transactions, you have an application bug that forgot to close a transaction. Since all locks are held until the end of the transaction, this blocks other transactions even though it is doing nothing.

The short-term solution is to use the pg_cancel_backend() function to kill the blocking transactions.

The long-term solution is to fix bugs that keep transactions open and to improve concurrency by using fewer and less heavy locks:

The EXCLUSIVE lock that the transactions in your images place on a table are heavy-handed. In my experience, explicit table locks are hardly ever necessary or indicated. Usually they are a sign that the programmer doesn't understand database transactions well.

  • Yup, that exclusive lock based query is one that I do not like the design of, but I dont believe that it is a cause in this case, just the victim due to being the gorrila of queries. – Karthik T Sep 11 '19 at 10:18
  • I will keep this in mind for the next lock that happens, but given the info in the image, would you say that 28298 is the one others are waiting on? If so, we dont explicitly use transactions for that one, its just a single query – Karthik T Sep 11 '19 at 10:20
  • No, 28303 is the bad guy. 28298 is just a long running query that modifies data, but 28303 tries to take a lock that conflicts with that, and all others have to queue behind 28303. Without knowing pg_locks and the statements executed, it is impossible to say more. – Laurenz Albe Sep 11 '19 at 10:38
  • 28298 is just a 1 row update, should not be a long running query. Anyway next time this happens ill try to take a dump of the pg_ tables to debug better. – Karthik T Sep 12 '19 at 9:14
  • Also in this particular instance, killing 28298 was enough to release the DB. – Karthik T Sep 12 '19 at 9:18

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.