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

[<soumik.bhattacharjee@xxxxxxx>]

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@xxxxxxxxxxxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx
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:

 

 

Attachment: data-1577680020226_dev.xlsx
Description: data-1577680020226_dev.xlsx