Last active
May 7, 2020 19:29
-
-
Save edgarrmondragon/2fee04b07099ac149b4962ec48575b0a to your computer and use it in GitHub Desktop.
[Fix PostgreSQL Table Locks] #sql #postgresql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- The following query may be helpful to see what processes or applications are | |
-- blocking SQL statements (these only find row-level locks, not object-level locks). | |
SELECT blocked_locks.pid AS blocked_pid, | |
blocked_activity.usename AS blocked_user, | |
blocking_locks.pid AS blocking_pid, | |
blocking_activity.usename AS blocking_user, | |
blocked_activity.query AS blocked_statement, | |
blocking_activity.query AS current_statement_in_blocking_process, | |
blocked_activity.application_name AS blocked_application, | |
blocking_activity.application_name AS blocking_application | |
FROM pg_catalog.pg_locks blocked_locks | |
JOIN pg_catalog.pg_stat_activity blocked_activity | |
ON blocked_activity.pid = blocked_locks.pid | |
JOIN pg_catalog.pg_locks blocking_locks | |
ON blocking_locks.locktype = blocked_locks.locktype | |
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE | |
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation | |
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page | |
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple | |
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid | |
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid | |
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid | |
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid | |
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid | |
AND blocking_locks.pid != blocked_locks.pid | |
JOIN pg_catalog.pg_stat_activity blocking_activity | |
ON blocking_activity.pid = blocking_locks.pid | |
WHERE NOT blocked_locks.GRANTED; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Looking at pg_locks shows you what locks are granted and what | |
-- processes are waiting for locks to be acquired. A good query | |
-- to start looking for lock problems: | |
SELECT relation::regclass, * | |
FROM pg_locks | |
WHERE NOT GRANTED; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- What are the running processes | |
SELECT * FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start; | |
-- Cancel process | |
SELECT pg_cancel_backend(<pid of the process>); | |
-- Kill process | |
SELECT pg_terminate_backend(<pid of the process>); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment