postgresql "idle in transaction" with all locks granted

user_1498257 0
Viewed 10553

A very simple delete (by key) on a small table (700 rows) every now and then stays "idle in transaction" for minutes (takes milliseconds usually) even though all the locks are marked as "granted".

What can I do to pinpoint what causes it? I'm using this select:

  SELECT a.datname,
     c.relname,
     l.transactionid,
     l.mode,
     l.GRANTED,
     a.usename,
     a.waiting,
     a.query, 
     a.query_start,
     age(now(), a.query_start) AS "age", 
     a.pid 
FROM  pg_stat_activity a
 JOIN pg_locks         l ON l.pid = a.pid
 JOIN pg_class         c ON c.oid = l.relation
ORDER BY a.query_start;

which shows a lot of "RowExclusiveLock"s but all are granted... so I don't see what is causing this spikes of delays.

locking postgresql sql
2 Answers

It could also be due to a combination of:

  1. exhaustion of a connection pool
  2. Transactions within transactions
  3. Implicit postgres transactions within transactions

This article opened my eyes on this issue https://www.birdie.care/blog/birdie-engineering-update

This explains why there are no deadlocks in the database itself. It's only because app's connection pool ceiling is reached.

user_811050
Related