GreenPlum 锁表以及解除锁定
2024-08-27 10:15:41
最近遇到truncate表,无法清理的情况,在master节点查看加锁情况,并未加锁
这种情况极有可能是segment节点相关表加了锁,所以遇到这种情况除了排查master节点的锁,所有的segment也要查看锁的持有情况,然后根据情况,进行处理。
这种情况极有可能是segment节点相关表加了锁,所以遇到这种情况除了排查master节点的锁,所有的segment也要查看锁的持有情况,然后根据情况,进行处理。
master节点:
查看segment锁情况
select gp_execution_dbid(), pid, relation::regclass, locktype, mode, granted
from gp_dist_random('pg_locks');
查看segment锁情况
select gp_execution_dbid(), pid, relation::regclass, locktype, mode, granted
from gp_dist_random('pg_locks');
查看具体什么语句持有的锁
select gp_execution_dbid() dbid,procpid,current_query
from gp_dist_random('pg_stat_activity')
where procpid in
(select pid from gp_dist_random('pg_locks') where locktype='relation' and mode='ExclusiveLock');
select gp_execution_dbid() dbid,procpid,current_query
from gp_dist_random('pg_stat_activity')
where procpid in
(select pid from gp_dist_random('pg_locks') where locktype='relation' and mode='ExclusiveLock');
通过以上语句大概定位到持有锁的segment
segment节点:
根据实际情况进行处理
1.连接相关segment,xxxx替换为实际segment节点的ip,端口,库名
PGOPTIONS="-c gp_session_role=utility" psql -h xxxxxxxxx -p xxxx -d xxxxx
2.在segment查询相关锁情况
SELECT
w.current_query as waiting_query,
w.procpid as w_pid,
w.usename as w_user,
l.current_query as locking_query,
l.procpid 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.procpid = 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.procpid
join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;
3.处理持有锁的pid
select pg_terminate_backend('procpid');
---------------------
根据实际情况进行处理
1.连接相关segment,xxxx替换为实际segment节点的ip,端口,库名
PGOPTIONS="-c gp_session_role=utility" psql -h xxxxxxxxx -p xxxx -d xxxxx
2.在segment查询相关锁情况
SELECT
w.current_query as waiting_query,
w.procpid as w_pid,
w.usename as w_user,
l.current_query as locking_query,
l.procpid 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.procpid = 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.procpid
join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;
3.处理持有锁的pid
select pg_terminate_backend('procpid');
---------------------
--GP查看锁
SELECT pid,rolname, rsqname, granted,
current_query, datname
FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity
WHERE pg_roles.rolresqueue=pg_locks.objid
AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid
AND pg_stat_activity.procpid=pg_locks.pid;
--GP解除锁定
pg_cancel_backend(#pid)
原文:
https://blog.csdn.net/dazuiba008/article/details/77878465
https://blog.csdn.net/housen1987/article/details/84295256
最新文章
- 转载文章----C#基础概念
- OD调试2
- iOS多线程编程指南(二)线程管理
- OpenGL2.0及以上版本中glm,glut,glew,glfw,mesa等部件的关系
- js的变量作用域 ,变量提升
- Entity Framework学习笔记(三)----CRUD(2)
- jQuery 的插件 dataTables
- Qt之XML(一) DOM
- 安装 Qt 及所需 gcc 等
- 解决:win7右键打开方式添加应用程序无法设置和删除多余的打开方式
- 帆软报表(finereport) 折叠树
- selenium 淘宝登入反爬虫解决方案(亲测有效)
- nodeJs--模块module.exports与实例化方法
- html5游戏开发-简单tiger机
- UVA-816.Abbott's Tevenge (BFS + 打印路径)
- Nginx单向认证的安装配置
- 在 JNI 编程中避免内存泄漏与崩溃
- Linux自动化部署尝试
- oracle 关于表数据delete 后如何恢复
- lr11_Run-time Settings选项介绍:
热门文章
- PB计算两个日期相差月份(计算工龄)
- POJ 3233-Matrix Power Series( S = A + A^2 + A^3 + … + A^k 矩阵快速幂取模)
- easyExcel用于导入导出
- Go context 介绍和使用
- [转载]Linux下非root用户如何安装软件
- Django一对一查询,列类型及参数
- iOS音频学习笔记二:iOS SDK中与音频有关的相关框架
- 如何在SAP云平台ABAP编程环境里把CDS view暴露成OData服务
- CIP 协议安全扫盲
- nginx连接php测试