PostgreSQL 排查链接锁问题常用SQL语句

server/2024/9/23 22:26:32/

目录

1. 背景

2. 常用SQL语句

2.1. 查询PostgreSQL设置的最大连接数

2.2. 查询当前使用中的连接数

2.3. 查询当前所有连接数据库用户和用户其连接数

2.4. 查询是否存在锁表记录

2.5. 查询详细的锁信息

2.6. 如何解锁?

2.6.1. 定位并终止阻赛的事务

2.6.1.1 pg_locks和pg_stat_activity的区别?

2.6.2. 查找到pid后,按pid终止进程

2.6.2.1 pg_cancel_backend和pg_terminate_backend的区别?

2.7. 参考


1. 背景

        正常情况下,PostgreSQL只要连上了就能愉快地使用了,但是在一些特别的场景,如压测或者某些不可描述的异常,会出现数据库连接异常的情况,比如链接数占满了,无法释放的情况,这时,我们会猜测是不是数据库死锁了?或者链接数达到最大值了?

2. 常用SQL语句

2.1. 查询PostgreSQL设置的最大连接数

sql">-- 查询最大连接数
SHOW max_connections;

2.2. 查询当前使用中的连接数

sql">-- 查询当前连接数
SELECT COUNT(*) FROM pg_stat_activity;

针对上述情况,我列举了几个常用的sql语句,用于排查“死锁”问题

2.3. 查询当前所有连接数据库用户和用户其连接数

sql">-- 查询当前所有连接数据库用户和用户其连接数(按用户名分组统计)
SELECT usename, COUNT(*) FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;-- 查询特定数据库用户当前连接数(your_db_user_name替换成自己的数据库名称)
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'your_db_user_name';

2.4. 查询是否存在锁表记录

sql">-- 显示出没有被授予的锁,也就是等待的锁
SELECT * FROM pg_locks WHERE granted = false;
-- 等价如下SQL
SELECT * FROM pg_locks WHERE granted = 'f';

注意:在PostgreSQL中,布尔类型的值可以用'true','false','t',或者'f'来表示。

  • 'true'或者't'表示真;
  • 'false'或者'f'表示假。

所以当你在查询结果中看到't',那就和'true'等价,都表示该进程已经被授予了锁。同样的,'f'和'false'等价,都表示该进程仍在等待锁。

2.5. 查询详细的锁信息

sql">-- 查询更详细的锁信息,包括哪些语句在等待锁
SELECT a.datname,l.relation::regclass,l.transactionid,l.mode,l.GRANTED,a.usename,a.query,a.query_start,age(now(), a.query_start) AS "age", a.pid 
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE NOT l.GRANTED;

每一行都表示一个等待的锁。大部分列都是自解释的,但是这里要提一下age列:当一个事务等待一个锁,但是却没有进展时,会显示这个进程已经多长时间没有作出响应了。

2.6. 如何解锁?

2.6.1. 定位并终止阻赛的事务

        在一般情况下,解锁最直接的方式就是终止导致锁表的进程或事务。首先要确定持有锁的事务,可以通过如下语句:

sql">-- 确定持有锁的事务
SELECT * FROM pg_stat_activity WHERE waiting = 't';
2.6.1.1 pg_locks和pg_stat_activity的区别?

        这两个视图查询的内容其实是不同的。具体选择用哪个视图,取决于你需要查询的信息。

sql">SELECT * FROM pg_stat_activity;

pg_stat_activity视图包含了所有连接到数据库服务器的活跃进程的信息。

这个视图通常用于查看正在运行的查询以及其它会话信息。如果需要诊断阻塞指定进程的问题,该视图是一个非常好的起点。在这个查询中,waiting='t'的情况指的是,这个进程正在等待获取一把被其它进程持有的锁。

sql">SELECT * FROM pg_locks WHERE granted = false;

pg_locks视图显示了所有活跃的锁以及等待锁的进程。granted = false表示锁尚未被授予,即有进程正在等待获取此锁,可能是由于有其它进程持有了这把锁而未能立即获取。

总的来说,如果你需要查看生成锁的查询,可能用pg_stat_activity会更有用。如果你想看哪些锁正在被等待,pg_locks会更有用。两者视图的使用应根据实际需求和场景来选择。

2.6.2. 查找到pid后,按pid终止进程

sql">-- 按PID终止进程
SELECT pg_terminate_backend(PID);

PID需要替换成实际的进程ID。

  1. 如果不能直接终止导致锁表的事务,就需要查找并修复导致阻赛的程序逻辑错误。

  2. 如果是长期的阻碍,可能需要考虑数据库的性能优化或者硬件的升级等更深入的措施。

终止事务有可能会丢失数据或者让数据状态变得不一致。在大部分情况下,最好先找出为什么会发生锁表情况,然后修复引发问题的原因,而不是简单的直接终止事务。

2.6.2.1 pg_cancel_backend和pg_terminate_backend的区别?

pg_cancel_backend和pg_terminate_backend函数类似于UNIX中的SIGINT和SIGTERM信号。

  • pg_cancel_backend(PID):这个函数会发送一个请求来取消后端当前的查询。PID是你想要取消查询的进程的PID。这个效果类似于UNIX的SIGINT信号,查询会尽可能的安全取消,比如说在一个安全的点回滚到开始状态。

  • pg_terminate_backend(PID):这个函数会中断一个连接和该连接上的任何活动。PID是你想要终止的后端进程的PID。这个函数发送的是一个terminate(BSIGTERM)信号给指定的后端,使得后端进程立即退出。

总结一下,pg_cancel_backend试图取消后端的执行的查询但是保持连接,而pg_terminate_backend则直接断开指定的连接。

2.7. 参考

ChatGPT


http://www.ppmy.cn/server/15374.html

相关文章

软件测试面试题(二)

Web 测试.web 测试描述用浏览器访问 www.baidu.com 的过程以京东首页为例,设计用例框架。(注意框架设计逻辑,区域划分,专项测试等,不需 要详细用例,需要查看 PC 可直接和辨识管提要求)如何测试购…

vscode 如何断点调试ros1工程

在vscode中断点调试ros1工程主要分为以下几步: 1. 第一步就是修改cmakelist.txt,到调试模式。 将CMAKE_BUILD_TYPE原来对应的代码注释掉,原来的一般都不是调试模式。加上下面一行代码,意思是设置调试模式。 # 断点调试 SET(CMAK…

Java Web 网页设计(1)

不要让追求之舟停泊在幻想的港湾 而应扬起奋斗的风帆 驶向现实生活的大海 网页设计 1.首先 添加框架支持 找到目录右键添加 找到Web Application选中 点击OK 然后 编辑设置 找到Tomcat--local 选中 点击OK 名称可以自己设置 找到对应文件夹路径 把Tomcat添加到项目里面 因为…

计算机网络3——数据链路层3以太网的MAC层

文章目录 一、MAC 层的硬件地址1、介绍2、注意点3、定制标准 二、MAC 帧的格式1、结构2、工作原理3、其他 一、MAC 层的硬件地址 1、介绍 在局域网中,硬件地址又称为物理地址或 MAC地址(因为这种地址用在MAC帧中)。 大家知道,在所有计算机系统的设计中…

ocr、人工智能、文字识别接口

人工智能这个词近几年热度颇高,工业上有人称之为“机器代工”,生活中有人称之为“物联网”,而所体现出来的就是智能化,减少人工参与。翔云公有云平台应运而生,提供的OCR API及实名认证API使产品智能化,自动…

RabbitMQ和Minio实现头像存储

使用 RabbitMQ 处理用户头像上传任务,同时将用户头像存储在 Minio 中是一个常见的应用场景。该示例将展示如何在 Spring Boot 项目中使用 RabbitMQ 和 Minio 实现此功能。示例包括两个部分:一是将头像上传任务推送到 RabbitMQ 队列中;二是从队…

docker 安装nacos最新版本单机版

文章目录 1.1 下载nacos镜像1.2 创建nacos需要映射的目录1.3 启动nacos,获取相关nacos文件1.4 复制相关文件到挂载目录1.5 将nacos相关数据库导入到宿主机的mysql中1.6 修改nacos配置文件1.7 启动nacos 二、nacos认证与鉴权2.1 开启鉴权2.2 自定义密钥2.3 生成base6…

Andorid进程间通信之 UNIX SOCKET

1,什么是UNIX SOCKET UNIX SOCKET,域套接字,UNIX SOCKET可用于同一台设备进程间通信,它不需要经过网络协议栈,不需要打包拆包、计算校验和、维护序列号应答等,只需要将数据从一个进程复制到另一个进程&…