PostgreSQL锁，第2部分:重量级锁

应用程序开发人员和dba的PostgreSQL锁可见性在大多数情况下与重量级锁相关。复杂的数据库锁定操作需要使用来自系统目录的视图进行完整的检测。应该清楚哪个对象被特定的数据库“后端”进程锁定。任何锁的另一个名称是“瓶颈”。为了使数据库操作并行，我们应该将单个瓶颈分解为多个特定于操作的任务。

这是与表级锁相关的三篇博客文章的第二部分。上一篇文章是关于行级锁的，随后的一篇文章评论锁存器保护内部数据库结构。

示例环境

CREATE TABLE locktest (c INT);
INSERT INTO locktest VALUES (1), (2);

Helper视图

为了检查这些锁的不同类型，让我们按照[Bruce Momjian在他的演示中]的建议创建一个助手视图)：

CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type,
mode AS lock_mode, granted,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text                                                                                     END AS xid_lock, relname,
page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
-- no need to show self-vxid locks                                                                           virtualtransaction IS DISTINCT FROM virtualxid
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

RowShareLock (rowshare)

许多应用程序使用read-modify-write范例。例如，应用程序从表中获取单个对象字段，修改数据，并将更改保存回数据库。在多用户环境中，不同的用户可以在事务处理过程中修改相同的行。我们可以通过简单的选择得到不一致的数据。为了响应用户的需求，几乎所有SQL数据库都有SELECT…FOR SHARE锁。此特性可防止应用程序实体在提交或回滚锁定事务之前进行数据修改。

例如：

1. 一个用户在accounts表中存储了多个银行帐户，在bank_clients表中存储了total_amount。
2. 为了更新total_amount字段，我们应该防止修改与特定银行客户端相关的所有行。
3. 最好使用单个update语句来计算total_amount并从accounts表中选择它。如果更新需要外部数据或用户的一些操作，则需要几个语句

START TRANSACTION;
SELECT * FROM accounts WHERE client_id = 55 FOR SHARE;
SELECT * FROM bank_clients WHERE client_id=55 FOR UPDATE;
UPDATE bank_clients SET total_amount=38984.33, client_status='gold' WHERE client_id=55;
COMMIT;

SELECT FOR SHARE语句在关系locktest上创建一个“RowShareLock”锁。

下面是用SQL语句创建的完全相同的锁: 00

BEGIN;
LOCK TABLE locktest IN ROW SHARE MODE;

不管查询锁定的行数是多少，都需要一个重量级的RowShareLock。

在下面的示例中，用一个未完成的事务来说明这一点。启动未完成的事务，在第二个数据库连接中选择lockview：

BEGIN;
SELECT * FROM locktest FOR SHARE;
-- In second connection:
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |   lock_mode   | granted | xid_lock | relname  
-------+------+---------------+---------------+---------+----------+----------
 21144 | 3/13 | transactionid | ExclusiveLock | t       | 586      | 
 21144 | 3/13 | relation      | RowShareLock  | t       |          | locktest

RowExclusiveLock (ROW EXCLUSIVE)

修改行的实际查询还需要对表进行重量级锁，每个表一个锁。

下一个示例使用DELETE查询，UPDATE将具有相同的效果。

所有修改表中数据的命令都获得ROW EXCLUSIVE锁。

BEGIN;
DELETE FROM locktest;
-- second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |    lock_mode     | granted | xid_lock | relname  
-------+------+---------------+------------------+---------+----------+----------
 10997 | 3/6  | transactionid | ExclusiveLock    | t       | 589      | 
 10997 | 3/6  | relation      | RowExclusiveLock | t       |          | locktest

这个新锁与前面的FOR SHARE示例不兼容。SELECT * FROM locktest FOR SHARE等待删除事务完成或终止：

postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname,page,tuple FROM lockview;
  pid  | vxid |   lock_type   |    lock_mode     | granted | xid_lock | relname  | page | tuple 
-------+------+---------------+------------------+---------+----------+----------+------+-------
 10997 | 3/6  | transactionid | ExclusiveLock    | t       | 589      |          |      |      
 10997 | 3/6  | relation      | RowExclusiveLock | t       |          | locktest |      |      
 11495 | 5/9  | relation      | RowShareLock     | t       |          | locktest |      |      
 11495 | 5/9  | tuple         | RowShareLock     | t       |          | locktest |    0 |     1
 11495 | 5/9  | transactionid | ShareLock        | f       | 589      |          |      |

修改表内容的查询还会锁定所有索引，即使索引不包含已修改的字段。

-- preparation
CREATE INDEX c_idx2 ON locktest (c);
ALTER TABLE locktest ADD COLUMN c2 INT;
CREATE INDEX c2_idx ON locktest(c2);

-- unfinished example transaction
BEGIN;
UPDATE locktest SET c=3 WHERE c=1;
-- second connection
postgres=# SELECT * FROM lockview;
 pid  |  vxid  | lock_type  |    lock_mode     | granted | xid_lock | relname  | page | tuple | classid | objid | objsubid 
------+--------+------------+------------------+---------+----------+----------+------+-------+---------+-------+----------
 3998 | 3/7844 | virtualxid | ExclusiveLock    | t       | 3/7844   |          |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | c2_idx   |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | c_idx    |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | c_idx2   |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | locktest |      |       |         |       |

ShareLock (SHARE)

CREATE INDEX的非并发版本可以防止使用ShareLock进行表更新，例如DROP table或INSERT或DELETE。

BEGIN;
CREATE INDEX c_idx ON locktest (c);
-- second connection
postgres=# SELECT * FROM lockview;
 pid  |  vxid  |   lock_type   |      lock_mode      | granted | xid_lock | relname  | page | tuple | classid | objid | objsubid 
------+--------+---------------+---------------------+---------+----------+----------+------+-------+---------+-------+----------
 3998 | 3/7835 | virtualxid    | ExclusiveLock       | t       | 3/7835   |          |      |       |         |       |         
 3998 | 3/7835 | transactionid | ExclusiveLock       | t       | 564      |          |      |       |         |       |         
 3998 | 3/7835 | relation      | AccessExclusiveLock | t       |          |          |      |       |         |       |         
 3998 | 3/7835 | relation      | ShareLock           | t       |          | locktest |      |       |         |       |

您可以并行执行多个CREATE INDEX查询，除非索引名称完全相同。等待发生在pg_class表中的行锁(具有" transactionid "类型的ShareLock)上。

注意，还有类型为“relation”的AccessExclusiveLock锁，但它不是表级别的锁。

ShareUpdateExclusiveLock (SHARE UPDATE EXCLUSIVE)

这些数据库维护操作需要带ShareUpdateExclusiveLock：

• ANALYZE table
• VACUUM (without full) runs.
• CREATE INDEX CONCURRENTLY

ANALYZE表名，语句更新表统计信息。只有统计信息是最新的，查询计划器/优化器才能为查询执行提供最佳计划。

BEGIN;
ANALYZE locktest;
-- in second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |        lock_mode         | granted | xid_lock | relname  
-------+------+---------------+--------------------------+---------+----------+----------
 10997 | 3/7  | transactionid | ExclusiveLock            | t       | 591      | 
 10997 | 3/7  | relation      | ShareUpdateExclusiveLock | t       |          | locktest

RowExclusiveLock和ShareUpdateExclusiveLock不冲突。UPDATE/DELETE/INSERT仍然可以在ANALYZE期间修改行。

VACUUM和CREATE INDEX concurrent只能在事务外部执行。要在lockview中查看这些语句的效果，首先执行一个冲突事务，例如在事务中运行ANALYZE，或者对一个大表运行VACUUM。

CREATE INDEX concurrent锁定可能会令人困惑。SHARE UPDATE EXCLUSIVE锁与用于DELETE、INSERT和UPDATE的ROW EXCLUSIVE锁不冲突。不幸的是，由于全表扫描，CREATE INDEX concurrent会等待活动事务完成两次:

在并发索引构建中，索引实际上在一个事务中被输入到系统目录中，然后在另外两个事务中进行两次表扫描。在每次扫描表之前，索引构建必须等待已修改表的现有事务终止。 PostgreSQL文档

AccessExclusiveLock (ACCESS EXCLUSIVE)

此锁与任何其他锁冲突，并由以下语句使用：

• CREATE RULE
• DROP TABLE
• DROP INDEX
• TRUNCATE
• VACUUM FULL
• LOCK TABLE (default mode)
• CLUSTER
• REINDEX
• REFRESH MATERIALIZED VIEW (without CONCURRENTLY)

BEGIN;
CREATE RULE r_locktest AS ON INSERT TO locktest DO INSTEAD NOTHING;
-- second connection
postgres=# select pid,vxid,lock_type,lock_mode,granted,xid_lock,relname from lockview;
  pid  | vxid |   lock_type   |      lock_mode      | granted | xid_lock | relname  
-------+------+---------------+---------------------+---------+----------+----------
 10997 | 3/19 | transactionid | ExclusiveLock       | t       | 596      | 
 10997 | 3/19 | relation      | AccessExclusiveLock | t       |          | locktest

更重要的是，drop index需要表和索引的访问独占锁：

BEGIN;
DROP INDEX c_idx;
-- second connection
postgres=# SELECT * FROM lockview;
 pid  |  vxid  |   lock_type   |      lock_mode      | granted | xid_lock | relname  | page | tuple | classid | objid | objsubid 
------+--------+---------------+---------------------+---------+----------+----------+------+-------+---------+-------+----------
 3998 | 3/7839 | virtualxid    | ExclusiveLock       | t       | 3/7839   |          |      |       |         |       |         
 3998 | 3/7839 | transactionid | ExclusiveLock       | t       | 569      |          |      |       |         |       |         
 3998 | 3/7839 | relation      | AccessExclusiveLock | t       |          | c_idx    |      |       |         |       |         
 3998 | 3/7839 | relation      | AccessExclusiveLock | t       |          | locktest |      |       |         |       |

注意：这是最危险的一种锁。避免在生产环境中运行需要访问独占锁的查询，或者至少将应用程序置于维护模式。

ExclusiveLock

同时，SQL命令不使用ExclusiveLock，除非使用一般的LOCK TABLE语句。这个锁可以阻止除非锁定选择(即没有FORSHARE/UPDATE)以外的所有请求。

BEGIN;
LOCK TABLE locktest IN EXCLUSIVE MODE;
-- second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid | lock_type |   lock_mode   | granted | xid_lock | relname  
-------+------+-----------+---------------+---------+----------+----------
 10997 | 3/21 | relation  | ExclusiveLock | t       |          | locktest

Savepoints

Savepoint生成一个transactionid类型的额外ExclusiveLock，并带有新的xid值。

BEGIN;
SELECT * FROM locktest FOR SHARE;
SAVEPOINT s1;
SELECT * FROM locktest FOR UPDATE;
-- second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |    lock_mode    | granted | xid_lock | relname  
-------+------+---------------+-----------------+---------+----------+----------
 10997 | 3/37 | transactionid | ExclusiveLock   | t       | 602      | 
 10997 | 3/37 | transactionid | ExclusiveLock   | t       | 603      | 
 10997 | 3/37 | relation      | AccessShareLock | t       |          | c_idx
 10997 | 3/37 | relation      | RowShareLock    | t       |          | locktest

pg_advisory_lock

有时应用程序开发人员需要进程之间的同步。在这样的系统中，应用程序频繁地创建和删除锁。使用基于行的锁实现的系统往往会导致表膨胀。

有许多与咨询锁相关的功能：

• -每个会话或每个事务
• 如果lock不可用，则等待或立即返回false
• 独占或共享
• 64位或两个32位整数资源标识符

假设我们有几个cron作业，并且应用程序应该防止同时运行同一个脚本。接下来，每个脚本可以检查特定整数作业标识符的锁在PostgreSQL中是否可用：

postgres=# SELECT pg_try_advisory_lock(10);
 pg_try_advisory_lock 
----------------------
 t
-- second connection
postgres=# SELECT * FROM lockview;
 pid  | vxid | lock_type |   lock_mode   | granted | xid_lock | relname | page | tuple | classid | objid | objsubid 
------+------+-----------+---------------+---------+----------+---------+------+-------+---------+-------+----------
 3998 | 3/0  | advisory  | ExclusiveLock | t       |          |         |      |       |       0 |    10 |        1
-- other connections
SELECT pg_try_advisory_lock(10);
 pg_try_advisory_lock 
----------------------
 f

查询生成带有类型咨询的ExclusiveLock。

Deadlocks

当查询永远无法完成时，任何具有多个锁的系统都可能出现死锁情况。解决这类问题的唯一方法是:终止一个阻塞的语句。更重要的是，死锁检测在PostgreSQL中是一个昂贵的过程。只有当事务锁定deadlock_timeout毫秒(默认情况下是一秒后)时才会发生死锁检查。

下面是两个不同连接A和B的死锁情况：

从锁等待开始的任何死锁。

A: BEGIN; SELECT c FROM locktest WHERE c=1 FOR UPDATE;
B: BEGIN; SELECT c FROM locktest WHERE c=2 FOR UPDATE; SELECT c FROM locktest WHERE c=1 FOR UPDATE;

你并不是唯一一个识别死锁的人，因为pg_stat_activity系统视图可以帮助你找到导致锁等待的语句和事务:

postgres=# SELECT pg_stat_activity.pid AS pid,
query, wait_event, vxid, lock_type,
lock_mode, granted, xid_lock
FROM lockview JOIN pg_stat_activity ON (lockview.pid = pg_stat_activity.pid);
  pid  |          query             |  wait_event   | vxid |   lock_type   |      lock_mode      | granted | xid_lock 
-------+----------------------------+---------------+------+---------------+---------------------+---------+----------
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | transactionid | ExclusiveLock       | t       | 605
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | advisory      | ExclusiveLock       | t       | 
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | relation      | AccessShareLock     | t       | 
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | relation      | RowShareLock        | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | transactionid | ExclusiveLock       | t       | 606
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | advisory      | ExclusiveLock       | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | relation      | AccessShareLock     | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | relation      | RowShareLock        | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | tuple         | AccessExclusiveLock | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | transactionid | ShareLock           | f       | 605

对c=2行的SELECT FOR UPDATE会导致死锁：

SELECT c FROM locktest WHERE c=2 FOR UPDATE;

之后，PostgreSQL在服务器日志中报告：

2018-08-02 08:46:07.793 UTC [10997] ERROR:  deadlock detected
2018-08-02 08:46:07.793 UTC [10997] DETAIL:  Process 10997 waits for ShareLock on transaction 606; blocked by process 11495.
Process 11495 waits for ShareLock on transaction 605; blocked by process 10997.
Process 10997: select c from locktest where c=2 for update;
Process 11495: select c from locktest where c=1 for update;
2018-08-02 08:46:07.793 UTC [10997] HINT:  See server log for query details.
2018-08-02 08:46:07.793 UTC [10997] CONTEXT:  while locking tuple (0,3) in relation "locktest"
2018-08-02 08:46:07.793 UTC [10997] STATEMENT:  SELECT c FROM locktest WHERE c=2 FOR UPDATE;
ERROR:  deadlock detected
DETAIL:  Process 10997 waits for ShareLock on transaction 606; blocked by process 11495.
Process 11495 waits for ShareLock on transaction 605; blocked by process 10997.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,3) in relation "locktest"

如您所见，数据库服务器会自动中止一个阻塞的事务。

Multi-way deadlocks

通常只有两个事务会产生死锁。但是，在复杂的情况下，应用程序可能会因为多个事务形成一个依赖关系圈而导致死锁。

A：锁第一行，B：锁第2行，C：锁第3行

下一步：

A：试图得到第3行，B：试图得到第1行，C：试图得到第2行

总结

• 不要把DDL语句放在长事务中。
• 对于频繁更新的表，请避免在高负载时打开DDL。
• CLUSTER命令要求独占访问表及其所有索引。
• 监控postgresql日志中与死锁相关的消息。

本栏目推荐文章
• 用于PostgreSQL索引维护的有用查询
• 【ubantu22.10】安装部署timescaledbv2.13.0及postgresql v14.10
• Ubuntu 安装Postgresql与PostGIS
• MapOnline在线地图插件升级——修复部分地图不能使用的问题
• 解决vite 文件的部分热更新问题
• PostgreSQL从小白到高手教程 - 第41讲：postgres表空间备份与恢复
• C# 面试题 如何优雅的检测一个double decimal是否含有小数部分 是否为小数
• go-carbon v2.3.5 发布，轻量级、语义化、对开发者友好的 golang 时间处理库
• 微信小程序 全局配置项、小程序窗口的组成部分
• docker中修改postgreSQL的时区

重量级 PostgreSQL 重量 部分重量级postgresql重量 部分 重量级 重量 重量级synchronized重量09 重量p1268 重量级 重量 问题http 重量级deepfloyd starcoder重量 synchronized轻量 轻量级 重量级 重量级 基金会 商用 全家 轻量 轻量级 重量级synchronized