Tuesday, February 14, 2023

Find Deadlock query and Kill the Process in Postgres

If your query took longer and never ending then possibility tables are under deadlock. You can easily identify the deadlock in postgres and kill the lock.

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where state= 'active'

    
Find the records which are under deadlock

select
    pid, state, usename, query, query_start
from
    pg_stat_activity
where
    pid in
    (
        select
            pid
        from
            pg_locks pgl
            join pg_class pgc on pgl.relation = pgc.oid
        where
            pgc.relkind = 'r'
            and pgc.relname = 'your_table'
    );



Observe the query output and identify your queries causing deadlock.

Then Kill the ProcessID's one by one

SELECT pg_cancel_backend(0001);


if Deadlock still there, your PID still visible in pg_locks table then use below query

select pg_terminate_backend(0001)

   

No comments:

Post a Comment

web stats