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

The attachment here contains all details of locks generated for the session.

Thanks..

From: Bhattacharjee, Soumik
Sent: maandag 30 december 2019 16:40
To: pgsql-admin(at)lists(dot)postgresql(dot)org; pgsql-admin(at)postgresql(dot)org
Subject: FW: Database Locks , Performance Issues and How to Resolve?

Sorry resending the mail, as my mail server bounced.

Please ignore if already received.

Thanks..

From: Bhattacharjee, Soumik
Sent: maandag 30 december 2019 16:09
To: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>; pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org>
Subject: Database Locks , Performance Issues and How to Resolve?

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) and it goes into lock mode even after we have COMMIT in the updates.

In PGADMIN dashboard, database applied exclusive lock on table cfs.next_nm_tabl.

This issue is major for us, and huge performance issue for applications.

Attached the spreadsheet with all details of LOCKS.

Please suggest how to handle this in PostgreSQL , as this never is an issue when we simulate the same in Oracle?

Query from application:
2019-12-28 20:00:17.185 DEBUG 25032 --- [pool-2-thread-1] org.hibernate.SQL :
update
cfs.next_nm_tabl
set
next_nm=?
where
nm_typ_ind=?

DB Locks details from PGADMIN:

[cid:image001(dot)jpg(at)01D5BF31(dot)DB7D91C0]