I have a JDBC connection pool in a web API that makes frequent requests to the RDS Instance. Each request is for a long running SQL query where PG decides not to use indices because the amount of data to be returned is quite large.
I'm noticing that some of these long running queries are just stuck in state idle in transaction. When look up the pid for these processes in pg_locks table they have 152 rows . With mode AccessShareLock.
The intent for this table is to be never updated — It serves as a point in time table. So I don't see a need for idle transaction or accesssharelock. Is there I can turn these off so that my queries run faster and aren't stuck because of AccessShareLock?
Best Answer
idle in transactionmeans the connection is not doing anything - it's "idle".The query has finished, if the query was still running the connection would be shown as
active.The code that initiated the query, forgot to end the transaction by calling commit or rollback. If a connections stays in "idle in transaction" for a long time, this is typically a bug in the application.
Make sure your application properly ends every transaction - even "read-only" ones that only run SELECT statements. Or switch your connection to auto-commit in your code.