PostgreSQL锁详解_postgresql 行锁

PG锁

psql提示符设置

#设置提示符
cd
cat >.psqlrc <<EOF
\set PROMPT1 '%/(%p)%R%#'
\pset border 2
EOF
#效果如下，postgres(11288)中11288为会话ID
[pgsql@centos7:/home/pgsql]$psql postgresql://postgres:postgres@localhost:5432/postgres
Border style is 2.
psql (12.2)
Type "help" for help.

postgres(11288)=#select pg_backend_pid();
+----------------+
| pg_backend_pid |
+----------------+
|          11288 |
+----------------+
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

系统视图

PostgreSQL提供了两个视图：

• pg_locks展示锁信息，每一个被锁或者等待锁的对象一条记录。
• pg_stat_activity，每个会话一条记录，显示会话状态信息。

pg_locks

描述事务ID的字段说明：

• transactionid

  代表事务ID，简写为"xid"

  每产生一个事务ID，都会在pg_clog下的commit log文件中占用2bit。

• virtualxid

  代表虚拟事务ID，简写为"vxid"。

  最早在PostgreSQL中是没有虚拟事务ID的，但后来发现，有一些事务根本没有产生任何实质的变更，如一个只读事务或一个空事务，若在这种情况下也分配一个事务ID会造成资源浪费，于是提出了虚拟事务ID的概念。

  对于这类只读事务，只分配一个虚拟事务ID，而不实际分配一个真实的事务ID，这样就不需要在commit log中占用2bit的空间了

• virtualtransaction

  pg_locks这张视图的字段分为以下两个部分：

  1. virtualtransaction字段之前的字段（不包括virtualtransaction字段），我们称其为"第一部分"

     第一部分字段用于描述锁定对象（Locked Object）的信息

  2. virtualtransaction字段之后的字段（包括virtualtransaction字段）我们称其为"第二部分"。

     第二部分字段描述的是持有锁或等待锁session的信息

“virtualxid"在第一部分字段中，表示锁对象是一个"virtualxid”，而"virtualtransaction"表示持有锁或等待锁session的虚拟事务ID。

pg_stat_activity

锁测试

#创建测试表
drop table if exists testtab01;
create table testtab01 (id int primary key,note varchar(100));
insert into testtab01 select generate_series(1,100),md5(random()::text);
1
2
3
4

表锁

#会话 14735 锁定表 testtab01
postgres(14735)=#begin;
BEGIN
postgres(14735)=#lock table testtab01;
LOCK TABLE
postgres(14735)=#




#其他会话查询
postgres(14736)=#select locktype,
postgres-#    relation::regclass as rel,
postgres-#    virtualxid as vxid,
postgres-#    transactionid as xid ,
postgres-#    virtualtransaction as vxid2,
postgres-#    pid,
postgres-#    mode,
postgres-#    granted
postgres-#    from pg_locks
postgres-# where pid = 14735;
+---------------+-----------+------+-----+-------+-------+---------------------+---------+
|   locktype    |    rel    | vxid | xid | vxid2 |  pid  |        mode         | granted |
+---------------+-----------+------+-----+-------+-------+---------------------+---------+
| virtualxid    |           | 3/2  |     | 3/2   | 14735 | ExclusiveLock       | t       |
| relation      | testtab01 |      |     | 3/2   | 14735 | AccessExclusiveLock | t       |
| transactionid |           |      | 510 | 3/2   | 14735 | ExclusiveLock       | t       |
+---------------+-----------+------+-----+-------+-------+---------------------+---------+
(3 rows)

postgres(14736)=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

第一行显示的是事务在自己的“virtualxid”上加的ExclusiveLock锁，这是必定会加上的。

如上第二行可以看出实际在表上加的锁“AccessExclusiveLock”

#会话 14737 也锁定表 testtab01
postgres(14737)=#begin;
BEGIN
postgres(14737)=#lock table testtab01;

1
2
3
4
5

此时的锁情况

postgres(14736)=#select locktype,
postgres-#    relation::regclass as rel,
postgres-#    virtualxid as vxid,
postgres-#    transactionid as xid ,
postgres-#    virtualtransaction as vxid2,
postgres-#    pid,
postgres-#    mode,
postgres-#    granted
postgres-#    from pg_locks
postgres-# where pid in (14735,14737);
+---------------+-----------+------+-----+-------+-------+---------------------+---------+
|   locktype    |    rel    | vxid | xid | vxid2 |  pid  |        mode         | granted |
+---------------+-----------+------+-----+-------+-------+---------------------+---------+
| virtualxid    |           | 5/2  |     | 5/2   | 14737 | ExclusiveLock       | t       |
| virtualxid    |           | 3/2  |     | 3/2   | 14735 | ExclusiveLock       | t       |
| transactionid |           |      | 511 | 5/2   | 14737 | ExclusiveLock       | t       |
| relation      | testtab01 |      |     | 3/2   | 14735 | AccessExclusiveLock | t       |
| transactionid |           |      | 510 | 3/2   | 14735 | ExclusiveLock       | t       |
| relation      | testtab01 |      |     | 5/2   | 14737 | AccessExclusiveLock | f       |
+---------------+-----------+------+-----+-------+-------+---------------------+---------+
(6 rows)

postgres(14736)=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

如上可以看出进程14735和14737都对表testtab01加 了锁，14735的granted字段为t，14737的granted字段为f，表示14737就成没有获得这把锁，从而被阻塞

从上面的示例中可以看出，想查看被锁阻塞的进程只要查询视图“pg_locks”中“granted”字段值为“False”的进程就可以了

行锁

# 14735 加行锁
postgres(14735)=#select * from testtab01 where id=1;
+----+----------------------------------+
| id |               note               |
+----+----------------------------------+
|  1 | 22c12446639eea7dbe86f5b85e7bbb98 |
+----+----------------------------------+
(1 row)

postgres(14735)=#begin;
BEGIN
postgres(14735)=#select * from testtab01 where id=1 for update;
+----+----------------------------------+
| id |               note               |
+----+----------------------------------+
|  1 | 22c12446639eea7dbe86f5b85e7bbb98 |
+----+----------------------------------+
(1 row)

postgres(14735)=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

其他窗口查询

postgres(14736)=#
postgres(14736)=#select locktype,
postgres-#    relation::regclass as rel,
postgres-#    virtualxid as vxid,
postgres-#    transactionid as xid ,
postgres-#    virtualtransaction as vxid2,
postgres-#    pid,
postgres-#    mode,
postgres-#    granted
postgres-#    from pg_locks
postgres-# where pid in (14735);
+---------------+----------------+------+-----+-------+-------+---------------+---------+
|   locktype    |      rel       | vxid | xid | vxid2 |  pid  |     mode      | granted |
+---------------+----------------+------+-----+-------+-------+---------------+---------+
| relation      | testtab01_pkey |      |     | 3/4   | 14735 | RowShareLock  | t       |
| relation      | testtab01      |      |     | 3/4   | 14735 | RowShareLock  | t       |
| virtualxid    |                | 3/4  |     | 3/4   | 14735 | ExclusiveLock | t       |
| transactionid |                |      | 512 | 3/4   | 14735 | ExclusiveLock | t       |
+---------------+----------------+------+-----+-------+-------+---------------+---------+
(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

加行锁的过程，是先在表上加一个表级意向锁，从上可以看出，行锁不仅会在表上加意向锁，也会在相应的主键上加意向锁。其中“testtab01_pkey”就是表“testtab01”的主键。

为什么没有发现行锁？

实际上pg_locks并不能显示出每个行锁的信息，原因也很简单，行锁信息并不会记录到共享内存中。这也很好理解，如果每个行锁在内存中都有一条记录的话，在对表做全表更新时，表有多少行就需要在内存中记录多少条行锁信息，那么内存会吃不消。所以PostgreSQL被设计成不在内存中记录行锁信息。

如果在pg_locks中没有行锁信息，如何知道一个进程被另一个进程的行锁阻塞了呢？

pg_locks中提供了另一种信息来表示这种阻塞关系

# 14737 也加行锁
postgres(14737)=#begin;
BEGIN
postgres(14737)=#select * from testtab01 where id=1 for update;

# 该会话被阻塞
1
2
3
4
5
6

查询当前的锁情况

postgres(14736)=#select locktype,
postgres-#    relation::regclass as rel,
postgres-#    virtualxid as vxid,
postgres-#    transactionid as xid ,
postgres-#    virtualtransaction as vxid2,
postgres-#    pid,
postgres-#    mode,
postgres-#    granted
postgres-#    from pg_locks
postgres-# where pid in (14735,14737);
+---------------+----------------+------+-----+-------+-------+---------------------+---------+
|   locktype    |      rel       | vxid | xid | vxid2 |  pid  |        mode         | granted |
+---------------+----------------+------+-----+-------+-------+---------------------+---------+
| relation      | testtab01_pkey |      |     | 5/3   | 14737 | RowShareLock        | t       |
| relation      | testtab01      |      |     | 5/3   | 14737 | RowShareLock        | t       |
| virtualxid    |                | 5/3  |     | 5/3   | 14737 | ExclusiveLock       | t       |
| relation      | testtab01_pkey |      |     | 3/4   | 14735 | RowShareLock        | t       |
| relation      | testtab01      |      |     | 3/4   | 14735 | RowShareLock        | t       |
| virtualxid    |                | 3/4  |     | 3/4   | 14735 | ExclusiveLock       | t       |
| transactionid |                |      | 512 | 3/4   | 14735 | ExclusiveLock       | t       |
| transactionid |                |      | 512 | 5/3   | 14737 | ShareLock           | f       |
| tuple         | testtab01      |      |     | 5/3   | 14737 | AccessExclusiveLock | t       |
+---------------+----------------+------+-----+-------+-------+---------------------+---------+
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

前面说过，如果想查看哪个进程被阻塞住了，只需要查看“granted”字段值为“False”的PID，如上就是倒数第2行，从中可以了解到，14737进程申请一个类型为“transactionid”的锁时被阻塞了，这个transactionid锁对应的xid为“512”。从倒数第3行可以看出，xid为“512”的锁被进程“14735”持有了。

从上面的分析中我们知道，实际上，行锁中的阻塞信息是通过transactionid类型的锁体现出来的。从原理上来说，行锁是会在数据行上加上自己的xid的，另一个进程读到这一行时，如果发现这一行上有行锁，会把行上另一个事务的xid读出来，然后申请在这个xid上加SHARE锁。而持有行锁的进程已经在此xid上加了EXCLUSIVE锁，所以后面要更新的行的进程会被阻塞。

若要查询因行锁被阻塞的进程信息，只需要查询视图“pg_locks”中类型为“transactionid”的锁信息就可以了。

那么进程是在哪一行上被阻塞的呢？

可以通过查看pg_locks的“page”和“tuple”字段来了解。

# 14735 更新表行
postgres(14735)=#begin;
BEGIN
postgres(14735)=#update testtab01 set note='aaaa' where id=1;
UPDATE 1
postgres(14735)=#


# 14737 同时更新同一行
postgres(14737)=#begin;
BEGIN
postgres(14737)=#update testtab01 set note='aaaa' where id=1;

#14737 会被阻塞
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询锁情况

postgres(14736)=#select locktype,
postgres-#   relation::regclass as rel,
postgres-#   page||','||tuple as ctid,
postgres-#   virtualxid as vxid,
postgres-#   transactionid as xid ,
postgres-#   virtualtransaction as vxid2,
postgres-#   pid,
postgres-#   mode,
postgres-#   granted
postgres-# from pg_locks
postgres-# where pid in (14735,14737);
+---------------+----------------+-------+------+-----+-------+-------+------------------+---------+
|   locktype    |      rel       | ctid  | vxid | xid | vxid2 |  pid  |       mode       | granted |
+---------------+----------------+-------+------+-----+-------+-------+------------------+---------+
| relation      | testtab01_pkey |       |      |     | 5/5   | 14737 | RowExclusiveLock | t       |
| relation      | testtab01      |       |      |     | 5/5   | 14737 | RowExclusiveLock | t       |
| virtualxid    |                |       | 5/5  |     | 5/5   | 14737 | ExclusiveLock    | t       |
| relation      | testtab01_pkey |       |      |     | 3/7   | 14735 | RowExclusiveLock | t       |
| relation      | testtab01      |       |      |     | 3/7   | 14735 | RowExclusiveLock | t       |
| virtualxid    |                |       | 3/7  |     | 3/7   | 14735 | ExclusiveLock    | t       |
| transactionid |                |       |      | 517 | 5/5   | 14737 | ShareLock        | f       |
| tuple         | testtab01      | 0,103 |      |     | 5/5   | 14737 | ExclusiveLock    | t       |
| transactionid |                |       |      | 517 | 3/7   | 14735 | ExclusiveLock    | t       |
| transactionid |                |       |      | 518 | 5/5   | 14737 | ExclusiveLock    | t       |
+---------------+----------------+-------+------+-----+-------+-------+------------------+---------+
(10 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

此时，可以看到事务被阻塞在表“testtab01”的ctid为“(0,103)”的行上，查询该行信息的SQL命令如下：

postgres(14736)=#SELECT * FROM testtab01 WHERE ctid='(0,103)';
+----+------+
| id | note |
+----+------+
|  1 | aaaa |
+----+------+
(1 row)
1
2
3
4
5
6
7

从上面的运行结果中我们知道，事务阻塞在表“testtab01”中的“id”为“1”的行上。

锁兼容性矩阵

锁的等级和对应操作在源码中有定义

/*

 * These are the valid values of type LOCKMODE for all the standard lock
 * methods (both DEFAULT and USER).
   */

/* NoLock is not a lock mode, but a flag value meaning "don't get a lock" */
#define NoLock					0

#define AccessShareLock			1		/* SELECT */
#define RowShareLock			2		/* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock		3		/* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4		/* VACUUM (non-FULL),ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock				5		/* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock	6		/* like EXCLUSIVE MODE, but allows ROW SHARE */
#define ExclusiveLock			7		/* blocks ROW SHARE/SELECT...FOR UPDATE */
#define AccessExclusiveLock		8		/* ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE */
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

pg锁兼容性模式

说明：

• access share：select
• row share ：select for update /select for share
• row exclusive：DML
• share update exclusive：非full模式vacuum/online建索引/analyze
• share：非online建索引
• share row exclusive ：任何pg命令都不会自动请求该锁等级
• exclusive ：任何pg命令都不会自动请求该锁等级
• access exclusive：DDL,LOCK TABLE

SQL查询

常用阻塞查询SQL

-- 查看阻塞的会话（granted=t 在等待申请锁的会话）
SELECT 
  database, 
  locktype, 
  relation, 
  relation :: regclass, 
  mode, 
  pid 
FROM 
  pg_locks 
where 
  granted = 'f';

-- 根据pid和relation找到阻塞源（granted=t）
SELECT 
  database, 
  locktype, 
  relation :: regclass, 
  mode, 
  pid 
FROM 
  pg_locks 
where 
  granted = 't';


-- 根据被阻塞/阻塞源的pid查正在执行的语句
select 
  pid, 
  usename, 
  substring(query from 0 for50), 
  now() - query_start as time, 
  wait_event, 
  state 
from 
  pg_stat_activity 
where 
  pid = xxx;


-- 慢查询会话
SELECT pgsa.pid,
      pgsa.client_port,
      pgsa.datname AS datname, 
      pgsa.usename AS usename, 
      pgsa.client_addr client_addr, 
      pgsa.application_name AS application_name, 
      pgsa.state AS state,
      pgsa.wait_event,
      pgsa.wait_event_type,
      pgsa.backend_start AS backend_start, 
      pgsa.xact_start AS xact_start, 
      state_change,
      pgsa.query_start AS query_start, 
      TRUNC(EXTRACT( epoch  FROM  (NOW() - pgsa.xact_start)  )) AS xact_stay, 
      trunc(EXTRACT( epoch  FROM  (NOW() - pgsa.query_start) )) AS query_stay, 
      REPLACE(pgsa.QUERY, chr(10),  ' ') AS QUERY,
      pgsa.backend_type,
      'select pg_terminate_backend('||pgsa.pid||');' kill1,
      'select pg_cancel_backend('||pgsa.pid||');' kill2
    FROM   pg_stat_activity AS pgsa
    WHERE   pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' )
ORDER BY  query_stay DESC,xact_stay DESC
LIMIT  100;



-- 锁
-- granted=t是阻塞别人的，f是被阻塞的
SELECT 
  database dbid, 
  pd.datname, 
  locktype, 
  relation, 
  relation :: regclass relation_name, 
  mode, 
  pid, 
  granted 
FROM 
  pg_locks pl 
  left join pg_database pd 
  on pl.database = pd.oid 
where 
  pl.database > 0;



-- 锁查询
SELECT pg_locks.pid as pid,  -- 进程ID
      transactionid as transaction_id, -- 事务ID
             pd.datname db_name,
      nspname as schemaname,  -- schema名
      relname as object_name,  -- 对象名
      locktype as lock_type,  -- 锁类型
      mode lock_mode,  -- 锁模式
      CASE
        WHEN granted = 'f' THEN
         'get_lock'
        WHEN granted = 't' THEN
         'wait_lock'
      END lock_satus,   -- 锁状态：持有锁|等待锁
      least(query_start, xact_start) AS query_start,  -- query请求开始时间
      substr(query, 1, 25) AS query_text   -- 当前SQL语句
 FROM pg_locks
    left join pg_database pd
    on (pg_locks.database=pd.oid)
 LEFT OUTER JOIN pg_class
   ON (pg_locks.relation = pg_class.oid)
 LEFT OUTER JOIN pg_namespace
   ON (pg_namespace.oid = pg_class.relnamespace),  pg_stat_activity
WHERE NOT pg_locks.pid = pg_backend_pid()
  AND pg_locks.pid = pg_stat_activity.pid
ORDER BY query_start;


-- 杀会话
select 
  'select pg_terminate_backend(' || a.pid || ');' kill1, 
  'select pg_cancel_backend(' || a.pid || ');' kill2 
from 
  pg_stat_activity a 
where 
  pid <> pg_backend_pid() 
  and state in ('idle');

SELECT pg_cancel_backend(pid); -- Cancel a backend's current query.
SELECT pg_terminate_backend(pid); -- Terminate a backend.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

vw_lock1：当前事务锁等待、持锁信息

create view vw_lock1 as 
with 
t_wait as (
  select 
    a.mode, 
    a.locktype, 
    a.database, 
    a.relation, 
    a.page, 
    a.tuple, 
    a.classid, 
    a.granted, 
    a.objid, 
    a.objsubid, 
    a.pid, 
    a.virtualtransaction, 
    a.virtualxid, 
    a.transactionid, 
    a.fastpath, 
    b.state, 
    b.query, 
    b.xact_start, 
    b.query_start, 
    b.usename, 
    b.datname, 
    b.client_addr, 
    b.client_port, 
    b.application_name 
  from 
    pg_locks a, pg_stat_activity b 
  where 
    a.pid = b.pid and not a.granted
), 
t_run as (
  select 
    a.mode, 
    a.locktype, 
    a.database, 
    a.relation, 
    a.page, 
    a.tuple, 
    a.classid, 
    a.granted, 
    a.objid, 
    a.objsubid, 
    a.pid, 
    a.virtualtransaction, 
    a.virtualxid, 
    a.transactionid, 
    a.fastpath, 
    b.state, 
    b.query, 
    b.xact_start, 
    b.query_start, 
    b.usename, 
    b.datname, 
    b.client_addr, 
    b.client_port, 
    b.application_name 
  from 
    pg_locks a,pg_stat_activity b 
  where 
    a.pid = b.pid and a.granted
), 
t_overlap as (
  select 
    r.* 
  from 
    t_wait w 
    join t_run r on (
      r.locktype is not distinct 
      from 
        w.locktype 
        and r.database is not distinct 
      from 
        w.database 
        and r.relation is not distinct 
      from 
        w.relation 
        and r.page is not distinct 
      from 
        w.page 
        and r.tuple is not distinct 
      from 
        w.tuple 
        and r.virtualxid is not distinct 
      from 
        w.virtualxid 
        and r.transactionid is not distinct 
      from 
        w.transactionid 
        and r.classid is not distinct 
      from 
        w.classid 
        and r.objid is not distinct 
      from 
        w.objid 
        and r.objsubid is not distinct 
      from 
        w.objsubid 
        and r.pid <> w.pid
    )
), 
t_unionall as (
  select r.* from t_overlap r 
  union all 
  select w.* from t_wait w
) 
select 
  locktype, 
  datname, 
  relation :: regclass, 
  page, 
  tuple, 
  virtualxid, 
  transactionid :: text, 
  classid :: regclass, 
  objid, 
  objsubid, 
  string_agg(
    'Pid: ' || case when pid is null then 'NULL' else pid :: text end || chr(10)|| 'Lock_Granted: ' || case when granted is null then 'NULL' else granted :: text end || ' , Mode: ' || case when mode is null then 'NULL' else mode :: text end || ' , FastPath: ' || case when fastpath is null then 'NULL' else fastpath :: text end || ' , VirtualTransaction: ' || case when virtualtransaction is null then 'NULL' else virtualtransaction :: text end || ' , Session_State: ' || case when state is null then 'NULL' else state :: text end || chr(10)|| 'Username: ' || case when usename is null then 'NULL' else usename :: text end || ' , Database: ' || case when datname is null then 'NULL' else datname :: text end || ' , Client_Addr: ' || case when client_addr is null then 'NULL' else client_addr :: text end || ' , Client_Port: ' || case when client_port is null then 'NULL' else client_port :: text end || ' , Application_Name: ' || case when application_name is null then 'NULL' else application_name :: text end || chr(10)|| 'Xact_Start: ' || case when xact_start is null then 'NULL' else xact_start :: text end || ' , Query_Start: ' || case when query_start is null then 'NULL' else query_start :: text end || ' , Xact_Elapse: ' || case when (now() - xact_start) is null then 'NULL' else (now() - xact_start):: text end || ' , Query_Elapse: ' || case when (now() - query_start) is null then 'NULL' else (now() - query_start):: text end || chr(10)|| 'SQL (Current SQL in Transaction): ' || chr(10)|| case when query is null then 'NULL' else query :: text end, 
    chr(10)|| '--------' || chr(10) 
    order by 
      (
        case mode when 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end
      ) desc, 
      (case when granted then 0 else 1 end)
  ) as lock_conflict 
from 
  t_unionall 
group by 
  locktype, 
  datname, 
  relation, 
  page, 
  tuple, 
  virtualxid, 
  transactionid :: text, 
  classid, 
  objid, 
  objsubid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

vw_lock2：final block session

create view vw_lock2 as 
with recursive tmp_lock as (
    select distinct
           --w.mode w_mode,w.page w_page,
           --w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
           --now()-w.query_start w_locktime,w.query w_query 
           w.pid as id,--w_pid,
           r.pid as parentid--r_pid,
           --r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
           --r.relation::regclass,
           --r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
           --r.query_start r_query_start,
           --now()-r.query_start r_locktime,r.query r_query,   
    from (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
           from pg_locks a,
                pg_stat_activity b 
          where a.pid=b.pid 
            and not a.granted 
          ) w,
         (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
            from pg_locks a,
                 pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
           where a.pid=b.pid 
             and a.granted 
          ) r 
    where 1=1
      and r.locktype is not distinct from w.locktype 
      and r.database is not distinct from w.database 
      and r.relation is not distinct from w.relation 
      and r.page is not distinct from w.page 
      and r.tuple is not distinct from w.tuple 
      and r.classid is not distinct from w.classid 
      and r.objid is not distinct from w.objid 
      and r.objsubid is not distinct from w.objsubid 
      and r.transactionid is not distinct from w.transactionid 
      and r.pid <> w.pid
        
),tmp0 as (
  select *
    from tmp_lock tl
   union all
  -- 查找root，同一时刻可能有多个root
  select t1.parentid,0::int4
    from tmp_lock t1
   where 1=1
     and t1.parentid not in (select id from tmp_lock)
),tmp3 (pathid,depth,id,parentid) as (
  -- 对过滤出的机构向下递归，构成tree
  SELECT array[id]::text[] as pathid,1 as depth,id,parentid
    FROM tmp0
   where 1=1
     and parentid=0
   union
  SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
    FROM tmp0 t1,  
         tmp3 t0
   where 1=1
     and t1.parentid=t0.id
)
select distinct 
       '/'||array_to_string(a0.pathid,'/') as pathid,
       a0.depth,
       a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,
       --'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,
       --'select pg_terminate_backend('||a0.id|| ');' as term_pid,
       case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end  as term_pid,
       case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end  as cancel_pid
       ,a2.datname,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state
       --,a2.backend_start,a2.xact_start,a2.query_start
  from tmp3 a0
       left outer join (select distinct '/'||id||'/' as prefix_id,id
                        from tmp0
             where 1=1 ) a1
                  on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
     left outer join pg_stat_activity a2 -- select * from pg_stat_activity
                  on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

示例

创建测试数据

#创建测试表
drop table if exists testtab01;
create table testtab01 (id int primary key,note varchar(100));
insert into testtab01 select generate_series(1,100),md5(random()::text);
1
2
3
4

会话 14735 插入数据

postgres(14735)=#begin;
BEGIN
postgres(14735)=#insert into testtab01 values(101,'fsadfasdf');
INSERT 0 1
postgres(14735)=#
1
2
3
4
5

查看锁情况

postgres(14736)=#SELECT database, locktype,relation::regclass, mode, pid FROM pg_locks where granted='t';
+----------+---------------+-----------+------------------+-------+
| database |   locktype    | relation  |       mode       |  pid  |
+----------+---------------+-----------+------------------+-------+
|    13593 | relation      | pg_locks  | AccessShareLock  | 14736 |
|          | virtualxid    |           | ExclusiveLock    | 14736 |
|    13593 | relation      | testtab01 | RowExclusiveLock | 14735 |
|          | virtualxid    |           | ExclusiveLock    | 14735 |
|          | transactionid |           | ExclusiveLock    | 14735 |
+----------+---------------+-----------+------------------+-------+
(5 rows)

#如上第三行信息，可以看出testtab01表上持有一个RowExclusiveLock级别的锁，insert未提交产生的
1
2
3
4
5
6
7
8
9
10
11
12
13

会话 14737 此时想要删除testtab01表

postgres(14737)=#drop table testtab01;
#当前会话会等待
1
2

查询此时锁情况

postgres(14736)=#SELECT database,locktype,relation,relation::regclass,mode,pid,granted FROM pg_locks where pid in (14735,14737);
+----------+---------------+----------+-----------+---------------------+-------+---------+
| database |   locktype    | relation | relation  |        mode         |  pid  | granted |
+----------+---------------+----------+-----------+---------------------+-------+---------+
|          | virtualxid    |          |           | ExclusiveLock       | 14737 | t       |
|          | virtualxid    |          |           | ExclusiveLock       | 14735 | t       |
|          | transactionid |          |           | ExclusiveLock       | 14735 | t       |
|    13593 | relation      |    16400 | testtab01 | AccessExclusiveLock | 14737 | f       |
|    13593 | relation      |    16400 | testtab01 | RowExclusiveLock    | 14735 | t       |
|          | transactionid |          |           | ExclusiveLock       | 14737 | t       |
+----------+---------------+----------+-----------+---------------------+-------+---------+
(6 rows)

#如上倒数第2行，14735持有锁（granted=t）锁模式为RowExclusiveLock，与如上倒数第3行14737申请的AccessExclusiveLock冲突，所以会话14737被阻塞
1
2
3
4
5
6
7
8
9
10
11
12
13
14

vw_lock1视图查询的结果

#lock_conflict信息
Pid: 14737
Lock_Granted: false , Mode: AccessExclusiveLock , FastPath: false , VirtualTransaction: 5/6 , Session_State: active
Username: postgres , Database: postgres , Client_Addr: ::1/128 , Client_Port: 54771 , Application_Name: psql
Xact_Start: 2023-06-29 20:26:21.563007+08 , Query_Start: 2023-06-29 20:26:21.563007+08 , Xact_Elapse: 00:15:17.081875 , Query_Elapse: 00:15:17.081875
SQL (Current SQL in Transaction): 
drop table testtab01;
--------
Pid: 14735
Lock_Granted: true , Mode: RowExclusiveLock , FastPath: false , VirtualTransaction: 3/13 , Session_State: idle in transaction
Username: postgres , Database: postgres , Client_Addr: ::1/128 , Client_Port: 54769 , Application_Name: psql
Xact_Start: 2023-06-29 20:22:39.503155+08 , Query_Start: 2023-06-29 20:23:07.535802+08 , Xact_Elapse: 00:18:59.141727 , Query_Elapse: 00:18:31.10908
SQL (Current SQL in Transaction): 
insert into testtab01 values(101,'fsadfasdf');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

vw_lock2视图查询的结果

#第一行term_pid信息
select pg_terminate_backend(14735);
##第一行cancel_pid信息
select cancel_backend(14735);

#/14735/14737表示  14735会话阻塞了14737会话，可以手动执行term_pid和cancel_pid的命令终止会话
1
2
3
4
5
6