postgresql 锁问题排查

查询pg_locks,获relation id

SELECT * FROM pg_locks WHERE NOT GRANTED;

查询到具体锁定的表名

SELECT pid,locktype, relation::regclass, mode, pid,granted FROM pg_locks;

通过relation获取那些sql占用锁

SELECT * FROM pg_locks WHERE relation = <relationID>;

通过pid查询sql具体信息

SELECT xact_start, query_start, backend_start, state_change, state FROM pg_stat_activity WHERE pid IN (<pid from previous query>);

kill pid

SELECT pg_cancel_backend(<pid from previous query>);
SELECT pg_terminate_backend(<pid from previous query>);