Re: Database Locks , Performance Issues and How to Resolve?

On Mon, Dec 30, 2019 at 10:09 AM <soumik(dot)bhattacharjee(at)kpn(dot)com> 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

>