In this post, I am sharing one script to find information about the Locks which are held by Open Transactions of PostgreSQL Database Server.
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:
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.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
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.