This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one script to find information about the Locks which are held by Open Transactions of PostgreSQL Database Server.
Database Theory: What is Timeout, Lock, Block, and Deadlock in a Database System?
PostgreSQL view pg_locks contains the main information about the all types of lock of Database Server.
It contains one row per active lockable object.
This script is very helpful to all PostgreSQL Database Administrators.
Below is a script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT pl.pid AS ProcessID ,psa.datname AS DatabaseName ,psa.usename AS UserName ,psa.application_name AS ApplicationName ,ps.relname AS ObjectName ,psa.query_start AS QueryStartTime ,psa.state AS QueryState ,psa.query AS SQLQuery ,pl.locktype ,pl.tuple AS TupleNumber ,pl.mode AS LockMode ,pl.granted -- True if lock is held, false if lock is awaited FROM pg_locks AS pl LEFT JOIN pg_stat_activity AS psa ON pl.pid = psa.pid LEFT JOIN pg_class AS ps ON pl.relation = ps.oid |
Leave a Reply