锁信息
查询数据库种的锁信息
SQL> SELECT * FROM pg_locks;
查询等待锁的线程状态信息
SQL> SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
锁故障排查
在数据库出现锁竞争和阻塞时,需要排查和处理锁定,必要时通过kill阻塞进程消除锁定。
--查询阻塞会话
SQL>
SELECT w.query as waiting_query,w.pid as w_pid,w.usename as w_user,l.query as locking_query,l.pid as l_pid,l.usename as l_user,t.schemaname ||'.'||t.relname as tablename
from pg_stat_activity w join pg_locks l1 on w.pid=l1.pid
and not l1.granted join pg_locks l2 on l1.relation = l2.relation
and l2.granted join pg_stat_activity l on l2.pid = l.pid
join pg_stat_user_tables t on l1.relation =t.relid
where w.waiting;
杀阻塞会话
--根据会话ID结束会话
SQL>SELECT PG_TERMINATE_BACKEND(13896)
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。