On Mon, Dec 30, 2019 at 10:09 AM <soumik.bhattacharjee@xxxxxxx> wrote:
Hi Experts,
In our TEST database – PostgreSQL 10 - We are facing issue while executing SELECT queries from application concurrently.
Select statements taking long some time (almost ½ hr)
It is not inherently wrong for a select query to take 30 minutes. It depends on what the query is. How long do you think it should take instead? does it take less time under other circumstances? What is the query? Can you do an EXPLAIN (ANALYZE, BUFFERS) for it?
and it goes into lock mode even after we have COMMIT in the updates.
According to your spreadsheet, none of your select queries are being blocked by locks. A few update statements are. But I am quite certain those have not been committed.
In PGADMIN dashboard, database applied exclusive lock on table cfs.next_nm_tabl.
The exclusive lock is not on the table, it is on a row within the table.
This issue is major for us, and huge performance issue for applications.
Attached the spreadsheet with all details of LOCKS.
This spreadsheet has omitted some important columns, like locktype, which makes it rather hard to figure out what is going on. But it looks like pid 27196 has opened a transaction and then forgotten to close it. Is it a SELECT...FOR UPDATE? The text of the query is truncated, you could increase track_activity_query_size so it is large enough to hold the whole query.
Please suggest how to handle this in PostgreSQL , as this never is an issue when we simulate the same in Oracle?
Your first recourse should pg_stat_activity, not pg_locks. What is 27196 doing? Is it 'idle in transaction'?
Cheers,
Jeff
