pg数据库的锁排查方法

关键字

pg lock

问题描述

pg 数据库上锁问题如何排查

解决问题思路

准备数据
create table lock_test(name varchar(10),age varchar(10));

insert into lock_test values('ff','10');
insert into lock_test values('yy','20');
insert into lock_test values('ll','30');


session1：
begin;
update lock_test set age = 'session1' where name = 'ff';

session2：
begin;
update lock_test set age = 'session2' where name = 'ff';

session2 因为更新同一行数据肯定 pending


-- 查看活跃会话
select pid,datname,usename,application_name,client_addr, query_start,wait_event_type ,wait_event,state,query from pg_stat_activity where state !='idle' and pid <> pg_backend_pid ();


|pid   |datname  |usename |application_name|client_addr|query_start                  |wait_event_type|wait_event   |state              |query                                                   |
|------|---------|--------|----------------|-----------|-----------------------------|---------------|-------------|-------------------|--------------------------------------------------------|
|23,755|iuap_apdb|postgres|psql            |           |2023-09-20 15:41:04.824 +0800|Lock           |transactionid|active             |update lock_test set age = 'session2' where name = 'ff';|
|24,101|iuap_apdb|postgres|psql            |           |2023-09-20 15:40:55.624 +0800|Client         |ClientRead   |idle in transaction|update lock_test set age = 'session1' where name = 'ff';|



state = 'idle in transaction' 这个属于开启了事务，但是目前已经处于idle状态的，属于不正常的状态。
大概率需要 kill。


-- 查询阻塞PID及SQL
        SELECT blocked_locks.pid     AS "被阻塞pid",
         blocking_locks.pid     AS "阻塞pid",
         blocked_activity.query    AS "被阻塞SQL",
         blocking_activity.query   AS "阻塞SQL",
         blocked_activity.wait_event_type as "被阻塞等待事件",
         blocked_activity.state as "被阻塞线程状态",
         blocking_activity.wait_event_type as "阻塞等待事件",
         blocking_activity.state as "阻塞线程状态",
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;
   
   
|被阻塞pid|阻塞pid |被阻塞SQL                                 |阻塞SQL                                         |被阻塞等待事件|被阻塞线程状态|阻塞等待事件|阻塞线程状态    |blocked_application|blocking_application|
|------|------|--------------------------------------------------------|--------------------------------------------------------|-------|-------|------|-------------------|-------------------|--------------------|
|23,755|24,101|update lock_test set age = 'session2' where name = 'ff';|update lock_test set age = 'session1' where name = 'ff';|Lock   |active |Client|idle in transaction|psql               |psql                |

这里可以明显看到是 24,101 阻塞了 23,755。


-- 谁被阻塞了
select distinct pid from pg_locks where not granted;  
|pid   |
|------|
|23,755|


-- 查看阻塞该PID的线程
select pg_blocking_pids(23755);

|pg_blocking_pids|
|----------------|
|{24,101}        |

也可以查出来是 24,101 阻塞了 23,755。


kill掉指定的SQL语句：
select pg_cancel_backend(24101);
select  pg_terminate_backend(24101)




模拟二
session1：
begin;
update lock_test set age = 'session1' where name = 'ff';

session2：
alter table lock_test add column name1 varchar(100);


依然可以使用如上的方式查找及kill

   

问题总结

pg数据库的锁排查方法