Menu
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.
741
idle in transaction: This indicates the backend is in a transaction, but it is currently not doing anything and could be waiting for an input from the end user.
Kill an Idle Connection: >> SELECT pg_terminate_backend(7408); The process has been magnificently killed. Now check the remaining idle connections from the below-appended query.
Locks or Exclusive Locks or Write Locks prevent users from modifying a row or an entire table. Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction. This prevents other users from changing the row until the transaction is either committed or rolled back.
The connections in Postgres aren't free each connection, whether idle or active, consumes a certain overhead of memory (10MB per connection). Idle is something that grabs connection from your application and holds it. Application connection poolers often also consume one or more idle connections.
This is a problem of the application server.
Sessions are in state "idle in transaction" when the application does not end the transaction with COMMIT or ROLLBACK. This is to be considered a bug in the application.
The locks remain (and are of course granted, otherwise the session could not be idle) until the transaction ends.
From PostgreSQL 9.6 on, you can set the parameter idle_in_transaction_session_timeout to terminate such transactions automatically with a ROLLBACK, but that is a band-aid to avoid problems on the database rather than a solution.
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
