Postgres: How to identify and release locks lock

 1. View Locks on a Specific Table 

Use this query to see all active locks for a specific table by name:

sql

SELECT 

    locktype, 

    relation::regclass AS table_name, 

    mode, 

    granted, 

    pid 

FROM pg_locks 

WHERE relation = 'your_table_name'::regclass;

granted: t means the process has the lock; f means it is waiting for it.

mode: The type of lock (e.g., AccessShareLock for selects, AccessExclusiveLock for schema changes). 

 

2. Identify "Who is Blocking Whom"

To find which session is holding a lock and preventing others from proceeding, use the pg_blocking_pids function: 

sql

SELECT 

    pid, 

    usename, 

    pg_blocking_pids(pid) AS blocked_by, 

    query AS blocked_query

FROM pg_stat_activity 

WHERE cardinality(pg_blocking_pids(pid)) > 0;

The blocked_by column will show the PID of the process holding the lock. 

  

3. Comprehensive Lock & Activity View

This joined query shows the table name, the lock mode, and the exact SQL query being executed by that process: 

sql

SELECT 

    a.datname, 

    l.relation::regclass AS table_name, 

    l.mode, 

    l.granted, 

    a.usename, 

    a.query, 

    a.pid 

FROM pg_stat_activity a 

JOIN pg_locks l ON l.pid = a.pid 

WHERE l.relation IS NOT NULL 

AND a.datname = current_database()

ORDER BY a.pid;

 

How to Release a Lock

If a process is stuck and blocking your table, you can terminate it using its PID found in the queries above: 

Cancel a query (soft): SELECT pg_cancel_backend(pid);

Terminate the connection (hard): SELECT pg_terminate_backend(pid);