1.在使用wal2json分析xlog,创建slot时,结果命令hang住了
2.查看pg_stat_activity,发现正在等待lock,而且是等待事务锁
3.既然是事务锁,就用收藏的查锁神器query一下,结果百试不爽的神器失灵了,返回null
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
ka.query AS
current_or_recent_statement_in_blocking_process,
ka.state AS
state_of_blocking_process,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid =
bl.pid
JOIN pg_catalog.pg_locks kl ON
kl.transactionid = bl.transactionid AND kl.pid
!= bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid =
kl.pid
WHERE NOT bl.GRANTED;
4.直接query一下pg_locks,virtualtransaction5/0想要获取transaction 370542d的sharelock,但是virtualtransaction-1/370542已经持有370542的exclusivelock,阻塞了前者,但是奇怪的是持有者-1/370542并没有pid,我该如何unlock?
5.百思不得其解,如是我想到restart db,这样总该释放锁了吧
重试命令,还是hang
query pg_locks还是依然如故,顽固呀
6.接着赶紧google、baidu, 结果在baidu上找到一篇文章提示可能是prepared transaction lock,具体哪篇文章已经不可考了,既然是prepared transaction lock,那就查一下pg_prepared_lock试图吧
果然有戏,而且持有得事务跟前述的一致,针对prepared transaction lock,可以使用语法[COMMIT|ROLLBACK]PREPARED GID去提交或者回滚,这里我选择回滚
回滚成功,再试试之前的命令
命令执行成功,prepared transaction lock就是这么顽固,重启db都不会释放,而且由于它不记录pid,也无法使用类似select pg_terminate_backend(pid)的方式终止